I am having datatable which has A,B,C…K columns.
Now i want a new data table in the way, mentioned in picture.
You would have to manually deal with the datatypes though. Sorry don’t know any other easier way of reordering the column as a whole apart from rebuilding the table in a different order - anyone else?
Not totally sure what the goal is, do you want to rename the columns or reorder them (or both)?
For simply reordering, you can use
dataTable.Columns["Qty"].SetOrdinal(0);
This simply changes the position of the column.
If you want to rename it, you can do something like this in an assign activity:
dataTable.Columns["C"].ColumnName = "B"
For deleting columns I’d use the activity Remove Data Column
And at last, combining columns can be done either with a simple for each row loop
or some other fance vb.net code
If this has helped you, please mark this answer as the solution.
Hope these steps would help you resolve this
- use excel application scope and read it and save it as datatable dt
-now use a BUILD DATATABLE activity where create a table structure with set of columns we want and get the output as dt_final
-then use a FOR EACH ROW activity and pass the variable dt as input
-inside the loop use ADD DATAROW activity
-in add datarow activity, mention the column values in arrayrow property, the one you want to have in the new datatable like this
arrayrow property as
{row(“Columnname-A”),row(“Columnname-C”),row(“Columnname-E”),row(“Columnname-D”).ToString+row(“Columnname-E”),row(“Columnname-K”)}
and mention the dt as dt_final
now your new datatble will have the new columns created with the values you want to have
Please let know for any clarification
Cheers
Can i do all these actions in a single go.
No worries
Instead of mentioning the column name as A, B in row(“A”) row(“B”) we can mention the columnindex like this along the ArrayRow Property
row(0) for column A
row(1) for column B
Where column index starts from 0 for first column
So let’s mention column index instead of column name in array row property
Pls let know for any further clarification
Cheers @Rahul_Tomar1
Sure, the good thing about my way is that you don’t need for-loops.
So you’d do
removeDataColumn("B") //remove columns first
removeDataColumn("F")
etc.
// Here you have do do the for loop from the picture above
for each row in dataTable
CurrentRow("D") = CurrentRow("D)".toString + currentRow("E")
dataTable.Columns["C"].ColumnName = "B" //then change names
dataTable.Columns["E"].ColumnName = "C"
dataTable.Columns["K"].ColumnName = "E"
After that you don’t even have to reorder stuff.
So first delete all the columns you don’t need, then combine D+E, and lastly change all the names of the remaining columns.
If this has helped you, please mark this answer as the solution.