Trimming columns containing zero in powershell

Question

Help! I'm looking for a PowerShell solution that can do the following to trim columns from a csv file containing only zeros:

Read/open the csv file
Scan the first column for 'Total'
Scan the first row for 'Total'
Read that 'Total' row until the 'Total' column
Record column indices containing zero
Cut those columns
Export/overwrite to new csv file

The csv file contains grand total values on both axes. It also contains a variable number of columns, the first being the row labels and the last for the row totals. Zero columns could be any of the columns in between. I'm trying to avoid hard-coding to allow the script to handle a variable number of rows and columns.

X: any non-zero positive integer

Original:

	A	D	Total
row1	X	X	X
row2	X	X	X
row3	X	X	X
Total	X	X	X

Desired:

	A	D	Total
row1	X	X	X
row2	X	X	X
row3	X	X	X
Total	X	X	X

I originally scripted this in bash which reads the row containing totals (row 6), appends the column number to string b to cut. It's not an elegant solution and it's significantly slowing down my script.

declare -i first=0;
for i in {2..26}
do
    a=$(awk -F',' 'NR == 6 { print $'$i' }' $original)
    if [[ $a == '"0"' && $first -eq 0 ]]
    then
        b+="$i"
        first=1
    elif [[ $a == '"0"' && $first!=0 ]] 
    then 
        b+=",$i"
    fi
done
cut -d, -f$b --complement $original > $edited;

What have you written in Powershell so far? Questions generally get a better response when you ask about a specific error or issue with existing code… — mclayton, Jul 27 '22 at 18:05
$csv = Import-Csv results2.csv | Select-Object -Last 1 $data = $csv | % { $row = @{}; $_.psobject.Properties | % {$row[$_.name]= $_.value}; $row; } foreach ($Key in $($data.Keys)) { "The value of '$Key' is: $($data[$Key])" } — jw-smc, Jul 27 '22 at 21:04
I was initially trying to import the csv as a hashtable but ran into a bunch of issues. Sorry, I should provide additional details such as a test case in plain text format, not some hypothetical table. — jw-smc, Jul 27 '22 at 21:27

score 1 · Accepted Answer · answered Jul 27 '22 at 18:36

If I understand correctly you have a csv like this:

Label,A,B,C,D,Total
row1,X,0,0,X,X
row2,X,0,0,X,X
row3,X,0,0,X,X
Total,X,0,0,X,X

Then to create a new csv from it where all columns totalling 0 are removed you can do this:

$data = Import-Csv -Path 'X:\Somewhere\original.csv'

# get the row labeled Total
$totalRow = $data | Where-Object {$_.Label -eq 'Total'}
# find the columns in this row that have a value of 0
$columnsToDelete = ($totalRow.PsObject.Properties | Where-Object {$_.Value -eq 0}).Name
# Select all from the original data except the columns to delete
$result = $data | Select-Object * -ExcludeProperty $columnsToDelete

# output on screen
$result | Format-Table -AutoSize

# output to new CSV file
$result | Export-Csv -Path 'X:\Somewhere\Edited.csv' -NoTypeInformation

The result will look like:

Label A D Total
----- - - -----
row1  X X X    
row2  X X X    
row3  X X X    
Total X X X

Trimming columns containing zero in powershell

1 Answers1