CODEWITHSUNDEEP
powershell
Alt F4
Alt F4

Reputation: 43

Change delimiter in a csv from ; to ,

I cannot seem to change the delimiter in a .csv from ; to ,.

The issue is that one of our overseas customers have a default delimiter of ; because of regional settings, but our system requires the , separator.

I cannot use the -replace because there are semicolons in some string entries that need to be kept there.

Here are the options I have tried:

Import-Csv -path $home\ | Export-Csv C:\Users\Dean\Desktop\test.csv -useculture

Import-Csv -path $home | Export-Csv C:\Users\Dean\Desktop\test\test.csv -Delimiter ","

Import-Csv -path $home | Export-Csv C:\Users\Dean\Desktop\test.csv -Replace "^\"|\"$", ""

I have also tried

Import-Csv -UseCulture | ConvertTo-Csv | Out-File and Import-Csv -Delimiter | ConvertTo-Csv | Out-File

None of these options are throwing out errors but the delimiter is still ; when checking the csv again. The only time I have seen these args work are if I export or outfile a csv to a location without the -delimiter or -useculture arg.

Upvotes: 4

Views: 10869

Answers (1)

StephenP
StephenP

Reputation: 4081

Creating a csv using ; as the delimiter:

Get-ChildItem c:\ | Select-Object FullName, Name, LastWriteTime |Export-Csv c:\temp\test.csv -Delimiter ';' -notypeinformation

Sample of the csv:

"FullName";"Name";"LastWriteTime"
"C:\APP-V_Global";"APP-V_Global";"8/5/2013 12:45:04 PM"
"C:\Program Files";"Program Files";"10/4/2017 7:48:06 AM"
"C:\Program Files (x86)";"Program Files (x86)";"10/10/2017 1:44:51 PM"
"C:\Temp";"Temp";"12/5/2017 1:29:34 PM"
"C:\Users";"Users";"9/6/2017 8:30:00 AM"
"C:\Windows";"Windows";"11/17/2017 4:16:43 PM"

Import the csv telling powershell what the delimiter is:

$imported = Import-Csv c:\temp\test.csv -Delimiter ';'

Sample output:

$imported
FullName                             Name                                 LastWriteTime
--------                             ----                                 -------------
C:\APP-V_Global                      APP-V_Global                         8/5/2013 12:45:04 PM
C:\Program Files                     Program Files                        10/4/2017 7:48:06 AM
C:\Program Files (x86)               Program Files (x86)                  10/10/2017 1:44:51 PM
C:\Temp                              Temp                                 12/5/2017 1:29:34 PM
C:\Users                             Users                                9/6/2017 8:30:00 AM
C:\Windows                           Windows                              11/17/2017 4:16:43 PM

Export the information back out:

$imported | Export-Csv C:\temp\test.csv -delimiter ',' -notypeinformation

File Contains:

"FullName","Name","LastWriteTime"
"C:\APP-V_Global","APP-V_Global","8/5/2013 12:45:04 PM"
"C:\Program Files","Program Files","10/4/2017 7:48:06 AM"
"C:\Program Files (x86)","Program Files (x86)","10/10/2017 1:44:51 PM"
"C:\Temp","Temp","12/5/2017 1:29:34 PM"
"C:\Users","Users","9/6/2017 8:30:00 AM"
"C:\Windows","Windows","11/17/2017 4:16:43 PM"

The basic process works so I'm wondering if your input files are the issue. If this isn't working please considering sanitizing and posting a sample csv.

Upvotes: 7

Related Questions