Powershell Parsing Pipeline CSV data with newlines using ConvertFrom-Csv

user_1186367 0
Viewed 504

When trying to parse CSV data with newlines inside of fields, ConvertFrom-Csv will not work properly. Here is an example:

test.csv

name,address
John Doe,"123 Easy Way
Apt. 10
Somewhere, USA"
Jane Doe,"456 Main St
Unit 5
SomewhereElse, USA"

Proper parsing

> Import-Csv test.csv

name     address
----     -------
John Doe 123 Easy Way...
Jane Doe 456 Main St...

Improper parsing

> Get-Content test.csv | ConvertFrom-Csv

name          address
----          -------
John Doe      123 Easy Way
Apt. 10
Somewhere     USA"
Jane Doe      456 Main St
Unit 5
SomewhereElse USA"

As seen here, the newlines are parsed as new records rather than newlines within the field. How do you solve this problem?

csv powershell
2 Answers

When trying to parse CSV data with newlines inside of fields, issues using the ConvertFrom-Csv method will arise. This is due to the newlines being used as delimiters rather than passed to the ConvertFromCsv method. Depending on how you are getting the CSV, the method of working around this varies:

Proper Parsing using the -Raw switch

PS> Get-Content -raw test.csv | ConvertFrom-Csv

name     address
----     -------
John Doe 123 Easy Way...
Jane Doe 456 Main St...

While this works for the Get-Content command, this doesn't work for arbitrary commands which may return CSV to the pipeline. To take care of these situations, pipe the output through the "Out-String" command.

Proper pipeline parsing

There are 2 variations I have found which will work:

PS> <command> | Out-String | ConvertFrom-Csv

This following method is thanks to sifb in #Powershell. It's a little more "hacky", but works just fine and could have it's uses.

PS> (<command>) -join "`r`n" | ConvertFrom-Csv
user_11863670

this is rather fragile in that it has the number of lines per record hard coded. however, it works with your sample data set. [grin] i suspect that a carefully written regex would do the job faster, but i was not able to come up with such. 

# fake reading in a text file
#    in real life, use "Get-Content"
$InStuff = @'
name,address
John Doe,"123 Easy Way
Apt. 10
Somewhere, USA"
Jane Doe,"456 Main St
Unit 5
SomewhereElse, USA"
'@ -split [environment]::NewLine

$CleanedInStuff = foreach ($Index in 0..$InStuff.GetUpperBound(0))
    {
    if ($Index -eq 0)
        {
        $InStuff[$Index]
        continue
        }
    if (-not [string]::IsNullOrEmpty($InStuff[$Index]))
        {
        ($InStuff[$Index..($Index + 2)]) -join ', '

        $InStuff[$Index + 1] = ''
        $InStuff[$Index + 2] = ''
        }
    }

$FromCSV = $CleanedInStuff |
    ConvertFrom-Csv

$FromCSV |
    Export-Csv -LiteralPath "$env:TEMP\Hobadee.csv" -NoTypeInformation

'=' * 40
$CleanedInStuff
'=' * 40
$FromCSV

on screen output ... 

========================================
name,address
John Doe,"123 Easy Way, Apt. 10, Somewhere, USA"
Jane Doe,"456 Main St, Unit 5, SomewhereElse, USA"
========================================

name     address                                
----     -------                                
John Doe 123 Easy Way, Apt. 10, Somewhere, USA  
Jane Doe 456 Main St, Unit 5, SomewhereElse, USA

CSV file content ... 

"name","address"
"John Doe","123 Easy Way, Apt. 10, Somewhere, USA"
"Jane Doe","456 Main St, Unit 5, SomewhereElse, USA"
user_30809080
Related