Powershell remove a column from csv only if a word is present

Question

I have a csv with columns A, B, C. I would like to import that to powershell and only on column C, remove any rows that have the word "Unknown" listed. If Column A or B has "Unknown", they stay, however, if Column C has it, the entire row gets deleted. Per the picture below, Row 4 would be deleted. Can someone please provide a sample script to do this?

Thanks!

Answer 1

So, you have 3 problems you need to solve:

1. Import the data from the CSV file
2. Filter it based on the value of column C
3. Export the filtered data to a file again

To import, use the aptly named Import-Csv cmdlet:

$data = Import-Csv .\path\to\file.csv

Import-Csv will parse the CSV file and for each row it reads, it will output 1 object with properties corresponding to the column names in the header row.

To filter these objects based on the value of their C property, use the Where-Object cmdlet:

$filteredData = $data |Where-Object C -ne 'Unknown'

Where-Object will test whether the C property on each object does not have the value 'Unknown' (-ne = not equals), and discard any object for which that's not the case.

To re-export the filtered data, use the Export-Csv cmdlet:

$filteredData |Export-Csv .\path\to\output.csv -NoTypeInformation

---

You can also combine all three statements into a single pipeline expression:

Import-Csv .\path\to\file.csv |Where-Object C -ne 'Unknown' |Export-Csv .\path\to\output.csv -NoTypeInformation

This "one-liner" approach might be preferable if you're working on large CSV files (> hundreds of thousands of records), as it doesn't require reading the entire CSV file into memory at once.

Answer 2

$Data = Get-Content "C:\file.csv" | ConvertFrom-Csv $Data | Where-Object {$_.C-ne 'Unknown'} | Export-Csv "C:\file_New.csv"