Forum Discussion
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. Usually used for inch values like 3". That means I have an uneven number of doublequotes which triggers the use of doublequotes on export/save.)
When I export an excel to a csv file, I could get something like this:
Number;Some_text;Another_Number
1;"Hello there";33
3;"The length";77
That works fine as long as the second column does not contain something like 3" (=3 inch)
So I get:
Number;Some_Text;Another Number
1;Hello there;33
3;The length;77
6;"This is 3""";22
The csv needs to be imported into another system. That system can cope with another text enclosure character (like '). What it cannot cope with is the fact that there is suddenly 3"" (two double quote) instead of 3" (one double quote)
the import would be easier, if the line would look like
6;'This is 3"';22
Can this be done?
Yes, I could postprocess the csv through a text editor, but I do have a lot of variations of the text above.
Perhaps i can teach excel to use single quotes to enclose text instead of double quotes?
If yes, how?
Thanks
Dan
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
Pre-requisite: DAX Studio installed
- Load the data to Power Query
- Add quotes around [Column2] (see attached sample)
- Load the query as Connection only + check Add this data to the Data Model
- Launch DAX Studio
- File > Options > Custom Export Format > Switch off 'Quote String Fields' > Back
- Home (tab) > Results > File
- DAX Query: EVALUATE Table1 > Run
- ...
Result (here):
- I've "resolved" the issue by doing a search/replace with PowerShell after the creation of the CSV file.
Thanks anyway.- 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... - ThanksSure 🙂
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
