Use PowerShell to see if a column is empty and the delete the entire row from csv file

Question

I have a csv file, with no headlines, that looks like this:

"88212526";"Starter";"PowerMax";"4543";"5713852369748";"146,79";"EUR";"6"
"88212527";"Starter";"PowerMax";"4543";"5713852369755";"66,88";"EUR";"20"
"88212530";"Starter";"PowerMax";"4543";"5713852369786";"143,27";"EUR";"0"
"88212532";"Starter";"PowerMax";"4543";"5713852369809";"80,98";"EUR";"6"
"88212536";"Starter";"PowerMax";"4543";"5713852369847";"";"EUR";"0"
"88212542";"Starter";"PowerMax";"4543";"5713852369908";"77,16";"EUR";"9"
"88212543";"Starter";"PowerMax";"4543";"5713852369915";"77,46";"EUR";"52"

I need a script in PowerShell that deletes the entire row if column 6 is empty. I have tried this

Foreach ($line in Get-Content .\POWERMAX_DK_1.csv) {
    $linearray = $line.split(";")
    if($linearray[6] -ne "") {
        Add-Content .\myTempFile.csv $line
    }
}

But it don't work. The line with empty column is not removed.

Please help /Kim

Answer 1

If you need check column 6, you have to use $linearray[5], because arrays starts counting on zero ($linearray[0] should be the first element)

Answer 2

Your immediate problem is twofold:

• As Mauro Takeda's answer points out, to access the 6th element, you must use index 5, given that array indices are 0-based.

• Since you're reading your CSV file as plain text, the field you're looking for has verbatim content "", i.e. including the double quotes, so you'd have to use -ne '""' instead of -ne "" ($linearray[5])

However, it's worth changing your approach:

• Use Import-Csv to import your CSV file, which in your case requires manually supplying headers (column names) with the -Header parameter.

  • This outputs objects whose properties are named for the columns, and whose property values have the syntactic " delimiters removed.

  • These properties can then be used to robustly filter the input with the Where-Object cmdlet.

  • In order to convert the results back to a CSV file, use a - single -call to Export-Csv, as shown below (see next point).

• Using Add-Content in a loop body is ill-advised for performance reasons, because the file has to be opened and closed in every iteration; instead, pipe to a single call of a file-writing cmdlet - see this answer for background information.

Therefore:

# Note: The assumption is that there are 8 columns, as shown in the sample data.
#       Adjust as needed.
Import-Csv .\POWERMAX_DK_1.csv -Delimiter ';' -Header (1..8) |
  Where-Object 6 -ne '' |
  Export-Csv -NoTypeInformation \myTempFile.csv

Character-encoding caveat: In Windows PowerShell, Export-Csv uses ASCII(!) by default; PowerShell (Core) 7+ commendably uses BOM-less UTF-8. Use the -Encoding parameter as needed.