How can I delete multiple columns from an Excel file?

Help
1

I tried using “Remove a Data Column” multiple times but something tells me that there is a more elegant way of getting this done. Also, it keeps giving me errors. I saw another solution that used arrays but I am not sure about the syntax.

Thank you in advance!

1 Like
2

@TasneemA

use Insert/Delete Columns Activity to delete specific no. Of columns from Excel file.
Please check below thread for more Info.

3

Hi @TasneemA,

Use the below expression in assign statement and store the output in datatable.

Datatable.DefaultView.Totable(False,“Column1”,“Column3”,“Column4”)

In the above expression just mention which all columns you need and it will automatically remove the other columns.

1 Like
4

Hi.

Either way you do it (ie .ToTable(), using Remove Data Column activity, or ExcelInsertDeleteColumns), you will need to know which columns you need to delete. This will probably require declaring them globally (from a config or Array variable).

Let’s assume you use an Array variable, then it might simply look like this (not much .net coding needed):

Declare variable: columnsToRemove = {"Col1", "Col2", "Col3"}

For Each col In columnsToRemove // TypeArgument as String
    If dt1.Columns.Contains(col)
        Remove Data Column using col as ColumnName

or to delete all but specified columns, it would be like this:

Declare variable: columnsToKeep = {"Col1", "Col2", "Col3"}

For Each col In dt1.Clone.Columns // TypeArgument as DataColumn
    If Not columnsToKeep.Contains(col.ColumnName)
        Remove Data Column using col.ColumnName as ColumnName

So, I believe those are the methods I have used in the past.

If you use .ToTable(), then you can use same array like this:

Assign dt1 = dt1.DefaultView.ToTable(False, columnsToKeep)

I’m not familiar with the other activity.

1 Like