CODEWITHSUNDEEP
powershellcsvimportimport-csv
Todd Welch
Todd Welch

Reputation: 1779

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

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.

Upvotes: 2

Views: 13822

Answers (3)

Blake Drumm
Blake Drumm

Reputation: 165

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

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32145

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"

Upvotes: 8

Matt
Matt

Reputation: 46690

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.

Upvotes: 3

Related Questions