Converting Multiple Fixed Width Text Files to Excel

Question

I am trying to convert about 60 fixed width text files into Excel files, preferably within the same workbook but on different tabs, but a new workbook for each text file would also be helpful. I tried using a simple workflow of "Input data" to "Output", but that was not extremely useful as I still had to manually enter the text file and set the fixed width limits. Is there a way that I can enter all the text files at once and have them all output into Excel files? From what I can tell, the fixed widths seem to be consistent across files.

ArtApa · Answer

Hi @wlyman - Two tools should be able to do the job for you, if configured right. The key points for consideration:

1) Use either "*" or "?" in the connection string to read multiple files.

2) Output file name as field.

3) Change file/Table name, when writing into xlsx:

I attached a sample for your convenience. If schema is the same it should work for all files.

Hope that helps.

TXT2XLSX.yxmd

Book2 - Copy (2).txt

Book2.txt

Book2 - Copy.txt

mbarone · Answer

Yes, if the fixed width files are ALL IDENTICAL, then first you need to start with a Directory tool (and perhaps a few others) to get the full path of all the files you want.  Then feed that list into a Dynamic Input tool .  Within the Dynamic Input tool , you set up a template for, say, the first file in the list (see the link provided on setting it up).  Make sure on the template to select "output file name as field" option and then you'll have your information on each file to create separate tabs.  A bit more to it than that, but first try to get that Dynamic Input tool working - let us know if you have trouble or get stuck.