Hello,
My DataTable looks like this:
In my automation, I want to merge the row values under each column into a single row, with each value being separated by a comma. This is what the DataTable should look like:
Can anyone advise the best way to do this?
For Each Row through the datatable and add each value to a separate array, so you end up with ProdArray {123,456,789} and PriceArray {1.2,4.3,5.3}
Then use Join to concatenate the arrays into a string. Then add those strings as a row in the datatable.
Yoichi
(Yoichi)
May 5, 2022, 1:20pm
3
Hi,
How about the following?
dt = {dt.Clone.LoadDataRow(dt.Columns.Cast(Of DataColumn).Select(Function(c) String.Join(",",dt.AsEnumerable.Select(Function(r) r(c.ColumnName).ToString))).ToArray,False)}.CopyToDataTable
Regards,
Yoichi:
dt = {dt.Clone.LoadDataRow(dt.Columns.Cast(Of DataColumn).Select(Function(c) String.Join(",",dt.AsEnumerable.Select(Function(r) r(c.ColumnName).ToString))).ToArray,False)}.CopyToDataTable
Thank you for your response, I like this solution very much. With this line, how would it be possible to specify the column names? For example if I have four columns, but only want the “Product Number” and “Price” to be merged?
Yoichi
(Yoichi)
May 5, 2022, 1:56pm
5
Hi,
Do you want to create datatable which has 2 columns? If so, the following will work.
targetCols={"Product Number","Price"}
dtResult = {dt.Clone.LoadDataRow(dt.Columns.Cast(Of DataColumn).Select(Function(c) String.Join(",",dt.AsEnumerable.Select(Function(r) r(c.ColumnName).ToString))).ToArray,False)}.CopyToDataTable.DefaultView.ToTable(False,targetCols)
Regards,
That works great, thank you. What if I want to keep all four columns, but only have the target columns merged?
Yoichi
(Yoichi)
May 5, 2022, 2:07pm
7
Hi,
In this case, you need to clarify what value should be in remaining columns.
If it’s first rows value in remaining columns, for example, the following will work.
dtResult = {dt.Clone.LoadDataRow(dt.Columns.Cast(Of DataColumn).Select(Function(c) if(targetCols.Contains(c.ColumnName),String.Join(",",dt.AsEnumerable.Select(Function(r) r(c.ColumnName).ToString)),dt.Rows(0).Item(c.ColumnName))).ToArray,False)}.CopyToDataTable
Regards,
system
(system)
Closed
May 8, 2022, 4:55pm
9
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.