CODEWITHSUNDEEP
.netpowershellcsvpowershell-2.0
blizz
blizz

Reputation: 4168

Powershell CSV removing newline from last cell

I have a CSV file that is in the format below:

ID,Name,Notes,Status
1,aaa,blah,
blah2,status1
2,bbb,foo,status1
3,ccc,pow,
pow2,status1

As you can see, some of the cells in the Notes column contain a comma and newline character. I need to replace these characters with a space and aggregate the data in the new line into the Notes cell of the previous row, while still bringing the status into the Status column, so that it will look like this:

ID,Name,Notes,Status
1,aaa,blah blah2,status1
2,bbb,foo,status1
3,ccc,pow pow2,status1

I came up with the following snippet:

(Import-Csv $reportfile) | % {
  $_.Notes = $_.Notes -replace "`r`n",' '
  $_.Notes = $_.Notes -replace "`n",' '
  $_.Notes = $_.Notes -replace ',',' '
  $_
} | Export-Csv $newfile -NoTypeInformation

The problem is that when the file is imported, the data which is on a line by itself is interpreted by Powershell as a row. So when I export it, it is still saved on its own row. Any ideas how I can import the file differently and still accomplish replacing those unwanted characters from that column?

Upvotes: 0

Views: 880

Answers (1)

Joey
Joey

Reputation: 354874

You could try the following:

(Get-Content -ReadCount 0 $reportfile) -replace ',\r\n(?=\D)', ' ' -split "`r`n" | ConvertFrom-Csv

(untested)

This would first read the file as a single string, then replace a comma at the end of a line followed by a line break by a space, then splits the string at line breaks and passes it to ConvertFrom-Csv.

Upvotes: 1

Related Questions