Changing column value to array and sorting

Hello,

I have a DataTable with a column with strings. Some of these values are multiple strings split by a comma. I would like to sort these values alphabetically within the datatable.

I tried using a For Each loop:

  • Assign an array by splitting the value based on a comma
  • Sorting the array
  • Writing the array back to a string by using String.Join to CurrentRow(“Column”).

This is a really big DataTable though, so it takes way too long.

Is there an easier way to do this in one go?

Thanks in advance.

Hi @Jelmer

Try with LINQ query

sortedDataTable = YourDataTable.AsEnumerable() _
.Select(Function(row) New With {
.OriginalRow = row,
.SortedValues = String.Join(", “, row.Field(Of String)(“YourColumnName”)
.Split(”,"c)
.Select(Function(value) value.Trim())
.OrderBy(Function(value) value))
}) _
.CopyToDataTable()

maybe yo can share some sample data

it would help us when more on the sorting needs will be shared with us, when it is not clear from the samples

That didn’t work for me, I got a lot of syntax errors. Is the idea to do in an invoke code or just an Assign? Thanks in advance.

The DataTable is as follows:

ID;ColumnToSort
123456;tafel,bank,stoel
234567;bank
243092;kast,stoel
120931;stoel,bank,kast

The idea is that the result is as follows:
ID;ColumnToSort
123456;bank,stoel,tafel
234567;bank
243092;kast,stoel
120931;bank,kast,stoel

I hope this makes it a bit more clear.

Lets assume, we can rely on the 2 col structure.

Give a try at:
Assign Activity:
dtSorted = dtOrig.Clone

Assign Activity:
dtSorted =

(From d In dtOrig.AsEnumerable
Let ars = d("Col2SortName").toString.Trim.Split({","}, StringSplitOptions.RemoveEmptyEntries)
Let sfo = String.Join(",",ars.OrderBy(Function (x) x))
Let ra = New Object(){d(0), sfo}
Select r = dtSorted.Rows.Add(ra)).CopyToDataTable

for sure we can also adapt.

Order Crosscheck
grafik