Regex Parsing ( Space & Character)

[Tommy23]

Hello,

I would like to split my data into column using regex (parse). I have a text file with data you look like this ( Dummy data Example)

2020-11-04-13.44.01.396216A61072 I9005374417

2020-11-04-16.03.26.776288A09000GSI5004320239

I would like this data to be like this 

Column1 (Size 26)                 Column2 (Size 2 )             Column3 (Size 1)      Column4(Size10)

2020-11-04-13.44.01.396216      A61072                             I                              9005374417

2020-11-04-16.03.26.776288      A09000GS                        I                              5004320239

For the first line and the Column2 : A61072 is size 8 because we want to consider the two white space as a character but it is not fill (See the picture Example1 from Notepad++ to see the character "orange point")

How can I create a regex who is going to consider all the particular case.

I know for the Column I need someting like this : (\d+-\d+-\d+-\d+.\d+.\d+.\d{6})

but I have trouble about the Colunm2 when there is space or not. I would like if it is possible a regex expression that create the four column even if there is particular case ( see column 2)

Thanks in advance for your help,

Best regards.

[atcodedog05]

Hi @Tommy23 

Since you know the size. Regex will be

Regex Tool | Parse tool.

(.{26})(.*)(.)(.{10})

[atcodedog05]

Hi @Tommy23 

Here is a workflow for the task.

Output:

Config:

(.{26})(.*)(.)(.{10})

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[PhilipMannering]

Hi @Tommy23 

This is how I'd do it,

(.*?)(A\w+)\s*(I)(\d{10})

[Tommy23]

Hello,

I am happy baout your answer, but I did'nt put all the line and it look like it is not working 

Imagine I have this line

2020-11-04-05.09.29.599024A07438 U80055492150001001CPDEC 2020-11-04I BVAS ASSM2020-11-04VTM

2020-11-04-05.25.17.142866A09000GSU00050546360001002CPCLO 2020-03-16I BVAS1 ASSM2020-11-04VTM

and I look like this 

Column1 (Size 26)               2020-11-04-05.09.29.59902

 Column2 (Size 😎                A07438 

Column3 (Size 1)                 I

Column4(Size10)                 8005549215

Column5( everything else)   0001001CPDEC 2020-11-04I BVAS ASSM2020-11-04VTM

Column1 (Size 26)              2020-11-04-05.25.17.142866

 Column2 (Size 😎                A09000GS

Column3 (Size 1)                 U

Column4(Size10)                0005054636

Column5( everything else)  0001002CPCLO 2020-03-16I BVAS1 ASSM2020-11-04VTM

I telling you this because when I try your regex it show like ttis (see picture).

Thans in advnce for you reply,

Best regards

[atcodedog05 · Accepted solution]

Hi @Tommy23 

Try this regex

(.{26})(.*)(\l)(\d{10}).*

Output:

[Tommy23]

Thanks it's working now!!

Do you know how to remove the first blank space and not the other one inside a line?

First blank space represent as (Here the first blank space) in this dummy data.

(Here the first blank space)2020-11-04-13.44.01.396216 A61072 DHHZ H123

Thansk again for your time and help.

[PhilipMannering]

@Tommy23 To remove the first blank space...

(.{26})\s?(.*)(\l)(\d{10}).*

[atcodedog05]

Hi @Tommy23 

You can use data cleansing Tool and removing leading & trailing spaces.

[MarqueeCrew]

@Tommy23 ,

structured data should be parsed with simple string functions for ease of maintenance as well as for performance considerations.  

Left([string],26)

Substring([string],26,2)

Substring([string],28,1)

Substring([string],29,10)

 these are the expressions which I would recommend

 cheers,

 mark