CODEWITHSUNDEEP
powershelldelimiter
user114042
user114042

Reputation:

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

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 ,?

Upvotes: 3

Views: 11855

Answers (6)

zett42
zett42

Reputation: 27766

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

Upvotes: 1

campbell.rw
campbell.rw

Reputation: 1386

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.

Upvotes: 0

zajbo
zajbo

Reputation: 21

You must escape the pipe, so:

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

Upvotes: 2

ricky89
ricky89

Reputation: 1396

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

Upvotes: 0

E. Jaep
E. Jaep

Reputation: 2153

I would use:

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

Upvotes: 3

mjolinor
mjolinor

Reputation: 68273

Seems easy enough:

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

Upvotes: 1

Related Questions