How to remove multiple columns at a time

how to remove multiple columns at a time without creating the duplicate columns and without leaving the space after removing the columns

Hi @anjani_priya

check the below thread

Regards

Hi @anjani_priya

You can use the LinQ Expressions to delete the multiple columns.

dt = dt.DefaultView.ToTable(False, dt.Columns.Cast(Of DataColumn)().Where(Function(c) c.ColumnName <> "Column2" AndAlso c.ColumnName <> "Column7" AndAlso c.ColumnName <> "Column12").Select(Function(c) c.ColumnName).ToArray())

Hope it helps!!

Hi @anjani_priya

You can simply use Filter DataTable activity

@anjani_priya

Assign activity:
dtOutput = (From col In dt.Columns.Cast(Of DataColumn)()
            Where Not {"Column1", "Column2", "Column3"}.Contains(col.ColumnName.Trim())
            Select dt.DefaultView.ToTable(False, col.ColumnName)).CopyToDataTable()

Hope it helps

what is the datatype of dtoutput

Hi @anjani_priya

Please check the below

Assign activity:

dt = dt.DefaultView.ToTable(False, "Column1", "Column3", "Column5")

Replace “Column1”, “Column3”, “Column5” with the actual column names you want to keep

Regards

Hi @anjani_priya

Use: Filter Data Table Activity
image

Configure Filter>Output Columns>Remove
And give Columns Name you want to remove


OR
Use Assign Activity

DT = DT.DefaultView.ToTable(False, “Column1”, “Column2”, “Column3”)

Hope it will helps you :slight_smile:
Cheers!!

there are 167 columns so I want to remove specific columns

filter datatable takes much time to run because data is huge

Hi @anjani_priya

You can use the below code:

dt= dt.DefaultView.ToTable(False, dt.Columns.Cast(Of DataColumn)().Where(Function(c) c.ColumnName <> "Tax" AndAlso c.ColumnName <> "So Created Date" AndAlso c.ColumnName <> "Sold to GST").Select(Function(c) c.ColumnName).ToArray())

Give the respective columns you can add columns also if needed.

dt= dt.DefaultView.ToTable(False, dt.Columns.Cast(Of DataColumn)().Where(Function(c) c.ColumnName <> "Tax" AndAlso c.ColumnName <> "So Created Date" AndAlso c.ColumnName <> "Sold to GST" AndAlso c.ColumnName <> "Ship To Country").Select(Function(c) c.ColumnName).ToArray())

Regards

@anjani_priya

How about the following?

BlankProcess18.zip (496.5 KB)

Cheers!!

Hi @anjani_priya

Check the below workflow:
Sequence27.xaml (8.0 KB)

Query:

dt= dt.DefaultView.ToTable(False, dt.Columns.Cast(Of System.Data.DataColumn)().Where(Function(c) c.ColumnName <> "Tax" AndAlso c.ColumnName <> "So Created Date" AndAlso c.ColumnName <> "Sold to GST").Select(Function(c) c.ColumnName).ToArray())

You can even add columns if you want.

Workflow:

Output:
Book 1.xlsx (682.5 KB)

Sheet1 is Input and Sheet3 is output.

Regards

the columns should be removed in same sheet

Hi @anjani_priya

You can use the delete file activity and delete the excel file and then write the output data table into the excel file again.

If you wanted to work on the same sheet using excel activities and macros code then please check the code provided by @lrtetala

Regards

@anjani_priya

Two ways

  1. Filter datatable…and in columns tab add the column names to remove or to retain
  2. Dt = dt.DefaultView.ToTable(False,"Column1","Column2") add more columns as needed

I have used second method but the first 4 columns deleted and next corresponding columns rewrote the first 4 columns

@anjani_priya

So you mean say you have 8 columns then you delete 4…then you still see 8 columsn? I dont think so…

Or is your requirement only to clear the values from the columns but not delete the column completely?

If yes…then use the below for each column so that valeus are cleared…

Use assign with

Dt.Columns("ColumnName").Expression = "''"

Cheers

I have 167 columns I have to remove 5 columns in it

@anjani_priya

Then just use filter datatable…

Then in columns tab…select remove and write the 5 column names you want to remove

Cheers