Import text file and split into multiple outputs

[matt_r]

Hi All,

I am struggling to come up with a workflow to import and transform the attached file.

I need to be able to import the file, split the file into multiple tables (e.g. TABLE ET_ARC, TABLE ET_ARCMAT etc.) and then parse each table into multiple columns.

Any help / suggestions would be appreciated.

[DiganP]

@matt_r  I would read the text file as a fixed width text file and set the appropriate parameters based on the string length. 

[DavidP]

Right, here's the 1st of 2 posts.

In this post, I've loaded your text file, created a Table identifier and parsed the first table to columns as shown below.

Workflow attached. 

In my next post I will turn the second part of the workflow into a batch macro that will parse each of the 45 tables contained in the text file and write each table to a separate sheet of an excel file.

At the moment all the fields are still text so we'll also have to look at how to turn some of them into numbers.

[DavidP]

Here's the macro with the output file.

[DavidP · Accepted solution]

I've now fixed the number format (converted scientific format to numbers) in the macro. Please find final version attached. Let me know if you have any problems running it.

[matt_r]

Brilliant DavidP... didn't have any problems running it. It'll just take me a while to understand it :)