In Excel i saved a CSV file. I tried to import it with PowerShell using Import-Csv. It didn’t work in the beginning. Maybe this blog post is helpful for all having the same issue…

All files can be downloaded from here.

Strange Behaviour when using Save As “CSV” in Excel

2015-05-02 ExcelFile

I saved this file as CSV:
2015-05-02 Save As

I always though, that the file is now Comma separated. But in my case (all default settings) the file is saved Semicolons. The interesting part is, the the Import-Csv Commandlet tries to read the file and expects the values to be delimited by a Comma. In my case PowerSehll could read the csv file but didn’t detect the columns. You can see it in the next steps.

Import-Csv .\FunWithCsv.csv

2015-05-02 Windows PowerShell 1
Powershell did something but it didn’t work. I wanted to filter for the Language column to display only records with “1031”. So i typed

Import-Csv .\FunWithCsv.csv | Where-Object { $_.Language -eq 1031 }

Nothing happened…
2015-05-02 Windows PowerShell 2

So i had a look into the CSV File. I was astonished: The delimiter was set to “;” instead of “,”
2015-05-02 Windows PowerShell 3

Something went wrong with the delimiters. That’s why Powershell could not do anything with my command.

Delimters to the rescue

So i set the delimiter to “;”

Import-Csv .\FunWithCsv.csv -delimiter ";" | Where-Object { $_.Language -eq 1031 }

Way better:
2015-05-02 Windows PowerShell 4

Some useful commands

Display only the first three Items matching Language “1031”

Import-Csv .\FunWithCsv.csv -delimiter ";" | Where-Object { $_.Language -eq 1031 } | Select-Object -first 3

Display Rows matching Language “1031” and Title not empty

Import-Csv .\FunWithCsv.csv -delimiter ";" | Where-Object { $_.Language -eq 1031 -and $_.Title -ne "" }

Display Rows matching Language “1031” and Title not empty and Title not “Deutsch”

Import-Csv .\FunWithCsv.csv -delimiter ";" | Where-Object { $_.Language -eq 1031 -and $_.Title -ne "" -and $_.Title -ne "Deutsch" }

Do something with the filtered Data

After finding out how to filter data, the next step was to do something with the filtered data:

Import-Csv .\FunWithCsv.csv -delimiter ";" | Where-Object { $_.Language -eq 1031 -and $_.Title -ne "" -and $_.Title -ne "Deutsch" } | Foreach-Object { Write-Host -ForegroundColor Green $_.Title }

2015-05-02 Windows PowerShell 5