Join columns into one cell (dynamic)

[aly]

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?

[Krishna_547 (MudhuKrishna)]

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 !!!

[aly]

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?

[artshoque (Yuriy Tymkiv)]

Hey,

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

[poorna_nayak07 (Poornachandra M)]

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

[poorna_nayak07 (Poornachandra M)]

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