Join columns into one cell (dynamic)

hi i have a datatable that looks like this (note that bank name column is dynamic, may go up to >5):

I need to join the banks into one cell with a comma and this is done for all companies:
eg for ABC Pte Ltd β†’ BANK ABC, BANK CDE, BANK FGH
how can I achieve this?

Hi ,

Create a data table using built datatable with columns Company and Bank, Use For each row to loop the excel datatable and use add data row activity with {row(0).tostring,row(1).tostring+","+row(2).tostring+","+row(3).tostring} and keep the newly created datatable

HAPPY AUTOMATION !!!

1 Like

hi thank you for your reply, however this solution only works if i know the number of Bank Name columns? in my case, i won’t know the exact number of bank name columns as new entries will be added for example, number of bank name columns may go up to 10 or more. is there a way to do this without having to amend the script every time?

Hey,

You can convert datarow to array and then use String.Join(array, β€œ,”)

Hi,
Build DataTable with 2 columns in it and assign below expression


(From d In TestDT.AsEnumerable
Let ra = New Object(){d.ItemArray.First,String.Join(",",d.ItemArray.Skip(1))}
Select ResultDT.Rows.Add(ra)).CopyToDataTable
1 Like

hi this is how the actual excel sheet format looks like
sample.xlsx (36.9 KB)
given solution copies the whole data table. is it possible to create a list of bank names instead?

Okey.
Here is the updates Linq expression.
This is prepared by assuming the columns have common word Bank Name in their headers.
Change this word accordingly to combine other columns.

(From d In TestDT.AsEnumerable
Let BankCols=dtData.Columns.Cast(Of DataColumn).Where(Function (x) x.ColumnName.Tostring.ToLower.contains("bank name")).ToArray
Let CombinedCols = String.Join(",", BankCols.select(Function (x) d(x).toString).Toarray())
Let ra = d.itemArray.Take(1).Concat(New Object(){CombinedCols}).ToArray
Select dtResult.Rows.Add(ra)).CopyToDataTable

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