Parse data from text file

[vvissamsetty]

Hi Guys,

I have data in the following format and wish to extract characters from this based on character limit into multiple columns.

AAAA  1234567890JOHNNEWYORK

I have multiple columns into which this data has to be extracted based on the character count like - first 4 characters should go into column 1(ID), next 10 characters should go into column 2 (Phone Number), next 4 characters into column 3(First Name), next 15 characters into column 4(City) and so on, all of this data is in a text file. Can anyone guide me on how to go with this?

Thank you

[RolandSchubert · Accepted solution]

Hi @vvissamsetty ,

in the INPUT tool, you can set the option "Read it as a fixed width text file" :

In the following step, you set the field width:

Would that solve your problem?

Best regards

Roland

[vvissamsetty]

Thanks @RolandSchubert, that helped.

[benakesh]

Hi @vvissamsetty ,

This is  Regex parse method .

(.{4})\s\s(.{10})(.{4})(.{5,15})    ( 4 char id ,  2 spaces  , 10 char phone , 4 char name and  5 to 15 char city .   

[Rob48]

I just used the first solution that RolandSchubert gave with a .txt file I have, but didn't account for the fact my source data has a first line/row of garbage that I wanted to ignore.  Now all the field width settings I made (60 fields of varying length across 600 characters) is parsing up the first row of garbage.  Is there a way to ignore the first row and still use this solution?  Or am I stuck not using this easy (but initially time-consuming) solution and instead creating 60 separate formula tools to parse up my data?   below is the current settings.  Thanks!