Parse data from text file

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

Input

Tips and Tricks

Parse

Macros

Accepted answers

RolandSchubert

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

All comments

RolandSchubert

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 .

regex2.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703