Using Import-CSV in Powershell, ignoring commented lines

Question

I think that I must be missing something obvious because I'm trying to use Import-CSV to import CSV files that have commented out lines (always beginning with a # as the first character) at the top of the file, so the file looks like this:

#[SpecialCSV],,,,,,,,,,,,,,,,,,,,
#Version,1.0.0,,,,,,,,,,,,,,,,,,,
#,,,,,,,,,,,,,,,,,,,,
#,,,,,,,,,,,,,,,,,,,,
#[Table],,,,,,,,,,,,,,,,,,,,
Header1,Header2,Header3,Header4,Header5,Header6,Header7,...
Data1,Data2,Data3,Data4,Data5,Data6,Data7,...

I'd like to ignore those first 5 lines, but still use Import-csv to get the rest of the information nicely in to Powershell.

Thanks

Answer 1

Simple - just use Select-String to exclude commented lines with a regex, and pipe to ConvertFrom-Csv:

Get-Content <path to CSV file> | Select-String '^[^#]' | ConvertFrom-Csv

The difference between Import-Csv and ConvertTo-Csv is that the former takes input from a file, and the latter takes pipeline input, otherwise they do the same thing - convert CSV data to an array of PSCustomObjects. So, by using ConvertFrom-Csv you can do this without modifying the CSV flie or using a temp file. You can assign the results to an array or pipe to a Foreach-Object block just as you'd do with Import-Csv:

$array = Get-Content <path to CSV file> | Select-String '^[^#]' | ConvertFrom-Csv

or

Get-Content <path to CSV file> | Select-String '^[^#]' | ConvertFrom-Csv | %{
  <whatever you want do with the data>
}

Answer 2

CSV has no notion of "comments" - it's just flat data. You'll need to use Get-Content and inspect each line. If a line starts with #, ignore it, otherwise process it.

If you're OK with using a temp file:

Get-content special.csv |where-object{!$_.StartsWith("#")}|add-content -path $(join-path -path $env:temp -childpath "special-filtered.csv");
$mydata = import-csv -path $(join-path -path $env:temp -childpath "special-filtered.csv");
remove-item -path $(join-path -path $env:temp -childpath "special-filtered.csv")
$mydata |format-table -autosize; #Just for illustration

Edit: Forgot about convertfrom-csv. It gets much simpler this way.

$mydata = Get-Content special.csv |
    Where-Object { !$_.StartsWith("#") } |
    ConvertFrom-Csv