Delete column using index number in excel file

Help Studio
1

We have duplicate columns with same name and we would like to delete one of the duplicate column

Only " Column name " option is there in delete column activity… How to delete the one of the duplicate column using index number?

image

2

HI @Sathish_Kumar_S

You can try with the Classic Activities

Insert/Delete Column You can use this activity inside the use excel as well

image
image1268×265 19.4 KB

Give your index number in the position field and change the mode as Remove

If it removes different column use the indexnumber+1 in the position.

If you dont find the activity you need to enable the classic or show classic in the in the filters

Activites panel → Filters → Show Classic or Classic

Regards
Sudharsan

1 Like
3

Thank you… How to delete only Empty columns? and delete multiple columns in single delete column activity

4

I assume that you have extra columns like Column1 Column 2 in excel? @Sathish_Kumar_S

5

multiple columns without any data and header

image
image870×198 13.6 KB

6

Hi @Sathish_Kumar_S,

Please try the below code and let me know if it works. add it in an assign and assign it to an variable of type object. and then it will give a array of column names with null values you can then use it in a for loop and remove all the columns that are present in the array.

(From col In dt.Columns.Cast( Of System.Data.DataColumn) Where dt.AsEnumerable().All(Function(x) String.IsNullOrEmpty(x(col.ColumnName).ToString) or String.IsNullOrWhiteSpace(x(col.ColumnName).ToString)) Select col.ColumnName).ToArray()

Regards.

7

Hey @Sathish_Kumar_S ,

Using the read range activity load the datatable to a variable of type datatable and then take one more assign activity and use the below linq, this will return only the column will be having at least one value in a row.

dt.Columns.Cast(Of DataColumn)().Where(Function(col) Not dt.AsEnumerable().All(Function(row) row.IsNull(col))).CopyToDataTable()

Thanks,
Sanjit

8

Thank you … What is the variable type of assign activity to use above code?

Getting below error

image
image1318×565 35.8 KB

9

Hi @Sathish_Kumar_S ,

You can follow the another approach mentioned in this xaml.
RemoveEmptyColumn.xaml (14.8 KB)

Thanks,
Sanjit

10

Thank you… it is removed the empty columns

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.