How to remove multiple columns from an excel datatable?

[sans.12 (SANDEEP PUPPALA)]

Hi,

I want to remove multiple columns for an existing datatable from an excel. Is there any way to do this??

Thanks in advance.

[ranjith]

Hi @sans.12,

You can use Remove Data Column activity to delete the datatable column by its index number or by its column name.

Regards,
Ranjith.

[sans.12 (SANDEEP PUPPALA)]

Thank you for the solution @ranjith. It worked.

[Snowman]

Hi @ranjith,

Can I put multiple columns into the ColumnIndex field as I have too many columns to remove ?

Many thanks
Snowman

[aksh1yadav (AKSHAY YADAV)]

@Snowman

as I have too many columns to remove ?

Instead of that i would prefer to use Dataview.

For more information look into this thread:

How to get data table with selected columns from excel sheet

well you can use excql as a data source and use queries but i have one more way to do this here for example purpose it will show only two columns data i.e. Date and Close Column Datatable Results_of_two_cols = Yourdatatable.Select().CopyToDataTable().DefaultView.ToTable(False, “Date”, “Close”) Note - If you wants rows that have distinct values for all its columns then just set DefaultView.ToTable(True, “Date”, “Close”) Regards…!! Aksh

How to get data table with selected columns from excel sheet

For your Reference @abskulkarni - sample_for_kulkarni.zip (3.3 KB) This will write the new datatable to an output.xlsx file in the same directory. Regards…!! Aksh

Regards…!!
Aksh

[Dominic]

@Snowman, You can’t give multiple column Index/Name in Remove Data Column activity.
Whereas we have an alternate with the combination of LINQ->Datatable

1. Assign → String Array arrayOfColumnsToBeRemoved = {“Column1”,“Column2”}

2. Assign → Select a Array of ColumnNames without these columns from the datatable dt with the query
   string Array arrayOfColumnsToBeSelected = (From col in dt.Columns.Cast(of System.Data.DataColumn) where not(arrayOfColumnsToBeRemoved.Contains(col.ColumnName)) Select CStr(col.ColumnName)).ToArray()

3. Assign → Datatable dt = dt.DefaultView.ToTable(false,arrayOfColumnsToBeSelected)

Regards,
Dom

[Snowman]

Dear @aksh1yadav, @Dominic,

Very glad to have the advice promptly !! Thank you very much

[TasneemA (Tasneem Abdus-Shakur)]

@Dominic

string Array arrayOfColumnsToBeSelected = (From col in dt.Columns.Cast(of System.Data.DataColumn) where not(arrayOfColumnsToBeRemoved.Contains(col.ColumnName)) Select CStr(col.ColumnName)).ToArray()

Would you mind explaining this a little bit more? I am trying to delete some columns from an Excel file. Is this is 3 different “Assign” activities?

Thank you in advance!

[JoVansant (JoV)]

don’t use a filter rows.
you can chose to keep or remove; and if it goes to a new datatable or just to overwrite the previous 1.

really shiny activity.
only works on name however if you need to do it via index number this won’t do

edit: srr didn’t see the date of the topic