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

1,841
1
29
50

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

32,709
24
103
117

answered Nov 30 '16 at 22:31

zajbo

21
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

62,812
6
108
130

score 0 · Answer 4 · 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 5 · 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?

5 Answers5