Split datatable variable by Nth occurance of character

Hi there,

Does anyone have any advice on how I can split the data in my datatable by the Nth variance of a character, and then add the new split data to a new row?

I would like to split my data by the 2nd occurrence of a comma, so the end output would be like this:

Original Data in DT:

|VALUE1’, ‘VALUE2’, ‘VALUE3’, ‘VALUE4’| Test Data 1 | Test Data 2 |

Desired output:

|VALUE1’, ‘VALUE2’ | Test Data 1 | Test Data 2 |
|‘VALUE3’, ‘VALUE4’ | Test Data 1 | Test Data 2 |

If anyone has any advice it’d be greatly appreciated - thank you!
Sam

we assume that it is a string, so we can split by split method.
myString.Split(","c) feel free to trim, cleansing the splits

When Working in Windows compatibility we can do:
grafik

This output along with a nested loop can be used for creating the desired output

A LINQ Approach could look like this:

Assign Activity:
dtSplit =dtOrigVar.Clone

Assign Activity:
dtSplit =

(From d in dtOrigVar.AsEnumerable()
From sp in d(0).toString.Trim.Split(","c).Chunk(2).Select(Function (x) String.Join(",",x))
Let ra = new Object(){sp, d(1), d(2)}
Select r = dtSplit.Rows.Add(ra)).CopyToDataTable
2 Likes

Hi,
I am testing this approach but getting some errors:
1/ Compiler error(s) encountered processing expression
“(From d In dt1.AsEnumerable()
From sp = d(0).toString.Trim.Split(”,“c).Chunk(2).Select(Function (x) String.Join(”,“, x))
Let ra = New Object(){sp, d(1), d(2)}
Select r = dt2.Rows.Add(ra)).CopyToDataTable”.
‘In’ expected.

2/ After replacing “=” by “in” I am gettgin the following
Compiler error(s) encountered processing expression “(From d In dt1.AsEnumerable()
From sp In d(0).toString.Trim.Split(”,“c).Chunk(2).Select(Function (x) String.Join(”,“, x))
Let ra = New Object(){sp, d(1), d(2)}
Select r = dt2.Rows.Add(ra)).CopyToDataTable”.
‘Chunk’ is not a member of ‘System.Array’.

What am I doing wrong?

Thank you for this catch, we updated the Statement

this operator was introduced at

Ensure that your Project is set to Windows and is using this .Net Level

For older versions we would model with, take, skip and Math.Ceiling for the segment count

1 Like

For those interested this is an alternative for earlier .NET versions:

dtSplit =

(From d In dtOrigVal.AsEnumerable()
From sp In d(0).toString.Trim.Split(","c).Select(Function(s, i) New With {Key .Value = s, Key .Index = i}).GroupBy(Function(x) x.Index \ 2).Select(Function(grp) grp.Select(Function(x) x.Value).ToArray()).Select(Function (x) String.Join(",", x))
Let ra = New Object(){sp, d(1), d(2)}
Select r = dtSplit.Rows.Add(ra)).CopyToDataTable
2 Likes

As a variation using ceiling, skip take

(From d In dtOrigVar.AsEnumerable()
Let spl = d(0).toString.Trim.Split(","c)
Let sc = CInt(Math.Ceiling(spl.Length / 2))
From sp In Enumerable.Range(0,sc).Select(Function (x) String.Join(",", spl.Skip(x*2).Take(2)))
Let ra = New Object(){sp, d(1), d(2)}
Select r = dtSplit.Rows.Add(ra)).CopyToDataTable
2 Likes

Thanks @ppr and @J0ska - these have worked great and done exactly what I’ve needed it to do! Thanks again for your help!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.