Split the values from Data table cell and create new columns

[Sathish_Kumar_S]

test.xlsx (7.1 KB)

Attached the excel file data that will be available in Data Table.

Is it possible to split “Created:” and Date & “Auto process Date” and Date from “New Column 0” in the DT. and create two new columns 1.Created 2. Auto process Date and add the respective values

Expected output:

[Yoichi]

Hi,

How about the following sample?

dtResult = dt.AsEnumerable.Select(Function(r) dtResult.LoadDataRow({System.Text.RegularExpressions.Regex.Match(r(0).ToString,"(?<=Created\D+)\d+/\d+/\d+").Value,System.Text.RegularExpressions.Regex.Match(r(0).ToString,"(?<=Auto Process Date\D+)\d+/\d+/\d+").Value,r(1),r(2)},False)).CopyToDataTable()

Sample20230927-6L.zip (8.5 KB)

Regards,

[tazunnisa.badavide (Taj)]

get.xaml (11.8 KB)
Here is the xaml,try out
—>output

[Shiva_Nikhil]

@Sathish_Kumar_S

hi

try this
newexceldata.zip (3.6 KB)

cheers

[Sathish_Kumar_S]

How to sort the final DT in descending order based on “Created” Column and store in to DT.

[Yoichi]

HI,

How about the following sample?

 dtResult = dt.AsEnumerable.Select(Function(r) dtResult.LoadDataRow({System.Text.RegularExpressions.Regex.Match(r(0).ToString,"(?<=Created\D+)\d+/\d+/\d+").Value,System.Text.RegularExpressions.Regex.Match(r(0).ToString,"(?<=Auto Process Date\D+)\d+/\d+/\d+").Value,r(1),r(2)},False)).OrderByDescending(Function(r) CDate(r("Created").ToString)).CopyToDataTable()

Sample20230927-6L (2).zip (9.8 KB)

Regards,

[Sathish_Kumar_S]

Input.xlsx (10.5 KB)
Thank you for your support.

Added few extra columns in input file … and few values are missing in output file

Attached both new input file & result
result.xlsx (6.9 KB)

Expected output :

[Sathish_Kumar_S]

Can you check my request?

[Sathish_Kumar_S]

Which name spaces needs to import to use this method in another project?

[Yoichi]

Hi,

Can you check the following sample?

Sample20230927-6Lv3.zip (21.7 KB)

Regards,