DataSplit

[NiranjanK1]

Hi All,

I would like to split below data into multiple columns. I have tried it is working for few rows and rest of the rows i got blanks details.

how can archive. please suggest.

K20    UE COMM REAL   DVD    0  02/04/22  03/09/21         0.0035000000           9.0035000000

660    TI COMM REAL   DVD    0  02/04/22  03/09/21         0.5000000           0.0023000000

ML0    PI FLUID REAL   DVD    0  02/04/22  03/09/21         9.0035900000           0.0022000000

QQ0    TH GLOBAL REAL REAL 0   DVD    0  02/04/22  03/09/21         5.0015000000           0.0012000000

3ZC    IIFL FIN REAL   DVD    0  02/04/22  03/09/21         4.0035000000           0.00321000000

GR1    HA COMM REAL   DVD    0  02/04/22  03/09/21         0.0003000000           0.007600000

CW3    VA BE REAL   DVD    0  02/04/22  03/09/21         1.0035900000           2.0035000000

How can i create above data into 8 new Fields

Thanks,

NR

[SPetrie · Accepted solution]

Regex is your best bet.

Looks like the spacing and some of the widths are constant so an expression like this may work for you. I hard coded DVD since that was the only value I saw in that column, but it can be changed if needed.

(.{3})\s{4}(.+)(DVD)\s{4}(\d)\s{2}(\d{2}\/\d{2}\/\d{2})\s{2}(\d{2}\/\d{2}\/\d{2})\s{9}(\d.\d+)\s{11}(\d.\d+)

[SPetrie]

Another option that doesnt look quite as messy is replacing double spaces with a new delimiter and then doing a txt to columns on that.

I attached a workflow with both the regex and text to columns methods.

[binuacs · Accepted solution]

@NiranjanK1 Another method

[NiranjanK1]

@SPetrie @binuacs  Thank you for the solution, but Where ever RegExOut2 lenght max 25 RegExOut3 field value coming in RegExOut2, how to fix it for max lenght

[binuacs]

@NiranjanK1 Can you provide some sample data by mimicking the above scenario?