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