Import-Csv and ConvertFrom-Csv fail to parse if the first header begins with a "#"

[AikenBM]

If the first column header in a csv file or from csv data begins with a # or with "#, then both ConvertFrom-Csv and Import-Csv do not parse the file or data correctly. If the second row of the input (i.e., the first row of data) is blank, then the commands fail to parse the data correctly.

While the problem is particularly highlighted by the row of empty data, I believe the actual problem is just that these commands assume that if the first line of input begins with # or "# that it must be a type information line.

By type information line, I'm referring to the first line of output of these commands:

PS C:\> Get-ChildItem C:\Windows\ | Select-Object Name, LastWriteTime -First 1 | ConvertTo-Csv -IncludeTypeInformation
#TYPE Selected.System.IO.DirectoryInfo
"Name","LastWriteTime"
"addins","9/15/2018 3:33:54 AM"

This issue was first identified in this StackOverflow question.

Steps to reproduce

Example 1:

$data = @'
#,Labels,Machine Name,System Description,IP,User Logged,Disk Free C
,,,,,,
6,"computers-PRO,Desktop,Office 2010,OS Windows 7,Update Adobe Reader",PRO-MEDASST,91-0-928,172.10.201.111,CHR\\jeniferc,6.3
7,Update Adobe Reader,RED,empty,172.10.201.5,,9.5
8,Update Adobe Reader,SIER,empty,172.10.201.5,,6.8
'@
$data | ConvertFrom-Csv

Example 2:

$data = @'
"#",Labels,Machine Name,System Description,IP,User Logged,Disk Free C
,,,,,,
6,"computers-PRO,Desktop,Office 2010,OS Windows 7,Update Adobe Reader",PRO-MEDASST,91-0-928,172.10.201.111,CHR\\jeniferc,6.3
7,Update Adobe Reader,RED,empty,172.10.201.5,,9.5
8,Update Adobe Reader,SIER,empty,172.10.201.5,,6.8
'@
$data | ConvertFrom-Csv

This next example which doesn't have a row of empty data fields in the CSV also does not parse correctly, though it behaves differently. It uses the first row of data as the headers.

Example 3:

$data = @'
#,Labels,Machine Name,System Description,IP,User Logged,Disk Free C
6,"computers-PRO,Desktop,Office 2010,OS Windows 7,Update Adobe Reader",PRO-MEDASST,91-0-928,172.10.201.111,CHR\\jeniferc,6.3
7,Update Adobe Reader,RED,empty,172.10.201.5,,9.5
8,Update Adobe Reader,SIER,empty,172.10.201.5,,6.8
'@
$data | ConvertFrom-Csv

If the contents of data.csv are the same as the value of the $data above, then this also reproduces the error:

Import-Csv data.csv

Note that removing the line of blank values does not change the behavior.

Expected behavior

The commands should know that the first line is not a type information line, and should try to parse it as the header row for the CSV data.

#                  :
Labels             :
Machine Name       :
System Description :
IP                 :
User Logged        :
Disk Free C        :

#                  : 6
Labels             : computers-PRO,Desktop,Office 2010,OS Windows 7,Update Adobe Reader
Machine Name       : PRO-MEDASST
System Description : 91-0-928
IP                 : 172.10.201.111
User Logged        : CHR\\jeniferc
Disk Free C        : 6.3

#                  : 7
Labels             : Update Adobe Reader
Machine Name       : RED
System Description : empty
IP                 : 172.10.201.5
User Logged        :
Disk Free C        : 9.5

#                  : 8
Labels             : Update Adobe Reader
Machine Name       : SIER
System Description : empty
IP                 : 172.10.201.5
User Logged        :
Disk Free C        : 6.8

Actual behavior

Examples 1 & 2 do this:

WARNING: One or more headers were not specified. Default names starting with "H" have been used in place of any missing headers.

H1
--
6
7
8

Example 3 does this:

6                                                                  : 7
computers-PRO,Desktop,Office 2010,OS Windows 7,Update Adobe Reader : Update Adobe Reader
PRO-MEDASST                                                        : RED
91-0-928                                                           : empty
172.10.201.111                                                     : 172.10.201.5
CHR\\jeniferc                                                      :
6.3                                                                : 9.5

6                                                                  : 8
computers-PRO,Desktop,Office 2010,OS Windows 7,Update Adobe Reader : Update Adobe Reader
PRO-MEDASST                                                        : SIER
91-0-928                                                           : empty
172.10.201.111                                                     : 172.10.201.5
CHR\\jeniferc                                                      :
6.3                                                                : 6.8

Environment data

Name                           Value
----                           -----
PSVersion                      6.2.1
PSEdition                      Core
GitCommitId                    6.2.1
OS                             Microsoft Windows 10.0.17763
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Unfortunately, I do not currently have access to a system where I can install PowerShell 7.0 previews to test on.

[iSazonov]

@AikenBM It was a compromise read either raw or smart with W3C standard support. The standard uses '#' as special char at first position in header. Our conclusion was that following to the standard is better, users would need workarounds only for edge cases like you share.
You can see the code here:
https://github.com/PowerShell/PowerShell/blob/master/src/Microsoft.PowerShell.Commands.Utility/commands/utility/CsvCommands.cs#L1285-L1314

Unfortunately, I do not currently have access to a system where I can install PowerShell 7.0 previews to test on.

You can download zip file from the site and install 7.0 Preview side-by-side.

[AikenBM]

@AikenBM It was a compromise read either raw or smart with W3C standard support. The standard uses '#' as special char at first position in header. Our conclusion was that following to the standard is better, users would need workarounds only for edge cases like you share.

