5

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

Improve this question
1
  • Csv's don't have comments as part of the format. Commented Jun 17, 2024 at 13:44

5 Answers 5

Reset to default
8

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>
}
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

No need for Get-Content. Simply do Select-String '^[^#]' 'C:\path\to\the.csv' | ...
The problem with that is that it adds the filename and line# info to the first property, so you get a property named <path>:6:Header1 and data like <path>:<line #>:<value of first column>. I don't see a way to suppress that info if you give Select-String a file argument rather than pipeline input (piping through select Line gives you string output, so ConvertFrom-Csv comes out null).
... | % { $_.Line } | ...
Oh, right! I confused myself with this one. I actually tried piping through %{$_.Line} but didn't bother trying to send that to ConvertTo-Csv because it produces string data. But my comment above is backwards - you want comma-separated string input for ConvertFrom-Csv, and | select Line doesn't work because it doesn't output strings... duh! In any case, you lose the simplicity if you have to do that - easier to type gc than %{$_.Line}.
I'd probably just to (Get-Content foo) -notlike '#*' | ConvertFrom-Csv. I never really saw the point in Select-String, somehow all the alternatives are easier to read and faster to write.
5

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
Improve this answer

Comments

3

If you feed convertfrom-csv csv data as an array of lines it seems to automatically filter out comments. I frequently use convertfrom-csv this way but I haven't seen it documented.

cat  data.csv | convertfrom-csv      #skips commented lines automagically


("co1,col2,col3",  "abc,def,ghi", "#this,is,a,comment", "abc1,def1,ghi1")|convertfrom-csv


co1  col2 col3
---  ---- ----
abc  def  ghi 
abc1 def1 ghi1

However, the following will not skip comments:

"co1,col2,col3
abc,def,ghi
#this,is,a,comment
abc1,def1,ghi1
"|convertfrom-csv


co1   col2 col3
---   ---- ----
abc   def  ghi 
#this is   a   
abc1  def1 ghi1
Improve this answer

1 Comment

Import-Csv ignores "#" comments too.
1

Where-object will work after import-csv as well. You just have to reference the first column from csv in the clause. e.g.:

$EscapeCharacter = '#'
$FilteredData = Import-Csv -Path "$($Home)\Documents\sample.csv" -Delimiter "`t" -Encoding UTF8 | Where-Object {$_.coll1 -notlike "$EscapeCharacter*"}

The sample of tab delimited csv:

coll1   coll2
#Kotehulky  SomeValue
Cakovice    OtherValue
Improve this answer

Comments

0

I know this is an older question but i had the Problem today and found this article. I solved the "problem i a different way:

$CSV = import-csv | where-object {$_.FirstField -notlike "#*"}

This way alle Lines that start with a # will be ignored.

bye, christian

Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.