PowerShell DataTable removing empty columns logic incorrect

Question

This is the first time that I'm writing a function that can call itself. I'm trying to remove all the columns of a DataTable where the rows are empty.

The code works fine but spits out some errors. For one reason or another it's looping one last time through the for loop with the old $Columns number still in memory. Although I'm filling it again each time the function is called... I don't understand why..

The code:

 Function Remove-EmptyColumns {
    $Columns = $DataTable.Columns.Count
    $Rows    = $DataTable.Rows.Count

    for ($c = 0; $c -lt $Columns; $c++) {    
        $Empty = 0       
        for ($r = 0; $r -lt $Rows; $r++) {
            if ($DataTable.Rows[$r].Item($c).ToString() -eq '') {
                $Empty++
            }
        }
        if ($Empty -eq $Rows) {
            $DataTable.Columns.Remove($DataTable.Columns[$c])
            Remove-EmptyColumns
        }
    }
}

Thank you for your help.

dustinmoris · Accepted Answer · 2015-03-11 12:25:48Z

2

Why is it a recursive method? Try removing the Remove-EmptyColumns and it should work, no?

EDIT: Try

Function Remove-EmptyColumns {
    $Columns = $DataTable.Columns.Count
    $Rows    = $DataTable.Rows.Count

    $columnsToRemove = @()

    for ($c = 0; $c -lt $Columns; $c++) {    
        $Empty = 0       
        for ($r = 0; $r -lt $Rows; $r++) {
            if ($DataTable.Rows[$r].Item($c).ToString() -eq '') {
                $Empty++
            }
        }
        if ($Empty -eq $Rows) {
            $columnsToRemove.Add($DataTable.Columns[$c])
        }
    }

    $columnsToRemove | ForEach-Object { 
            $DataTable.Columns.Remove($_) }
}

answered Mar 11, 2015 at 12:25

dustinmoris

3,3813 gold badges29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DarkLite1 Over a year ago

I thought that to but it doesn't work. Once a column is removed the ID number for the other columns changes..

DarkLite1 Over a year ago

Your edited proposal works fine, thank you. I had to change it to $columnsToRemove += $DataTable.Columns[$c], as it's a normal Array by default and not an ArrayList. This workaround is great! Thank you, should've thought about it myself ... :P

MKANET Over a year ago

@DarkLite1 thanks for your comment. Without the suggestion in your comment, the latest EDIT'ed answer still wouldn't work for me.

GLaisne · Accepted Answer · 2015-03-11 12:42:58Z

2

Just a thought, but I like to use the String static method of IsNullOrEmpty.

if ([string]::IsNullOrEmpty($DataTable.Rows[$r].Item($c).ToString()))
{ ... }

This removes issues where the value may not equal '' but be $Null instead and it does it in a single line.

answered Mar 11, 2015 at 12:42

GLaisne

1753 silver badges13 bronze badges

1 Comment

DarkLite1 Over a year ago

Good thinking, I should consider using your proposal more often. I never do it actually because it looks so non-PowerShell-native, if that is a word :D Thank you for the heads-up though!

Collectives™ on Stack Overflow

PowerShell DataTable removing empty columns logic incorrect

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related