Need help to change string to correct data table

[Ahmad_Rais (Ahmad)]

Hi Everyone ,
i have a “string” input with fixed "column named ",
i really need to change this string into a data table , maybe using regex or i really prefer using
“Generate Data Table from String” activity

S.Number Contract …Item Customer/Vendor Name Qty deduct Ctr* Material Material Description
P1 81123456789 Wumbo Wumbo LTD New 5,250 6.7212121 Wumbo is new Wumbo No
P2 81123456789 Wumbo Wumbo LTD OLDS 750 6.721212A Wumbo is new Wumbo Yes
P3 81123456789 Wumbo Wumbo LTD Yes 1,600 6.7222222 Wumbo is new Wumbo Maybe

Header is :

• S
• Number
• Item
• Customer/Vendor Name
• Qty deduct Ctr
• Material
• Material Description

Thanks

[ppr (Peter Preuss)]

we do feel that the heades and the data have some different schema e.g.

SNO	Contract	Vendor	Qty	Matrial	Descr	Unknown Col
P1	81123456789	Wumbo Wumbo LTD New	5,250	6.7212121	Wumbo is new Wumbo	No

In general we have the approach of

• set markers with different Regex, String method techniques
• also can be done in a chain

Once all data is marked, then parse it e.g. generate DataTable or other approaches

Example Marker setting as 1 step in a chain:

next step:

so unclear / remaining is the Yes, no, maybe part

Afterwards we just trim the column values if wished

[Ahmad_Rais (Ahmad)]

Hi PPR,

thanks for replying , i want to clear up some of the RAW data

S.Number Contract …Item Customer/Vendor Name Qty deduct Ctr* Material Material Description
P1 81123456789 Wumbo Wumbo LTD New 5,250 6.7212121 Wumbo is new Wumbo No
P2 81123456789 Wumbo Wumbo LTD OLDS 750 6.721212A Wumbo is new Wumbo Yes
P3 81123456789 Wumbo Wumbo LTD Yes 1,600 6.7222222 Wumbo is new Wumbo Maybe

expected output

Would you be kind enough to help me on the steps by steps to generate this?

thankn you

[ppr (Peter Preuss)]

explore the above mentioned approach for more samples as

@Ahmad_Rais

i want to clear up some of the RAW data

as we could drive the same strategy of marking, parsing, cleansing

[Ahmad_Rais (Ahmad)]

Thanks for the suggestion ppr,

but at the moment i lack the skills to made a good Regex,
im looking for complete solution

but your suggestion has already help me a lot
Thank you , really appreciate it

[ppr (Peter Preuss)]

@Ahmad_Rais

but at the moment i lack the skills to made a good Regex,

when new to regex cascading and marking can be more easy to get it done instead of writing a All-In-One regex

Have a look on this approach exploiting groups and substitution:

^([A-Z])(\d)( [\d]+)(\d)(.*?)([\d,]+)([\d. ]+)(.*)

[ppr (Peter Preuss)]

In addition to our PM chat:

[CheatSheet] - System.Text.RegularExpressions | RegEx

This CheatSheet introduces the basic use of regex functions. With further examples also special cases are presented. Introduction From the namespace System.Text.RegularExpressions following methods are offered: Regex.Match Regex.Matches Regex.isMatch Regex.Replace Regex.Split A simple usage for example would look like this: [grafik] Recommendation: add System.Text.RegularExpressions to the imports: [grafik] it allows to use the shortened statement, as the namespace part can be ommited…