Based on two column value we need concatenate remain column value

column name =“name,name1,name2,name3,” column will store in assign and its dynamic


above input image

Screenshot 2023-12-28 003946
output image

i need code without doing for each looping

Create a variable list(of string) (yourList)

  • For Each Row in Data Table
    – For Each in yourDT.Columns
    — If Left(currentItem.ColumnName,4).ToUpper = “NAME”
    ---- Then Add to List CurrentRow(currentItem.ColumnName)
    – /end columns for each
    – Assign CurrentRow(“name”) = String.Join(“,”,yourList)
  • /end For Each Row

Hi @Jijendiran_Murugan

You can find the expected output in the sheet2 of excel.

Forum.zip (230.0 KB)

Hope it helps!!

Hi @Jijendiran_Murugan

Check on the below post

Cheers!!

Hi @Jijendiran_Murugan

You can use the Linq expression to get the output in excel file.

Take a Read range workbook activity to read the excel and store in a datatable called dtInput.
After Read range workbook, use the Invoke code activity to run the vb code. Give the below code in the Invoke code activity.

Dim updatedDataTable = dtInput.Clone() ' Creates a new DataTable with the same structure
For Each row As DataRow In dtInput.Rows
    Dim newRow As DataRow = updatedDataTable.NewRow()
    newRow.ItemArray = row.ItemArray
    newRow("Name") = String.Join(",", {row("Name"), row("Name1"), row("Name2")})
    updatedDataTable.Rows.Add(newRow)
Next
dtOutput = updatedDataTable
 
dtOutput.Columns.Remove("Name1")
dtOutput.Columns.Remove("Name2")

In arguments create two arguments dtInput and dtOuput as below

After that use write range workbook activity to write the dtOuput to excel.

Check the below image for workflow

Hope it helps!!

Hi @Jijendiran_Murugan

Please check the below thread

Regards

1 Like

Hi @Jijendiran_Murugan

You can also try using macros Invoke VBA

Forum.zip (220.8 KB)

Hope it helps!!

column name is dynamic we cant able to mention name row(“”)like that each it may change

column name is dynamic we cant able to mention name row(“”)like that each it may change

Instead of using the Names of the Columns use the below code in Invoke code Activity.

Dim updatedDataTable = dtInput.Clone() ' Creates a new DataTable with the same structure
For Each row As DataRow In dtInput.Rows
    Dim newRow As DataRow = updatedDataTable.NewRow()
    newRow.ItemArray = row.ItemArray
    newRow(2) = String.Join(",", {row(2), row(3), row(4)})
    updatedDataTable.Rows.Add(newRow)
Next
dtOutput = updatedDataTable
 
dtOutput.Columns.Remove(3)
dtOutput.Columns.Remove(4)
1 Like

Hi @Jijendiran_Murugan

  1. Read File using Read Range Activity and assign in DT as DataTable
  2. Take Assign Activity and use below expression

DT= (From row In DT.AsEnumerable()
Let AllNames = String.Join(", ", {row(“name”).ToString(), row(“name1”).ToString(), row(“name2”).ToString()}.Where(Function(name) Not String.IsNullOrWhiteSpace(name)))
Select DT.Clone().Rows.Add(row(“class”), row(“subject”), AllNames)).CopyToDataTable().DefaultView.ToTable(False, “class”, “subject”, “name”)

  1. Write Range

Hope it will helps you :slight_smile:
Cheers!!

its throwing error Cannot set column 'Column name '. The value violates the MaxLength limit of this column

@Jijendiran_Murugan

Its working fine for me
INPUT
image
OUTPUT
image

Check Column Name Again & Try :slight_smile:

Assign_linq: Cannot set column ‘column name’. The value violates the MaxLength limit of this column. its showing error

I have updated the code here, use this one @Jijendiran_Murugan

Dim updatedDataTable = dtInput.Clone() ' Creates a new DataTable with the same structure

For Each row As DataRow In YourDataTable.Rows
    Dim newRow As DataRow = updatedDataTable.NewRow()
    newRow.ItemArray = row.ItemArray
    newRow(2) = String.Join(",", {row(2).ToString(), row(3).ToString(), row(4).ToString()})
    ' Assuming columns at indices 2, 3, and 4 are "Name," "Name1," and "Name2" respectively
    updatedDataTable.Rows.Add(newRow)
Next

dtOutput = updatedDataTable

' Remove the "Name1" and "Name2" columns from the DataTable
dtOutput.Columns.RemoveAt(4) ' Remove column at index 4 (Name2)
dtOutput.Columns.RemoveAt(3) ' Remove column at index 3 (Name1)

Check the output below,

Hope you understand!!

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