There are many situations where we want to read a csv file as part of our admin script – usually as a way to input data to the script for some kind of bulk processing. This makes the following a fairly common task
PS> import-csv -Path c:\test\csvtests\file1.csv | ft -a
col1 col2 col3
---- ---- ----
A 1 B
B 2 C
C 3 D
D 4 E
E 5 F
Normally we would expect a comma as the delimiter but we can use other characters e.g. “|” the pipeline symbol
PS> import-csv -Path c:\test\csvtests\file3.csv -Delimiter "|" | ft -a
col1 col2 col3
---- ---- ----
A 1 B
B 2 C
C 3 D
D 4 E
E 5 F
Sometimes we don’t get quite what we expect
PS> import-csv -Path c:\test\csvtests\file2.csv | ft -a
Import-Csv : Cannot process argument because the value of argument "name" is invalid. Change the value of the "name" argument and run the operation again.
At line:1 char:11
+ import-csv <<<< -Path c:\test\csvtests\file2.csv | ft -a
+ CategoryInfo : InvalidArgument: (:) [Import-Csv], PSArgumentException
+ FullyQualifiedErrorId : Argument,Microsoft.PowerShell.Commands.ImportCsvCommand
Not the most informative of error messages and one that leads to a lot of confusion because the name and path of the file is obviously correct.
One reason for this happening is that the header is wrong. There is a trailing blank and\or a delimiter at the end of the header row
“col1|col2|col3| “ - its only in quotes so the blank is obvious
We can use get-content to check this
PS> get-content c:\test\csvtests\file2.csv | select -first 2
col1|col2|col3|
A|1|B
Remove the blank and\or delimiter and import-csv works again.
PS> import-csv -Path c:\test\csvtests\file2.csv -Delimiter "|" | ft -a
col1 col2 col3
---- ---- ----
A 1 B
B 2 C
C 3 D
D 4 E
E 5 F
These extra delimiter can appear if we use Excel to create the csv file and inadvertently bring in too many columns.
CSV files are an important data source – this problem shouldn’t stop us using them
Technorati Tags:
PowerShell,
csv file