Split coulmn on data table based on ","

[adrian_sullivan]

hi all,

i have an excel sheet.

i have read it into a datatable
what i need to do is to get the everything in to one column. the rows with a comma should be split and the data after the column added to the datatable

it should look like this. (the order does not matter to me)

any suggestions

thanks,
Split dataTable.xlsx (11.0 KB)

[vrdabberu (Varunraj Dabberu)]

Hi @adrian_sullivan

=> Read Range Workbook

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

Regards

[Jayesh_678 (Jayesh)]

Hi,

Here I used input:-

Output:-

Please find attached workflow for your ref.
Split datatable column.zip (3.6 KB)

If this works for you, please mark this as a solution, so others can refer this.

[adrian_sullivan]

That is great, working for me.

i was trying to use an if statement and go from there but your solution is much better.

thanks,
Adrian

[adrian_sullivan]

back again, found in my data a very large number. this causes an error when using the assign activity

Split dataTable.xlsx (11.0 KB)

any suggestions to get aroung this issue

thanks again

[vrdabberu (Varunraj Dabberu)]

Hi @adrian_sullivan

Try this syntax:

dt = (From row In dt.AsEnumerable()
            From column In row.Field(Of Object)("Column").ToString().Split(","c)
            Select dt.Clone().Rows.Add(column.Trim())).CopyToDataTable()

Input

Output

Regards

[adrian_sullivan]

perfect that worked just fine

thanks

off to watch some rugby now

[adrian_sullivan]

so i am back with another compliaction to this query

Split dataTable.xlsx (11.3 KB)

i discovered that if i split coulmn3 out then i run into problems later with a join that i have for another data table.

so i will have from this data table abc,def, then from another data table abc and def and when i go to join them together they will not match up. is there anyway of getting my datatable to look like this

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

thanks

ps rugby did not go as planned…

[vrdabberu (Varunraj Dabberu)]

Hi @adrian_sullivan

Can you raise a new query regarding this, I will help you with solution.

Thanks in Advance
Regards