i have excel data which contains 189 columns and i want to delete few columns say 45 columns from the excel sheet and write back that data to excel
is there any best way for doing this
or i have to use only filter data table activity and mention all the column name to remove from it.
Please provide me the best procedure
Store Columns to Delete in config file or string array.
Use Excel Process Scope and Iterate trough column array using For Each. Inside for each use Delete Column activity. Pass the current iterating column name and the same will be deleted from the excel file.
Other option would be Use Read range to DataTable. Do the modification and write it back to excel file.
First option is more excel native. Second option could be time consuming as we will have to take the data out from excel and write it back.
Thanks,
Ashok 
here the columns are like not in series number first i want to delete ABCD and then G to U and then W to CO and then DN to GG will this logic going to work
First option will require column names to delete. In this case go with second solution.
Thanks,
Ashok
iam bit confused which second solution you are referring to can u please elaborate it
It’s Use Read range to DataTable. Delete unwanted columns and write it back to excel file.
Thanks,
Ashok
ok i will take to take to data table. How the modification will happen …so i have to use filter data table activity to remove all the columns which are unwanted or any other way
At position, you should be passing the Column number. Instead of storing Column names in array, you can maintain column numbers and iterate this array of integer.
Just remember, when you delete a column, your another column to be deleted’s column number will also change. So make sure you are adding the correct column numbers to array.
Thanks,
Ashok
Financial Sample1.xlsx (81.5 KB)
Here by iam sharing excel doc how can we delete columns A B C I and M
can u make a sample for this
Here you go.
Sample Code:
DeleteColumns.xaml (10.4 KB)
Output File:
Financial Sample1.xlsx (69.6 KB)
Thanks,
Ashok
ok let me try thank you
You can simply use read range and filter data table and mention those columns which u want to remove their in an option of remove column
Hey, another approach to removing columns is LINQ. It’s just a step process. Here is the query.
dt_YourDTVariable.DefaultView.ToTable(True, "Column_Name1", "Column_Name2", "Column_Name3").AsEnumerable.CopyToDataTable
Write the column name in the expression you want to have in your datatable. For example, from a datatable I want to have only one column, then I will use this as:
dt_CoffeeShop.DefaultView.ToTable(False, "Item").AsEnumerable.CopyToDataTable
It will return datatable with only one column “Item”. Others will be removed.
here i want to write back the data to the same sheet where iam removing columns
So use the datatable variable that holds the filtered data and use it in the write range workbook.
this function is removing the duplicate data…i dont want to remove any data . i just want to delete the columns and paste that same data in the same sheet
iam attaching a sample sheet i want to remove column A C and H and write back the data on Sheet 1 , sheet 1 should have only data the columns i need
Financial Sample - Copy.xlsx (53.1 KB)
The function I wrote above returns the column that you mentioned in double quotes, all other columns will be deleted except those in double-quotes. So the columns you want to keep, write them in double quotes as I write “Item”.
