Fixed width text file - set widths with reference table

[TDurell]

I have 6 different groups of text files. All have set widths for the column, but the widths differ depending on the group. I have the widths of each column of each group set out in a table.

Is there a way of using this table/values from the table as an automatic reference when importing the txt files or will widths just have to be set manually? (i.e. [if the text file is from group 1 - it will have 4 columns of widths: 45, 15, 20, 150], [if the text file is from group 2 - it will have 5 columns of widths: 20, 12, 35, 20, 75], etc.)

[echuong1]

What is the purpose of the widths? Is it for presentation purposes for your output, are you just trying to get it so your columns fit the data it contains without having to be expanded?

If this is for your output, you can actual specify the widths in the table tool. You can either use automatic so the size fits the data, or you can specify one. You'd use a Render at the end to actually output the data.

Hope this helps!

[TDurell]

Hi echuong1, sorry I don't think I explained my problem very well.

When I bring in a normal text file I want to define the columns to filter.

So I want to take this data in the text file:

And using the import tool, read it as a fixed width text file, like this:

But without having to manually set the column widths each time. I want to use a table to automatically set the field sizes to 8, 57, 25, etc. when importing the data. So then its in an easy to use format separated into columns.

I hope this helps

[echuong1 · Accepted solution]

So in that case what you can do is import the data without any delimiters. Also, make sure to read in the headers as actual data - you'll parse these later on.

Once the data is imported, use a Regex to delimit. Essentially, you'll make a marked group per field (this will be signified by parenthesis). Inside of the parenthesis will be a period (a wildcard) and a number in {}. The number signifies the number of characters. In my case I used (.{20})(.{10})(.{10}+), which means it is looking for 20 characters, 10 characters, and 10 characters or more. I will generally make the last one shorter and include the plus, to account for shorter values in the last column. 

From there, I used a data cleansing to remove the extra spaces. A dynamic rename bumps the headers up to the proper position and a Select removes the original, unparsed field.

Hope this helps!

[TDurell]

Perfect, exactly what I was looking for. Thank you for your help