We help IT Professionals succeed at work.

How to remove Carriage return from the XLS and  XLSX files using powershell

Kiran Kumar
Kiran Kumar used Ask the Experts™
on
There is Carriage return in my file(xls and xlsx format). I want use powershell to remove filter on the columns and carriage return in the whole file cells and convert it to csv file.

Please find the attached file and generate the csv file [embed=file Capture.PNG1437800]
New-Microsoft-Excel-Worksheet.csv
New-Microsoft-Excel-Worksheet.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Try something like in PowerShell 5+:
((Get-Content $xlsFile) -join "`n") + "`n" | Set-Content -NoNewline $csvFile

Open in new window

Author

Commented:
Hello Kevin, Thanks for the help but by some reason its throwing below error . I am using Version 4.0

Capture1.PNGCapture2.PNG
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Probably because XLSX is stored as XML and not just plain text.

Author

Commented:
ok, Can you please suggest a way to make it work.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Yes will take a look.  It looks like we can Excel COM object so it should be looping the rows in spreadsheet and replace the carriage return and line feed in each cell with whatever you want like space or just line feed.

$excel = New-Object -Com Excel.Application
$wb = $excel.Workbooks.Open($xlsFile)

Open in new window


Not at computer right now but will try when I get back to one.
$Data =   (Get-Content $csvpath -Raw) -replace("`n","")
   $Cleanup = $Data.Replace("`r`n","") | Set-Content $csvpath -Force