Why is Powershell Import-Csv not working properly on this CSV file?

Question

I am importing this CSV file into powershell but it is not importing the headers and also not all the columns. It is only getting the first column but thinks there are no headers. I realize the second line is empty but that is how the software generates the CSV file with the data I need to work with.

CSV File:

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

Powershell Code:

$computerscsv = import-csv -Path "C:\C Drives Less than 10GB free.csv" #import the csv file
$computerscsv | Format-Table
pause

Powershell Output:

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

Any help would be greatly appreciated.

Bacon Bits · Accepted Answer · 2019-06-19 21:17:17Z

8

Edit: This issue has been reported on the PowerShell GitHub repository.

It appears to be a bug in the command. The header in the CSV file can't begin with an #. Note that converting the first header to "#" instead of # also doesn't fix the issue.

As a workaround:

$data = Get-Content "C:\C Drives Less than 10GB free.csv"
$data[0] = 'Num' + $data[0]
$data | ConvertFrom-Csv

Or, for a more general solution:

$data = Get-Content "C:\C Drives Less than 10GB free.csv"
if ($data[0][0] -eq '#') {
    $data[0] = 'Num' + $data[0]
}
elseif ($data[0].Substring(0,2) -eq '"#') {
    $data[0] = '"Num' + $data[0].Substring(1)
}
$data | ConvertFrom-Csv

My guess is that Import-Csv and ConvertFrom-Csv are mistaking the first line beginning with a # as the start of the (now somewhat deprecated) type information line:

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

edited Jun 19, 2019 at 21:17

answered Jun 19, 2019 at 19:03

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

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

4 Comments

Matt Over a year ago

ah... you are probably right about the information line. that would make sense. I am so used to trying to remove that and I forgot about it.

Bacon Bits Over a year ago

@LotPings I reported the linked issue using this question for the examples.

user6811411 Over a year ago

The conditional replacement of the # might be easier with a single RegEx containing a negative lookahead and a backreference of the optional leading ". => $data[0] = $data[0] -replace '^("?)#(?!TYPE)','$1Num#'

Todd Welch Over a year ago

Yes it was the #, I was able to remove that from the output into the CSV file and the program ran just fine then. Thanks for the help!

Matt · Accepted Answer · 2019-06-19 18:56:09Z

I do not know the exact reasoning but it looks like the pound/hash is making Import-CSV ignore the first line. In essence your file is using those commas as the header, which is also wrong. You can simulate this by removing the first line in your file. You will get the same result. The symbol itself is not the issue but the fact that it's the first character in the header.

Changing that character is enough to make it work.

$file = Get-Content "C:\C Drives Less than 10GB free.csv"
$file[0] = $file[0].Replace('#',"Num")
$file | ConvertFrom-Csv

"Num" might not suffice for you but you need to make it something else. Looking to see if there is a better way but that should do it.

Optionally pipe to | Select-Object -Skip 1 to deal with that empty row or maybe safer would be to flush out entries with no # via | Where-Object{$_.Num} in case there is more of those or it is not guaranteed that first line is blank.

Blake Drumm · Accepted Answer · 2021-12-22 00:04:45Z

0

For anyone looking for a fix, this worked for me:

((Import-CSV .\VMM_GlobalSetting.csv).Where{$_.PropertyName -eq 'QFEDatabaseVersion'}).PropertyValue

I was getting this error before applying the above:

 Select-Object : Property "QFEDatabaseVersion" cannot be found.
At C:\Users\blakedrumm\Desktop\VMMDataCollector\DataCollector\Functions\General-Info.ps1:789 char:81
+ ... ing.csv") | Select-Object QFEDatabaseVersion -ExpandProperty QFEDatab ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (@{PropertyName=...opertyValue=20}:PSObject) [Select-Object], PSArgumen
   tException
    + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand

edited Dec 22, 2021 at 0:04

answered Dec 15, 2021 at 0:18

Blake Drumm

1851 silver badge7 bronze badges

Collectives™ on Stack Overflow

Why is Powershell Import-Csv not working properly on this CSV file?

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related