FTP to FTP XML document question
Answered
Hi everyone, I'm working on a flow that is basically moving an XML document from an FTP to FTP site but I want to do a transformation on the XML content. Specifically I'm looking to transform one XML segment.
We have an internal ERP that is creating the XML document. In the ERP the customer codes are internal and we need to transform the customer code to a list provided by the customer.
When we subscribed to the Celigo we were told that we could do this but I'm unsure on the steps involved. Any guidance would be appreciated.
All of the content should remain except that a single element called <PracticeCode></PracticeCode> should be converted and examples are:
123D1 > ON-03
123E01 > AB-055
123E7 > ON-17
etc
-
It looks like you're working with our Support team on this, which is great since they can look into your configuration and assist. I'll also follow that ticket. Thanks!
0 -
Yes, after not receiving any feedback here I opened an official ticket. Thank you.
0 -
Hi,
My way to do this is to create a flow that loads and saves the xml document as a CSV-file. That way you dont need to parse&rebuild the xml-file, which saves a lot of work. Then in a javascript-hook do a find&replace to change the text.
0 -
Hi Bas,
Is your selection of CSV-file to treat the file as a <cr> or <nl> delimited file or to simply bypass file parsing?
I will try your method as this would greatly simplify the process as the parse&rebuild process makes this harder than I would have originally imagined.
0 -
This is a great post with clever collaboration. Thanks, Sinclair Dental IT Admin and Bas van Ditzhuijzen.
If you wanted to go the most direct route (but perhaps the hardest to rewrite values on the fly), it would look like the following XML upload instructions. I'm adding full details here because this information isn't yet captured in the Help Center, and others will surely come across this thread with the same question.
- The last import step should be to upload to FTP. (+ Create destination; Application: FTP; What would you like to do: Transfer files into destination application.)
- Name the upload step, and keep the default Generate files from records set to Yes.
- Under How would you like to generate files, select XML for the File type.
Notice that an XML document entry now appears within Where would you like the files transferred. - Click at the right of XML document to open the handlebars Advanced field editor (shown below).
- Paste the expected XML format from your app's guide into the Type your handlebars template here portion of the editor.
- Based on the flow's data resources, supply the needed JSON paths as handlebars references. Preview to test the evaluated output.
Very often, you'll have to use handlebars helpers for looping, data type conversions, or logic to prepare the values for the destination app. In this case, you could use a series of {{#compare}} statements to change the PracticeCode values as needed.
This example comes from the Marketplace template Salesforce Commerce Cloud (FTP) – NetSuite in the flow "NetSuite customer refunds to Salesforce Commerce Cloud refunds."
0 -
Sinclair Dental IT Admin I parse it as as pipe-delimited file without header, and then when saving also write as a pipe-delimited file without header. That way it writes the file back the way it was originally.
Then in a presave script:
options.data.forEach(line => {
Object.entries(line).forEach(entry => {
line[entry[0]] = entry[1].replace('foo','bar');
});
});0 -
Hey Bas, this is a great work around, and thanks for sharing! I will def keep this idea in my back pocket.
Eventually, we will provide some sort of JavaScript hook to process raw files (i.e. right when they are first downloaded, or right before they get set somewhere, etc...), but not many people have asked for this, so not sure when we will build this.
0 -
Hi Bas, your example has helped us to move forward. We have now been able to import and transform the data as needed. Where we are struggling now is with the Save step where we write it back out. We don't know how to action "when saving also write as a pipe-delimited file without header'. Can you please provide a bit more detail here?
We are so close that we can taste success. :)
0 -
Hi Bas, I believe that Stephen's email covered the step that I was asking about. If this is not the case please let me know.
0
Please sign in to leave a comment.
Comments
9 comments