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