How to remove multiple columns at a time

Help Automation Ops
,
1

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

2

Hi @anjani_priya

check the below thread

Regards

3

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!!

4

Hi @anjani_priya

You can simply use Filter DataTable activity

5

@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

6

what is the datatype of dtoutput

7

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

8

Hi @anjani_priya

Use: Filter Data Table Activity
image

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

image
image748Ă—473 15.9 KB

OR
Use Assign Activity

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

Hope it will helps you :slight_smile:
Cheers!!

9

there are 167 columns so I want to remove specific columns

10

filter datatable takes much time to run because data is huge

11

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

12

@anjani_priya

How about the following?

image
image900Ă—1031 36.4 KB

BlankProcess18.zip (496.5 KB)

Cheers!!

13

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:

image
image840Ă—585 74.6 KB

Output:
Book 1.xlsx (682.5 KB)

Sheet1 is Input and Sheet3 is output.

Regards

1 Like
14

the columns should be removed in same sheet

15

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

16

@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
17

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

18

@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

19

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

20

@anjani_priya

Then just use filter datatable…

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

Cheers