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!
CodePudding user response:
So, you have 3 problems you need to solve:
- Import the data from the CSV file
- Filter it based on the value of column
C
- 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.
CodePudding user response:
$Data = Get-Content "C:\file.csv" | ConvertFrom-Csv $Data | Where-Object {$_.C-ne 'Unknown'} | Export-Csv "C:\file_New.csv"