column name =“name,name1,name2,name3,” column will store in assign and its dynamic
above input image
output image
i need code without doing for each looping
above input image
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
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!!
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
- Read File using Read Range Activity and assign in DT as DataTable
- 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”)
- Write Range
Hope it will helps you 
Cheers!!
its throwing error Cannot set column 'Column name '. The value violates the MaxLength limit of this column
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.
Anil_G
Nidhi_Gupta1