topic Re: how To Delete empty columns in collection? in Product Forum

how To Delete empty columns in collection?

Haribp — Tue, 29 Mar 2022 07:35:00 GMT

I have 500 rows and 150 columns in excel sheet which I am copying into collection (using Get worksheet as collection). Out of 150 columns, around 80 are empty. How should I delete those empty columns? If I use 'delete column' action, it will ask for column name but I want delete all null columns in one go. Please suggest.

------------------------------
Hari bp
------------------------------

RE: how To Delete empty columns in collection?

PvD_SE — Tue, 29 Mar 2022 10:40:00 GMT

Hi H,

I would suggest looking into the OLEDB actions as delivered by BP. It allows you to extract data from an XL and put it into a collection. OLEDB runs a regular SQL statement to define what data needs to be extracted, this allows you to omit empty rows or select rows based on cells with a certain value.

Alternatively, you just read the XL into a collection unsing the regular XL into Collection action, and then run the collection-action to remove empty rows. The regular action to get XL data into a collections has its limitations to the volume of data. But according to a recent post by Eric, a new/extended VBO is available that does handle larger volumes without OutOfMemory errors.

------------------------------
Happy coding!
Paul
Sweden
------------------------------

RE: how To Delete empty columns in collection?

RaviKumar3 — Tue, 29 Mar 2022 13:23:00 GMT

Try with this-

Open Excel VBO and just for your comfort duplicate 'Remove Blank Rows' action, and rename the action into 'Remove Blank columns' and then open the code stage of the action and replace "Row" with "Column" and "Rows" with "Columns"

Dim worksheet As Object
worksheet = GetWorkbook(handle,Nothing).ActiveSheet

Const xlCellTypeLastCell As Integer = 11
Dim FirstColumn As Integer = 1
Dim LastColumn As Integer = worksheet.Cells.SpecialCells(xlCellTypeLastCell).Column

For i As Integer = LastColumn To FirstColumn Step -1
    If worksheet.Application.CountA(worksheet.Columns(i)) = 0 Then
        worksheet.Columns(i).Delete
    End If
Next

------------------------------
Ravi Kumar
Sr Automation Designer
Ericsson
Asia/Kolkata
------------------------------

Re: how To Delete empty columns in collection?

whitehouse-UoL — Wed, 10 Jul 2024 12:20:52 GMT

I am guessing this issue is solved judging by the age of the post but I solved a similar issue with this Utility.