Remove columns dynamically from a datatable

Hi to all,

I am having a process that is creating a template based on a mapping read in excel ( Example below )

When creating the template, the process reads 2 files: one of them is the mapping from above and the other one is another excel with variable sizes ( rows & columns );
From the second excel ( i have to keep only the columns that are mentioned in the attribute values from mapping excel.
For this, I am creating a list based on what I read from the mapping file, like below:

Use Remove Data Column in the DataTable activity.

For this, I am creating a list based on what I read from the mapping file, like below:

image

Now, from my second excel file, I want to keep only the columns that are in the out_ColumnsToKeep list.
For now, I have them semi-hardcoded, but I want to solve this.
image

I tried creating an array of columns and then using the invoke Method to remove the columns from the target dataTable, but somehow it does not work.

Hello @KannanSuresh,

Thank you for your suggestion.
I will try it and keep you up-to-date!

Regards,
Radu

Hi @radu.spantu

You can first read both excel sheets into separate data tables.
Now, get the column names of the other datatable onto an array as shown in this post

Now, loop through the attributes of the datatable that holds mapping data using a for each row.
Inside, use another for each activity to loop through the array of column names of the other table.
Within it, use an IF condition to see whether the column names are matching. If matching, that means your mapping sheet has the column that you want.

based on the result, use the Remove Data Column activity to remove the unwanted columns from the data table.

1 Like

Hi, @Lahiru.Fernando ,

Thanks for the support.
Both of my excels are stored in 2 data tables variables: MappigTable and OrderTable.
Now, my mapping table has other values also, like cell addresses, constant values, and blanks.
From the MappingTable I am saving only the data that contains Column, just to have the Columns to keep:

Now looping through the 2 tables in order to see if the columns match. If they don’t - remove from OrderTable

After the process removes the dataColumn, it throws an error:" For Each clmn in OrderTable: Collection was modified; enumeration operation may not execute.". What am I doing wrong?
Is it mandatory to use arrays? I am new to RPA and i have no previous vb.net or c# experience so using List of DataColumn was easier for me.

Thank you, again, for your support!

Hi,

Conclusion of what i found out: you cannot copy column entirely ( name & holding data ).

Here’s how i solved it:

  1. From the List i created to hold my ColumnsToKeep, i created out_OrderTable to hold those columns.
  2. Then, for each row in my OrderTable, i created a new row in my destination order table ( out_OrderTable).
  3. For each (in the ForEachRow) column in out_OrderTable.columns, assign value from the targetDatatable ( my case : OrderTable )
  4. Add dataRow:

image

Hopefully the next to visit this page will spend less time documenting on this.

Regards,