Remove line breaks from csv file created from excel

Question

I have written a vba macro that can save a range in excel into a csv. Each record is supposed to occupy 1 line in my csv file. However, that is a case that a cell in my excel file has line breaks (ALT + ENTER), which caused the record to break into 2 lines. I want to remove line breaks in the csv file using a powershell script (My powershell version is V2).

Currently what I do to remove double quotes from the csv file is like this:

(gc $fileloc) |% {$_ -replace '"',''}|set-content $fileloc

I searched the internet and tried the below code to remove line breaks but it didn't work:

(gc $fileloc) |% {$_ -replace '`n',''}|set-content $fileloc

UPDATE: as a comment says that sample data could help, I would make one here to make my question clearer:

MY EXCEL FILE RANGE (THE PIPELINE DELIMITS CELLS):

A1| B1|C1|D1  (There is a line break at the beginning of B1)

A2|B2|C2|D2

WHAT I WANT TO EXPORT AS CSV:

A1,B1,C1,D1
A2,B2,C2,D2

WHAT THE CSV LOOKS LIKE RIGHT NOW:

A1,
B1,C1,D1
A2,B2,C2,D2

Russell Russell · Accepted Answer · 2018-01-17T02:30:40

Powershell will return each line in the array, so you need to check for empty lines:

(gc $fileloc) |? { $_ -ne "" }|set-content $fileloc

Edit: I may have misinterpreted your question, there is a good chance that excel included a carriage return character as well.

The following will remove both carriage return and line feed chars:

(gc $fileloc) |% {($_ -replace '`r','') -replace "`n",''}|set-content $fileloc

Finally: Your code doesn't parse it as a CSV, simply as a multi-line text file. This means it is unable to differentiate between the end of a line, and content over multiple lines.

You can use the Import-CSV (which is included in PSv2) to parse the file as CSV, then iterate through each CSV line to remove line breaks.

For example: (Don't have access to powershell v2 using Win10)

$csv = (Import-Csv $fileloc)
foreach($row in $csv)
{
  $row.MyField = $row.MyField -replace "`n"
}
$csv | Export-Csv $fileloc

Remove line breaks from csv file created from excel

1 Answers