Trouble with csv files

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: ,

One Response to Trouble with csv files

Leave a Reply