Split coulmn on data table based on "," an dkeep data in data table

[adrian_sullivan]

so i need to split the column(3) based on the “,”
then add a new row with all the original data except for what is before the “,”

to end up with something like this

thanks
Adrian

Split dataTable.xlsx (11.3 KB)

[vrdabberu (Varunraj Dabberu)]

Hi @adrian_sullivan

=> Read Range Workbook

Output-> dt

=> Use below syntax in Assign:

dt_Output = (From row In dt.AsEnumerable()
             Let column3Values = row("Column3").ToString().Split(","c)
             From value In column3Values
             Select dt.Clone().Rows.Add(row("Column1"), row("Column2"), value.Trim(), row("Column4"))).CopyToDataTable()

dt_Output is of DataType System.Data.DataTable

=> Write Range Workbook dt_Output

Regards

[Jayesh_678 (Jayesh)]

Hi,

Input:-

Output:-

Xaml File:-
spit column forum.zip (11.5 KB)

If this works for you, please mark this as a solution.

Thanks

[Krishna_Raj]

@adrian_sullivan

Split coulmn on data table based on ","

Hi @adrian_sullivan => Read Range Workbook [image] Output-> dt => Use below syntax in Assign: dt = (From row In dt.AsEnumerable() From column In row.Field(Of String)("Column").Split(","c) Select dt.Clone().Rows.Add(column)).CopyToDataTable() => Write Range Workbook dt [image] [image] Regards

check the thread

[adrian_sullivan]

for the example provided that works, but i have a longer version that it is not working for. when i try add extra rows it will not update correctly.

what is happening is that my coulmn3 (isin) is not being inserted into the new data table. the split is happening but the isin is not being updated

this is what i am using - any idea as to where i am going wrong
(From row In DT_Full.AsEnumerable()
Let column3Values = row(“Isin”).ToString().Split(","c)
From value In column3Values
Select DT_Full.Clone().Rows.Add(row(“Perimeter Id”), row(“Class”), row(“Sub-fund”),row(“Class Code”), row(“Type”), row(“Periodicity”), row(“Status”), row(“Template”), row(“Production deadline”), row(“Approved by DQM”), row(“Portofolio_main”), row(“Decalog Code”),row(“CP_Service”))).CopyToDataTable()

thanks again

Split dataTable.xlsx (11.7 KB)

[vrdabberu (Varunraj Dabberu)]

Hi @adrian_sullivan

Give me some time I will check and let you know

Regards

[vrdabberu (Varunraj Dabberu)]

Hi @adrian_sullivan

I have made small modifications in the query and this is working

dt_Output = (From row In dt.AsEnumerable()
    Let column3Values = row("Isin").ToString().Split(","c)
    From value In column3Values
    Select dt.Clone().Rows.Add(row("Perimeter Id"), row("Class"), value.Trim(), row("Sub-fund"), row("Class Code"), row("Type"), row("Periodicity"), row("Status"), row("Template"), row("Production deadline"), row("Approved By DQM"), row("Portofolio_main"), row("Decalog Code"), row("CP_Service"))
).CopyToDataTable()

Input:

Output:

Regards