Remove empty columns

Help Studio
, , , ,
1

Hey guys,

I need to delete every emtpy column in my datatable. The columns do not have any headers. I saw other forum solutions on this topic, but none of them helped me really out. The empty columns aren’t alwasy at the same spot. The RPA bot should delete every empty column in the range: A:AJ. Furthermore I found out that the write range activity doesnt work on that as well (after filtering). It destroys the formats.
Any ideas on that?

Thanks in advance!
Regards
Marco

2

@marco.roensch,

Any sample input output to understand what should be the empty column here?

Thanks,
Ashok :slight_smile:

3

@marco.roensch
Sequence
Excel Application Scope
Path: “YourExcelFilePath.xlsx”
Body:
Read Range
Range: “A1:AJ”
DataTable: dt
AddHeaders: False

        Assign
            To: emptyColumns
            Value: (From col In Enumerable.Range(0, dt.Columns.Count)
                    Where dt.AsEnumerable.All(Function(row) row.IsNull(col) OrElse String.IsNullOrEmpty(row(col).ToString()))
                    Select col).ToList()
        
        For Each
            Item: colIndex
            Argument Type: Int32
            Values: emptyColumns.OrderByDescending(Function(x) x)
            Body:
                Assign
                    To: dt.Columns.RemoveAt(colIndex)
        
        Write Range
            Range: "A1"
            DataTable: dt
            AddHeaders: False

try this flow steps in studio

4

Sol1:

get the datatable by reading the excel. Afterwards, you can delete the empty lines with the filter activity and write them back to excel. you can use rowno instead of rowname to avoid headers issue.

sol2:

Use this query

YourDt=YourDt.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(“”))).CopyToDataTable()

Replace yourdt with actual name.

Happy Automation
Tukdi. :slight_smile:

5

Hey guys,

thanks for your solutions. Actually I found another way:

Read Range A1:AJ1
Assign: DeleteCounter = 0
For each: Column
If: String.IsNullOrEmpty(dt.Rows(0)(Column).ToString)
Then: Assign: ColumnIndex = dt.IndexOf(Column)
Delete Columns: Position: ColumnIndex+1-DeleteCounter
Assign: DeleteCounter = DeleteCounter+1

As you probably all know, the +1 addition on the ColumnIndex is because the read range activity starts with Index 0, while the Delete column activity doesn’t

Regards
Marco