How to make Powershell's Import-Csv correctly import blank spaces

user_1618897 0
Viewed 678

I have a CSV file where some of the cells have a blank space in them, and others are just empty. If I run

" " -eq ""

It returns False so Powershell itself understands the difference between a blank space and an empty string. But, I have a CSV file with the following content:

Header0,Header1,Header2,Header3
FillerData, ,,FillerData

When I import this and compare it I get the following:

PS C:\> $MyCSV = Import-Csv -Path "C:\MyCSV.csv"
PS C:\> $MyCSV.Header1 -eq " "
False

PS C:\> $MyCSV.Header1 -eq ""
True

PS C:\> $MyCSV.Header2 -eq " "
False

PS C:\> $MyCSV.Header2 -eq ""
True

So, Import-Csv defaults to treating CSV entries with a blank space as equal to an empty string, when they're not equal. I really need to import the blank spaces for my use case. Is there any way to make Import-Csv correctly import the blank spaces instead of treating them as empty strings?

csv powershell
1 Answers

If I were a bit better at regex this could be improved but here is working solution, so basically Import-Csv will remove empty spaces by default unless you specify you need a literal blank space. A way to do this is to add opening and closing " on each cell but since your CSV doesn't have that, one way to add those " is this:

$csv = Get-Content ./test.csv

$newCsv = $csv | ForEach-Object {
    $_ -replace '(^|$)','$0"' -replace ',','","'
} | ConvertFrom-Csv

# As you can see, this results in 1 instead of 0
PS /> $newCsv.Header1.Length
1

Edit:

Actually, the foreach-object shouldn't be needed. This works fine too:

$newCsv = $csv -replace '(^|$|[\r\n])','$0"' -replace ',','","' | ConvertFrom-Csv

Edit 2:

Adding mklement0's examples from his comments below.

  • With ForEach-Object for lower memory consumption however less efficient:

[^,]+ matches any nonempty run of characters other than , i.e. any nonempty field.

$newCsv = $csv | ForEach-Object {
    $_ -replace '[^,]+', '"$&"'
} | ConvertFrom-Csv
  • Without ForEach-Object (most efficient solution):
$csv = (Get-Content -Raw ./test.csv) -replace  '[^,\r\n]+', '"$&"' | ConvertFrom-Csv
user_153395440
Related