Assign Private Data and/or Rename Job using Reference File???

[matt.baile]

I am creating a flow that downloads files from an FTP server, renames them, stores the original and renamed names as private data, calls a SQL database, and then archives files into a hierarchy derived from the file names and private data. I've done this before so I have most of the flow built, but one of the problems is that I'm dealing with a Publisher Number, a Customer Acronym, and a Job Acronym. In order to run the SQL database call script properly, I need to have a job named using the Customer Acronym. Right now, the job is named using the Publisher Number and there is no place where all three of these acronyms/numbers exist. What I'd like to do is look up the Publisher Number and have the Customer Acronym returned as private data so that I can rename the job accordingly before the SQL database call. For example, my file is currently named:

CustomerNumbers_Pub#_volume_issue.tra (Example: 02310-00838_C295_673_1.tra)

I need some way to associate the C295 (Pub#) with ACB (Customer Acronym) so that I can rename the file as follows:

CustomerNumbers_CustAcro_volume_issue.tra (Example: Example: 02310-00838_ACB_673_1.tra)

Right now, I have a list of 400+ jobs in Excel format that I need to use, so I don't want to manually hard code those in a script, but I don't really know the best way to do this and adding the additional Pub# to the existing database is not an option. Right now my Column A is Pub#, Column B is Customer Acronym, and Column C is Job Acronym. What is the best way to do this?

[Padawan]

There are ODBC drivers for Excel, so you could handle your Excel file as a database. This way you could write an SQL expression which does a lookup in your Excel file which Pub# corresponds to which Customer Acronym and use that SQL expression as a variable in your rename job.

[jan_suhr]

You could try the Excel to XML app, it will give you XML that you can pickup for your job renaming.


Jan

[matt.baile]

I've honestly never used either of those options so this is a good opportunity to do something new!

Padawan - Do you have any examples of this or any reference material? Unfortunately, I didn't write the script to do the SQL lookup, but I can use it as a reference. I don't have any experience with this area, but it sounds like a great solution.

jan_suhr - Are you saying that I should save the Excel file as an XML document from within Excel itself and then point to that file as a reference in a SWITCH folder?

[jan_suhr]

The app will convert the Excel file to XML with a few different options for you in the Switch workflow.

You can use an XSLT-script to separate this XML to one single XML file for each row, in this case it can be named after some value in the row. Then Switch can pick up that XML file and embed its values as a dataset in to the job file. And from that you can get the values to rename the file.

Everything is done in one flow and it will treat those 400+ records and files in just a few minutes.


Jan

[matt.baile]

So I would put the XSLT transform in line with my job? I wouldn't be passing the Excel file each time, I was just looking to reference it when we received a file download from the FTP. I just added an XSLT transform element to the flow and it's asking for an XSLT stylesheet that I'm not familiar with. I honestly don't have a preference in referencing the Excel file itself through ODBC or creating an XML that I can reference. Either way is great by me, I just haven't done either of those before so I'm in the dark when it comes to those.

[jan_suhr]

If you try the Excel to XML app there is an example XSLT in the documentation that will split each row in the Excel file to one single XML-file per row.

You need the Saxonica tool with the XSLT to process the XML.


Jan

[Arthur]

If you try the Excel to XML app there is an example XSLT in the documentation that will split each row in the Excel file to one single XML-file per row.

You need the Saxonica tool with the XSLT to process the XML.


Jan

Or you can use XML Repeater telling it to split it by node.

We have a flow build which utilising Jan's Excel to XML (which is absolutely a no brainer and works like a charm) creates one joned XML and then going with XML Repeater each line of an original Excel table (however here already converted to XML, where each row makes for an individual node in the XML) makes a single XML and then pick up with Pickup XML to embed the dataset into relevant files.

[matt.baile]

Thanks for all of the replies. When I posed on the forum, I also sent a support case in to enfocus and was made aware that we need the database module to read from an Excel file using the ODBC connector. Unfortunately, we do not have this module, so the XML may be the only way to go here. I do not have the option to purchase additional apps so I would need to use something as a free trial and then not have access to it any more. Would any of the apps that have been suggested fit that purpose?