Datatable column transformation


I am having datatable which has A,B,C…K columns.
Now i want a new data table in the way, mentioned in picture.

1 Like

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
image

or some other fance vb.net code

If this has helped you, please mark this answer as the solution.

Hi @Rahul_Tomar1

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

The data is coming from csv file. I tried you


It is showing column A is not blong to datatable

1 Like

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.