In Powershell Script, how do I convert a pipe '|' delimited file to a comma ',' delimited CSV?

Question

In Powershell Script, how do I convert a | (pipe) delimited CSV file to a , (comma) delimited CSV file?

When we use the following command in Windows Powershell Encoding 'UTF8' -NoType to convert from | (pipe delimiter) to , (comma delimiter), the file is converted with , delimited but the string was surrounded by " " (double quotes). Like given below:

Source file data:

ABC|1234|CDE|567|

Converted file data:

"ABC","1234","CDE","567",

I want to generate the following:

ABC,1234,CDE,567,

What command can I use to convert the delimiter from | to ,?

Quoted values are still valid CSV, and probably safer, Are you certain you can't use them? — alroc, Jan 08 '14 at 13:58
what do you want to happen if there's a comma in the source ex. : ABC|1,23|DE,F|456 ? — Pieter B, Jan 08 '14 at 15:16

E. Jaep · Answer 1 · 2014-01-08T14:35:15.177

3

I would use:

(Get-Content -Path $file).Replace('|',',') | Set-Content -Path $file

edited Jan 08 '14 at 14:35

answered Jan 08 '14 at 13:51

E. Jaep

2,095
1
30
56

No output file is produced. – alroc Jan 08 '14 at 13:58
@alroc you still need to pipe it to the output. I have corrected the sample code. – E. Jaep Jan 08 '14 at 14:34

score 2 · Answer 2 · edited Nov 30 '16 at 22:32

2

You must escape the pipe, so:

(get-content "d:\makej\test.txt" ) -replace "\|","," | set-content "d:\makej\test.csv"

edited Nov 30 '16 at 22:32

Floern

33,559
24
104
119

answered Nov 30 '16 at 22:31

zajbo

21
1

1

This doesn't escape commas if they are present in the source file. – Stevoisiak Jun 17 '20 at 16:49

score 1 · Answer 3 · answered Jan 08 '14 at 13:36

1

Seems easy enough:

(get-content $file) -replace '|',',' | set-content $file

answered Jan 08 '14 at 13:36

mjolinor

66,130
7
114
135

zett42 · Answer 4 · 2022-09-03T08:42:33.833

In general, you should use the commands Import-Csv and Export-Csv which properly handle delimiters embedded in the field values, such as Field,1|Field2. The Get-Content based solutions would turn this into 3(!) fields Field,1,Field2, while the output actually should be quoted like "Field,1",Field2 or "Field,1","Field2".

Import-Csv input.csv -Delimiter '|' | Export-Csv output.csv -Delimiter ','

This always quotes fields in "output.csv". Since PowerShell (Core) 7+, the new Export-Csv parameters -UseQuotes and -QuoteFields allow us to control the quoting of the output file.

E. g. to quote only if necessary (when a field value contains the delimiter or quotation marks):

Import-Csv input.csv -Delimiter '|' | Export-Csv output.csv -Delimiter ',' -UseQuotes AsNeeded

Be careful with -UseQuotes Never, because it can render the output file unreadable, if a field value contains embedded delimiter or quotation marks.

Here is a function to convert to unquoted CSV for PowerShell 5.x (possibly supports older versions as well). This is like -UseQuotes Never, so make sure your data doesn't contain the delimiter. Additionally you may omit the header by passing the -NoHeader switch.

Function ConvertTo-CsvUnquoted {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory, ValueFromPipeline)] $InputObject,
        [string] $Delimiter = ',',
        [switch] $NoHeader
    )
    process {
        if( -not $NoHeader ) { 
            $_.PSObject.Properties.Name -join $Delimiter
            $NoHeader = $true 
        } 
        $_.PSObject.Properties.Value -join $Delimiter
    }
}

Usage example:

Import-Csv input.csv | ConvertTo-CsvUnquoted -Delimiter '|' | Set-Content output.csv

score 0 · Answer 5 · answered Apr 07 '14 at 17:06

Sorry this may need some tweaking on your part, but it does the job. Note that this also changes the file type from .txt to .csv which I dont think you wanted.

$path = "<Path>"
$outPath = $path -replace ".txt",".csv"
Get-Content -path $path | 
ForEach-Object {$_ -replace "|","," } |  
Out-File -filepath $outPath

score 0 · Answer 6 · edited May 23 '17 at 12:30

I view the suggested answers as a little risky, because you are getting the entire contents of the existing file into memory, and therefore won't scale well, and risks using a lot of memory. My suggestion would be to use the string replace as the previous posts suggested, but to use streams instead for both reading and writing. That way you only need memory for each line in the file rather than the entire thing.

Have a look here at one of my other answers here: https://stackoverflow.com/a/32337282/380016

And in my sample code you'd just change the string replace to:

$s = $line -replace '|', ','

And also adjust your input and output filenames accordingly.

In Powershell Script, how do I convert a pipe '|' delimited file to a comma ',' delimited CSV?

6 Answers6