By that I presume you mean the Model for Tabular Data and Metadata on the Web and the Metadata Vocabulary for Tabular Data. The second is necessary, because that's the only document I can find that refers to # being the default character. The first one for general tabular data and to which the metadata recommendation refers to, says the default comment character is null, while the metadata recommendation seems to say that the value should be user-configurable with a default value of #. I'm just skimming, though, so I may be misreading things. I'd also point out that both documents repeatedly state that they are not attempting define any CSV standard (RFC4180 does that), but instead trying to describe how CSV data are actually used.

However, let's set aside entirely any argument over the W3C standard and RFC4180. The problem is that even if I accept that the # is in widespread use as a comment character, the commands are still not consistent with that rule.

First I state my understanding of the rule: In a CSV format file, the leading character of # indicates a comment row which must be ignored by the parser.

Let's take these two CSV sets of data:

$CommentLine1 = @'
#,2,3
A,B,C
4,5,6
'@

$CommentLine2 = @'
A,B,C
#,2,3
4,5,6
'@

The above two variables with CSV data should be parsed identically in any circumstance. The # indicates a header, so the parser should always ignore those lines. That's not the case.

PS C:\> # Comment Line 1
PS C:\> $CommentLine1 | ConvertFrom-Csv

A B C
- - -
4 5 6


PS C:\> # Comment Line 1 - Split
PS C:\> -split $CommentLine1 | ConvertFrom-Csv

A B C
- - -
4 5 6


PS C:\> # Comment Line 2
PS C:\> $CommentLine2 | ConvertFrom-Csv

A B C
- - -
# 2 3
4 5 6


PS C:\> # No Quotes Comment Line 2 - Split
PS C:\> -split $CommentLine2 | ConvertFrom-Csv

A B C
- - -
4 5 6

See how the third example with a comment on line 2 and no -split isn't consistent with the others?

Also note that it's this third example that indicates how Import-Csv will handle the data:

PS C:\> $CommentLine2 | Set-Content ~\test.csv
PS C:\> Import-Csv ~\test.csv

A B C
- - -
# 2 3
4 5 6

It's different again if you try it with quoted CSV data:

$QuotedCommentLine1 = @'
"#","2","3"
"A","B","C"
"4","5","6"
'@

$QuotedCommentLine2 = @'
"A","B","C"
"#","2","3"
"4","5","6"
'@

First, I think it's important to note that, by my understanding of the rule, neither of the above sets of CSV data have any comments. The first character of the line is not #. It's ". I am going to take some convincing to think that it's appropriate to treat a quoted escape character as the escape character. You'll note that when ConvertTo-Csv -IncludeTypeInformation is specified or Export-Csv -IncludeTypeInformation is specified, the type information line is not quoted.

Here's the output:

PS C:\> # Quoted Comment Line 1
PS C:\> $QuotedCommentLine1 | ConvertFrom-Csv

A B C
- - -
4 5 6


PS C:\> # Quoted Comment Line 1 - Split
PS C:\> -split $QuotedCommentLine1 | ConvertFrom-Csv

A B C
- - -
4 5 6


PS C:\> # Quoted Comment Line 2
PS C:\> $QuotedCommentLine2 | ConvertFrom-Csv

A B C
- - -
# 2 3
4 5 6


PS C:\> # Quoted Comment Line 2 - Split
PS C:\> -split $QuotedCommentLine2 | ConvertFrom-Csv

A B C
- - -
# 2 3
4 5 6

Here, the system behaves completely differently if the comment is on the first line instead of on the second line. This makes no sense at all. First of all, there are no comments in this CSV data. Second of all, even if they were comments, why is a comment on line 1 treated differently than a comment on line 2? Further, this behavior is totally inconsistent with the behavior of the non-quoted data! For reference, Import-Csv behaves identically here.

What I believe needs to happen is:

1. There needs to be consistency of operation in how these commands parse the data.
2. There should be a way to set or ignore the comment character, per the W3C recommendations.

Unfortunately, I do not currently have access to a system where I can install PowerShell 7.0 previews to test on.

You can download zip file from the site and install 7.0 Preview side-by-side.

You don't understand. I currently have access to two different systems: A Windows 10 Enterprise workstation and a Windows 10 Enterprise laptop both owned by my organization. I am allowed to install software on these systems, but I am prohibited from installing preview or beta software of any kind without approval. I would have to check, but I'm fairly certain I'm not allowed to install Hyper-V or other virtualization software to circumvent these policies. My personally owned workstations are currently 1,200 miles away and powered off, and they will be until the end of August.

When I say I have no access to a system I can test on, I mean I have no access to a system I can test on!

[iSazonov]

@AikenBM Thanks for your investigations! As I commented above it is an expected behavior for the edge case. No need complicate the parser (we want to keep it fastest) until we get an important business case (like Aparche, ISS, or nginx will start writing logs of this format).

[jangliss]

I'm surprised this was rejected as "By Design". The behavior has changed from previous versions (such as 5.1.18362.145), not documented anywhere that I've found, other than this bug report and Stack Overflow post, and a potential breaking change for many.

As @AikenBM suggested, a quoted "#" is not a line starting with #, so shouldn't be treated as a comment, and yet is being done so. I've got plenty of CSV files that use a "#" to represent a record number. This will mean changes required to read the file, either by loading the whole file and replacing the # with something else, or changing the export.

As a side note, the code appears to have explicit definition to handle the W3C Common log format, but only the "#Fields:" directive, when the guidance suggests there is also Version, Software, Start-Date, End-Date, Date, and Remark.

[iSazonov]

I've got plenty of CSV files that use a "#" to represent a record number.

What is source of the files?