Extract value from Datatable and add to another column

[Sathish_Kumar_S]

Input Data table 1 :

Requirement ::

Extract value between two spaces in the from the input datatable in each row and create another column as “UserID” and place extracted values into new column and store into new or existing Data table
Input DB.xlsx (9.1 KB)
.

Keep remaining text in the ActionTaken column

Expected Output: ( in datatable format)

Expectedoutput.xlsx (9.1 KB)

[Shiva_Nikhil]

@Sathish_Kumar_S

can you try this by using invoke code activity

dt.Columns.add("UserID")
For Each row As datarow In dt.AsEnumerable
	row(1)=system.Text.RegularExpressions.Regex.Match(row(0).ToString,"\s[A-Z_0-9]+").Value
row(0)=System.Text.RegularExpressions.Regex.Replace(row(0).ToString,"\s[A-Z_0-9]+","").Trim
Next

[Sathish_Kumar_S]

Thanks a lot! Your solution works well

[sasi_poosarla (sasi p)]

@Sathish_Kumar_S

dt_excel.AsEnumerable.Select(function(x) dt_Build.Clone.LoadDataRow({System.Text.RegularExpressions.Regex.Replace(x(0).ToString,“\s[A-Z_0-9]+”,“”).Trim,System.Text.RegularExpressions.Regex.Match(x(0).ToString,“\s[A-Z_0-9]+”).Value},false)).CopyToDataTable

Cheers!!

[Sathish_Kumar_S]

How to trim the space/whitespaces in values the UserID column?

After we copied the value from “ActionTaken” column to “UserID” column… the values are containing whitespaces before the value in UserID column

[Shiva_Nikhil]

@Sathish_Kumar_S

dt.Columns.add("UserID")
For Each row As datarow In dt.AsEnumerable
	row(1)=system.Text.RegularExpressions.Regex.Match(row(0).ToString,"\s[A-Z_0-9]+").Value.trim
row(0)=System.Text.RegularExpressions.Regex.Replace(row(0).ToString,"\s[A-Z_0-9]+","").Trim
Next

can you try this by making trim at last