How to copy specific columns from one DataTable to another?

Help
1

How to copy specific columns from one DataTable to another?

I have a MS Excel sheet downloaded from a SAP server through a workflow which have 146 columns.

146columns
146columns1339Ă—370 13.3 KB

I need only 5 columns from those. I am able to create a DataTable through Excel Application scope and Read Range.

5 (+1, which I will create later) columns:

5%201columns
5%201columns783Ă—443 11.7 KB

As per this comment,

I tried to populate the new Data Table:

var_columnNames = {"Name"}
dt_new = new DataView(dt_input).ToTable(false, var_columnNames)

But it seems three consecutive columns have the same name as Payment in and UiPath shows an error for duplicated Column Names. So I am trying with Add Headers as unchecked.

Can you suggest me if I am on the right track or a better approach?

2

If you just want to remove some columns, you can just delete from the DataTable variable and write back to excel using methods like DataTable.Columns.Remove(“columnName”) or by index DataTable.Columns.RemoveAt(columnIndex).

1 Like
3

Thanks @bcorrea . That would be tedious as I just want to keep 5 columns out of 146. Any other suggestions?

4

and i believe is not like the columns that you want to keep are next to each other right?

1 Like
5

Yes @bcorrea You are right. The columns that I want to keep aren’t next to each other.

6

ok, since the only way will be remove the columns you dont want, see attached solution that you can invoke passing the columns to keep as easy parameter: CleanDataTable.xaml (5.4 KB)

4 Likes
7

Hi @debanjan,

Greetings…!

Please use Filter data table activity,

Example: GetSpecificColumns.xaml (6.9 KB) DataIn.xlsx (9.9 KB)

Thanks!

3 Likes
8

Thanks @bcorrea your shared workflow looks promising. So this VB code will remove the columns with respect to columnNames. Now before I ask, how to pass the columnNames which I want to keep, can we tweak the code block to keep the columns with respect to the column index e.g. column1, column5, column17, column3?

9

like this: {“Iwantthiscolumn”,“pleasekeepthisonetoo”,“i love this column”}

Capture
Capture739Ă—330 26.2 KB

2 Likes
10

Thanks again @bcorrea I think this workflow will solve my issue but before applying it I have to ensure the DataTable gets populated properly (which was an initial barrier due to duplicate column name within the downloaded excel from SAP) . Please bear with me.

11

you sure will have error if you have duplicate excel columns, but you can write something to rename duplicates before reading it as datatable.

1 Like
12

Thanks @bcorrea I am out of words, all I can say "you are a gem ". Thanks again for your time and all the help.

2 Likes
13

Thanks @kadiravan_kalidoss I will take up your solution shortly and update you accordingly.

14

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