Import-Csv on file with `"#"` in the header

Question

I'm importing a CSV generated by the chess cli tool Ordo. It has this output format:

"#","PLAYER","RATING","ERROR","POINTS","PLAYED","(%)"
1,"New",2500.0,"-",841.50,1683,50.00
2,"Old",2500.0,"-",841.50,1683,50.00

I'm trying to import it in PowerShell 7 with this command:

$csv = Import-Csv -Path ./ratings.csv
$csv


1      : 2
New    : Old
2500.0 : 2500.0
-      : -
841.50 : 841.50
1683   : 1683
50.00  : 50.00

It seems to have done something really strange with the parsing. Instead of mapping the #, PLAYER, etc. columns as properties on the PSCustomObject, it's skipped the header row and interpreted the first data row as a header, then mapped the second row's values to those headers.

PowerShell 5.1 behavior

Curiously, this works fine on PowerShell 5.1:

$csv = Import-Csv C:\code\Forklift\matches\ratings.csv
$csv


#      : 1
PLAYER : New
RATING : 2500.0
ERROR  : -
POINTS : 841.50
PLAYED : 1683
(%)    : 50.00

#      : 2
PLAYER : Old
RATING : 2500.0
ERROR  : -
POINTS : 841.50
PLAYED : 1683
(%)    : 50.00

What I think is happening

Reading the documentation for Import-Csv for PowerShell 7, I find this line:

Import-Csv also supports the W3C Extended Log format. Lines starting with the hash character (#) are treated as comments and ignored unless the comment starts with #Fields: and contains delimited list of column names. In that case, the cmdlet uses those column names. This is the standard format for Windows IIS and other web server logs. For more information, see Extended Log File Format.

That same section about comments is missing from the documentation for Import-Csv for PowerShell 5.1.

I suspect that the first field "#" is being interpreted as a W3C Extended Log format comment and destroying the header.

What's the solution here to correctly use Import-Csv when one of the header values is "#"?

Logged this as a bug in the PowerShell repo: github.com/PowerShell/PowerShell/issues/26419 — Kyle Pollard
– Kyle Pollard ♦, Commented Nov 11 at 3:44
You will probably find that will be rejected. CSV does not really have a standard, it has does have an RFC 4180 that lays out a template for what a CSV is. Around that an assortment of conventions have grown up. One is that # denotes a comment line, see Handle comments. So this is not a PowerShell issue per se. In other words you will see this crop up elsewhere. Your best bet is not start a line with #. — Adrian Klaver
– Adrian Klaver, Commented Nov 11 at 17:50
@AdrianKlaver I'm not starting the line with #, the line starts with ". This is a bug in PowerShell, it first parses the header into a list of values ("#","PLAYER" becomes @('#', 'PLAYER')) and then checks if the first parsed value is #. This is different from what the PowerShell docs say, which is that "Lines starting with the hash character (#) are treated as comments and ignored". Note the difference between checking the first parsed header value and the first character of the raw line. — Kyle Pollard
– Kyle Pollard ♦, Commented Nov 11 at 20:12
I see what you are getting at. I still think it will not get anywhere. CSV is a text file and # vs "#" is really not a difference, you are starting a line with a text # in either case. Good luck though on your attempt. — Adrian Klaver
– Adrian Klaver, Commented Nov 11 at 22:38

Kyle Pollard · Accepted Answer · 2025-11-11 03:09:52Z

0

My quick and dirty solution is to just replace the "#" with "NUMBER" before converting it from a csv:

$file = (Get-Content ./matches/ratings.csv) -replace '^"#"', '"NUMBER"'
$csv = Convert-FromCsv $file
$csv

NUMBER : 1
PLAYER : New
RATING : 2500.0
ERROR  : -
POINTS : 841.50
PLAYED : 1683
(%)    : 50.00

NUMBER : 2
PLAYER : Old
RATING : 2500.0
ERROR  : -
POINTS : 841.50
PLAYED : 1683
(%)    : 50.00

...but ideally, there's a better solution built into PowerShell at some point in the future, or this bug is just fixed.

answered Nov 11 at 3:09

Kyle Pollard♦

2,3622 gold badges17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import-Csv on file with `"#"` in the header

PowerShell 5.1 behavior

What I think is happening

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

PowerShell 5.1 behavior

What I think is happening

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related