How to delete multiple columns of an excel

Help
, ,
1

Hi

I have an excel having 88 columns. Need to delete some columns from there if that specific column doesn’t belong to an array of strings (header values).

Q1. By knowing the column name (that it doesn’t belong to the array) how can we find the cell value of that header ?

Or please let me know what is the other way of deleting the columns while traversing through the header values ?

2 Likes
2

Hi
Did we try with REMOVE DATACOLUMN activity where we can pass the column name as input
like pass the array variable with column name as input to a FOR EACH activity and change the type argument as string
–inside the loop use a REMOVE DATACOLUMN activity and mention the input as item.ToString in column name property and mention the datatable name in datatable property

Cheers @kkpatel

3

Hi @Palaniyappan

I am not using data table. Want to do directly on excel. So if i use delete column activity of excel i have to pass column no. which i can get by index of header array but once a specific column is deleted it will shift and the counting will change and won’t be in sync with the position no of the array. So it will delete the wrong columns next time.

Note: The excel file contains more than 3.5 lakhs of rows. So reading and writing from data table will be good ??

5

Hi @kkpatel,

Here is an activity to delete the contiguous columns.

Regards
Balamurugan.S

6

Hi , @kkpatel

taking into consideration the number of columns and rows, i would try this method.

I would read range (with the excel application , not the system - it’s faster on big files or with lots of columns) , and create a datatable.

After that i would use the “Filter DataTable” and use the “Keep” option to identify the columns i want and would save it as a second datatable.

Finally i would use the write range to write the new datatable. In the end i would delete the original file (if not needed).

See if this works for you

1 Like
7

Hi @lfcosta

This would be very good i guess. But i have a question. For large excel file like having 350000 records, will it be a good way to use data table or there is any better way to do this ?

8

@Palaniyappan The above question is for you too. :grinning:

1 Like
9

Hmm one more simple option is just a ASSIGN ACTIVITY like this
—once after getting the datatable from READ RANGE activity we can keep the columns we want with this expression

Yourdatatable = Yourdatatable.DefaultView.ToTable(False,”columnname1”,”columnname2”,…,”columnname n”)

This will give us only the column we want

And for this

Of course Datatable handle such numbers easily

Cheers @kkpatel

1 Like
10

Hello @Palaniyappan , yes your option is actually good, and i use it as well. Altough when i began i found it more easy to use the “FilterActivity” and only later on did i move to your solution.

@kkpatel, i only had problems with files with large columns without the excel application scope (it would be excruciatingly slow), using it i never had any problems (other than those inerent to the Excel handling big files , :))

11

@lfcosta @Palaniyappan As my file contains more than 300000 records its not even executing the read range activity. The execution is absolutely stopped also not giving any error. I am not getting any clue what is happening in the background.

What to do here ?

12

Hi
Is the read range from excel package
@kkpatel

13

Yes.

14

Hmm actually excel package can handle that number
Did we try with read range from workbook activities
@kkpatel

15

How to debug ? What is happening ?

16

No I haven’t tried that yet?

17

@Palaniyappan I tried through the read range under workbook. There i provided the full path, my file is csv not xlsx. So it gave me exception that .csv is not supported.

But in excel application scope it was not throwing any error for the .csv file.

1 Like
18

Oh if it’s a csv we can use READ CSV FILE activity and that would even give us a datatable as output
@kkpatel

19

@Palaniyappan Thank you. I admit that i really forgot to use the read csv activity as mostly we go for excel.

But also now i am able to read the data after un-checking the Preserve Format property.

1 Like
20

Great
So were we able to proceed further
@kkpatel

21

Yes @Palaniyappan. Thank you so much.

1 Like