Remove New Line Character from CSV file's string column

Question

I have a CSV File with a string column were that column spans to multiple lines. I want to aggregate those multiple lines into one line.

For example

1, "asdsdsdsds", "John"
2, "dfdhifdkinf
dfjdfgkdnjgknkdjgndkng
dkfdkjfnjdnf", "Roy"
3, "dfjfdkgjfgn", "Rahul"

I want my output to be

1, "asdsdsdsds", "John"
2, "dfdhifdkinf dfjdfgkdnjgknkdjgndkng dkfdkjfnjdnf", "Roy"
3, "dfjfdkgjfgn", "Rahul"

I want to achieve this output using PowerShell

Thanks.

Answer 1

Building on Ansgar's answer, here's how to do it when:

• You don't know the column names

• Your CSV file may contain CR or LF independently

  (Import-Csv $csvInput) | % {
      $line = $_
      foreach ($prop in $line.PSObject.Properties) {
          $line.($prop.Name) = ($prop.Value -replace '[\r\n]',' ')
      }
      $line
  } | Export-Csv $csvOutput -NoTypeInformation
  

Answer 2

Try this:

$csv = 'C:\path\to\your.csv'

(Import-Csv $csv -Header 'ID','Value','Name') | % {
  $_.Value = $_.Value -replace "`r`n",' '
  $_
} | Export-Csv $csv -NoTypeInformation

If your CSV contains headers, remove -Header 'ID','Value','Name' from the import and replace Value with the actual column name.

If you don't want double quotes around the fields, you can remove them by replacing Export-Csv with something like this:

... | ConvertTo-Csv -NoTypeInformation | % { $_ -replace '"' } | Out-File $csv

To remove the header from the output you add another filter before Out-File to skip the first line:

... | select -Skip 1 | Out-File $csv