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

1. Read/open the csv file
2. Scan the first column for 'Total'
3. Scan the first row for 'Total'
4. Read that 'Total' row until the 'Total' column
5. Record column indices containing zero
6. Cut those columns
7. 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:

Desired:

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;

CodePudding user response：

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