How to escape quotes when exporting as CSV?
Completed
I am trying to figure out how to export the results of a query into a database table when that data contains quote characters. This is my data:
id | token | country
1|12345|us
1|"12345|us
When we export as a CSV, the lone quote causes the entire file to be messed up from there on.
In the Configure CSV Formats screen, Quotation: " " Escape: duplicate is set.
It seems like this should be a fairly straightforward process. What additional settings do I need to make to be able to export a CSV in valid format?
Please sign in to leave a comment.
UPD: The issue is fixed and fix will be available in DataGrip 2020.1.1
I reopened the issue.
As a workaround you can use scripted CSV extractor with correct quotation:
Just to add, if the double quote is anywhere but the first character in the field it isn't an issue. Is this a bug?
You need to remove escaping chars for ":
This solution seems to be for importing data from CSV into the database, rather than exporting from the database to a CSV file. Turning off escaping doesn't cause the application to start escaping the data correctly.
It appears that DataGrip ONLY puts quotes around fields when there is a comma or newline in the data.
DataGrip SHOULD put quotes around fields when the field starts with a quote. Otherwise you end up with invalid fields like mentioned in my question.
The workaround I thought of was to set Quote values: always. However this has the side effect of increasing time to parse the file as well as making the file a bit larger.
By the way, I did look through the tickets and found https://youtrack.jetbrains.com/issue/DBE-5082 which you closed under the same rationale as in your reply above - there is nothing wrong with EXPORTS because you can IMPORT just fine.
Please try EXPORTING from the database the example data above to replicate the issue.
Once again, one needs to configure custom csv format:
A possible solution for your case:
Make sure you selected proper format:
It'll result:
But... the value still isn't escaped in the file. I can't use that file (testquotes.csv or buffer1.txt) in Excel because line two still has a field that starts with a " that is not escaped, and has no corresponding closing " causing record 2 and 3 to be combined in a single record.
I need to be able to take the generated CSV file and have it be in proper CSV format so I can use it in Java applications, Open Office, Word, etc, and have those applications be able to parse the data correctly.
If I create an Excel file (or Open Office Spreadsheet, or PHP's CSVfile library) and have a value "123, and save the file as CSV, that value becomes """123". Conversely, if I have a field in a CSV """123" and run it through DataGrip, it gets imported correctly, as "123 in the database.
@Bill
Try this one:
This is still not working within the latest version IntelliJ 2019.3.3
This is still a bug, and quote characters are not escaped when exporting to CSV or TSV. It is problematic when the original text begins with a quote, which intelliJ does not escape. Then if you try to import the file that you just exported, it fails with an error.
(setting "Quote values" to "Always" will fix the escaping problem, but this is not a fix for when "Quote values" is set to "When needed")
Is there any news on this issue? I have the same problem - I need to have a csv where quotes within the data are escaped. Pretty straight forward, I think...
This bug has been around for ages. I've just ended up using another client for exporting data to CSV/TSV (e.g. Postico for MacOS), which escapes quotes properly (in fact all other clients I've tried escape quotes properly without configuring anything). It is a bit annoying, but there we go...