Text to columns by specs not a delimiter

[MemphLantAntonio]

Hi all,

This seems like it would be text to columns but I am not trying to separate by a delimiter, but rather specs given for a txt file (attached). How do I set it so, for example, the first 9 characters are a column, the next 11, etc?

[ShankerV]

@MemphLantAntonio 

You can do this with the help of Regex tool

Many thanks

Shanker V

[PanPP]

Hi @MemphLantAntonio 

Can you provide another screenshot of what your output should look like?

[ShankerV]

Hi @MemphLantAntonio 

Please make use of the below.

Regex Function:

(.{9})(.{1})(.{16})(.{8})(.{40})(.{40})(.{40})(.{40})(.{33})(.{2})(.{5})(.{2})

Each () will help you to create a new column, so first 9 characters will read in column 1.

Nest 1 character (10th character) will read in column 2.

and so on.

Many thanks

Shanker V

[ScottE · Accepted solution]

Hi @MemphLantAntonio,

You're going to want to read the data in as a flat file (typically .txt) using the Input Data Tool, and then use the fixed width option when presented with the resolve file type interface.

Then you'll be able to either upload your mapping file or parse it out manually.

Hope that helps,

Scott

[ShankerV]

Hi @MemphLantAntonio 

As per the below screenshot use Regex tool.

Colum to Parse -> Select your column which you read the file in the input.

Regular expression -> (.{9})(.{1})(.{16})(.{8})(.{40})(.{40})(.{40})(.{40})(.{33})(.{2})(.{5})(.{2})

Output method -> Parse

Output Columns -> It will create and split into columns, where you can rename the column output if you need.

Each () will help you to create a new column, so first 9 characters will read in column 1.

Nest 1 character (10th character) will read in column 2.

and so on.

Many thanks

Shanker V

[MemphLantAntonio]

This is what I needed, thank you!