Forum Discussion

DanHuber's avatar
DanHuber
Iron Contributor
Dec 25, 2023
Solved

Exporting to CSV. Can I use another character than " to enclose text?

(there is a similar question in https://superuser.com/questions/349882/how-to-avoid-double-quotes-when-saving-excel-file-as-unicode, but the case described below has an issue with single doublequotes...
excel
  • DanHuber's avatar
    DanHuber
    Dec 29, 2023

    Lorenzo 

    Sure 🙂

    First I export to a csv file. I am using ANSI encoding due to special characters.

    The script acts like sed(1) from linux.

     

    It replaces 

    ';"' with ';'

    '";' with ';'

    '""' with '"'

     

    Example

    6;"This is 3""";22

    becomes

    6;This is 3";22

     

    Here is the rather simple script:

     

    # fixquotes.ps1

param (
    [string]$FilePath
)

# Read the content of the file
$content = Get-Content -Raw $FilePath

# Perform the desired replacements
$newContent = $content -replace ';"', ';' -replace '";', ';' -replace '""', '"'

# Remove empty lines at the end
$newContent = $newContent.TrimEnd()

# Get the original file extension
$originalExtension = [System.IO.Path]::GetExtension($FilePath)

# Construct the output file path with the same basename and the desired suffix
$originalBasename = [System.IO.Path]::GetFileNameWithoutExtension($FilePath)
$outputFilePath = "$originalBasename-quotesfixed$originalExtension"

# Write the modified content to the output file with ANSI encoding
$newContent | Set-Content -Path $outputFilePath -Encoding Default

# Optional: Display a message indicating success
Write-Host "Replacements completed. Modified content saved to '$outputFilePath' with ANSI encoding."

     

    Have fun.


    Daniel

     

DanHuber's avatar
DanHuber
Iron Contributor
Dec 29, 2023
I've "resolved" the issue by doing a search/replace with PowerShell after the creation of the CSV file.
Thanks anyway.
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 29, 2023
You're welcome
Maybe you could share your PowerShell script and mark it as Best solution
I'm quite sure this will help some people... - Thanks
  • DanHuber's avatar
    DanHuber
    Iron Contributor
    Dec 29, 2023

    Lorenzo 

    Sure 🙂

    First I export to a csv file. I am using ANSI encoding due to special characters.

    The script acts like sed(1) from linux.

     

    It replaces 

    ';"' with ';'

    '";' with ';'

    '""' with '"'

     

    Example

    6;"This is 3""";22

    becomes

    6;This is 3";22

     

    Here is the rather simple script:

     

    # fixquotes.ps1

param (
    [string]$FilePath
)

# Read the content of the file
$content = Get-Content -Raw $FilePath

# Perform the desired replacements
$newContent = $content -replace ';"', ';' -replace '";', ';' -replace '""', '"'

# Remove empty lines at the end
$newContent = $newContent.TrimEnd()

# Get the original file extension
$originalExtension = [System.IO.Path]::GetExtension($FilePath)

# Construct the output file path with the same basename and the desired suffix
$originalBasename = [System.IO.Path]::GetFileNameWithoutExtension($FilePath)
$outputFilePath = "$originalBasename-quotesfixed$originalExtension"

# Write the modified content to the output file with ANSI encoding
$newContent | Set-Content -Path $outputFilePath -Encoding Default

# Optional: Display a message indicating success
Write-Host "Replacements completed. Modified content saved to '$outputFilePath' with ANSI encoding."

     

    Have fun.


    Daniel

     

Resources