Delete Columns in Excel

[nikhil_palli (nikhil)]

In my workflow, I want to delete empty columns in Excel file and overwrite the original file or create a new one. Please help with the steps to delete empty columns in excel.

[V_Roboto_V]

Hi @nikhil_palli

Welcome to the Community

Here is how you can delete empty columns from an Excel.

The Sample Excel Data:

The Simple Workflow:

Explanation:
Step 1: Use a Read Range activity to read the excel file. (explaining this, assuming you are also new to UiPath)

Step 2: Use the Invoke Code activity. Under the Edit Code, use the following code:

Dim columnsToRemove As New List(Of System.Data.DataColumn)

For Each col As System.Data.DataColumn In dt.Columns
    If dt.AsEnumerable().All(Function(row) IsDBNull(row(col)) OrElse String.IsNullOrWhiteSpace(row(col).ToString())) Then
        columnsToRemove.Add(col)
    End If
Next

For Each col As System.Data.DataColumn In columnsToRemove
    dt.Columns.Remove(col)
Next

Under the Edit Argument, provide the argument:

Step 3: Use Write Range to Update the existing Excel, but in a different Sheet, or write into a different file. Do make sure that Add Headers is Checked under properties.

The Output:

Note: If you write into same Sheet, then it will override old data and you will end up with this:

The reason being: the new data has less columns, hence the extra columns from old data will still remain. The workaround would be to use Excel Activities: Clear Range Activity and then Write Range.

I hope this solves your issue, Do mark it as a solution.
If you have further queries, Do ask.
Happy Automations

[sonaliaggarwal47 (Sonali Aggarwal)]

Hi @nikhil_palli,

You can do below steps:

1. use read range activity to read data from excel file.
   2)then use filter data table activity on the datatable(output from step 1) to remove the columns that has no data.
2. use write range activity to write the filtered datatable(output from step 2) into a new excel file or new sheet in the same excel file.

Hope this helps.

Regards
Sonali

[sharazkm32 (Sharaz K M)]

Easiest solution would be to follow below steps. Sharing sample project folder as well which is tested and working fine

1. Open Excel File
2. Read complete sheet into a data table
3. Identify blank columns in the data table using Linq query expression

dtSample.Columns.Cast(Of DataColumn)().Where(Function(col) dtSample.AsEnumerable().All(Function(row) String.IsNullOrEmpty(row(col).ToString()))).ToList()

4. Loop through blank columns and delete one after other

ForumProject.zip (314.8 KB)

[nikhil_palli (nikhil)]

Thanks for the solution. what is value to save in assign step ?

[sharazkm32 (Sharaz K M)]

You can check my sample solution and match it with the same data type. It is list of data Columns

I would suggest to open my project and copy the same data type.

[sharazkm32 (Sharaz K M)]

You can search with above details while setting the data type

System.Collections.generic.list

Hope it helps