Search code examples
powershellcarriage-returnlinefeed

Powershell: Editing all Line Feeds and Carriage Returns in a CSV file

Goal is to remove all single "Line Feeds"(LFs) and keep the "Line Feeds" which follow a Carriage Return(CR) in a csv file.

I got a report which includes several LFs in one row, but I only want to keep the "CR+LF" so every row stands for one reported object.

I need a solution in PowerShell, unfortunately I am very new to PowerShell scripting. I tried to alter some scripts on this side for this job, but its not working.

First, I would try to remove all LFs in the file, and then I would replace all remaining CRs with [CR][LF]. But I did not achieve the first step.

$original_file ='C:\Test\Server.csv'
$new_file = 'C:\Test\Server_changed.csv'
(Get-Content $original_file -Raw).Replace('´n',' ') | Set-Content $new_file -Force
(Get-Content $new_file -Raw).Replace('`r','`r`n') | Set-Content $new_file -Force

Source CSV:

"Servername";"CPU","Memory";"Annotation";"OperatingSystem"[CR][LF]
"Server1";"4";"8";"very importand Server!![LF]
If reboot is needed:[LF]
1. Contact Me[LF]
2. Stop all running Services before shutting down the OS[LF]
";"Windows Server 2019";[CR][LF]

How it should look:

"Servername";"CPU","Memory";"Annotation";"OperatingSystem"[CR][LF]
"Server1";"4";"8";"very importand Server!! If reboot is needed: 1. Contact Me 2. Stop all running Services before shutting down the OS ";"Windows Server 2019";[CR][LF]
Solution

  • You can use the -replace operator multiple times to achieve the result.

    $original_file ='C:\Test\Server.csv'
$new_file = 'C:\Test\Server_changed.csv'

(Get-Content $original_file -Raw) -replace "(?<!\r)(\n)" -replace "\r(?!\n)","`r`n" |
    Set-Content $new_file -NoNewLine -Force

    Explanation:

    • -replace is the regex replace operator as opposed to the string class .Replace(). -replace is used so that we can access the regex mechanisms negative lookahead ((?!)) and negative lookbehind ((?<!)). In each -replace operation, the first set of quotes represent the regex pattern to capture the data you want to replace. The second set of quotes represent the replacement string. If you specify no second set of quotes, then the captured data will just be removed.

    • -Raw switch is used in Get-Content to prevent PowerShell from reading the file in as an array, which will add newline characters to the data in memory.

    • -NoNewLine switch on Set-Content is used to not add an additional, trailing newline character at the end of the output file.