Archive for the ‘MySQL’ Category
CSV Escaping Double Quotes
What is the proper way to escape double-quotes in CSV files?
When you try to export from MySQL in CSV format, the MySQL reference manual itself suggests using
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
Now, I thought I had a nicely formatted CSV file where fields that have double quotes would be properly escaped. Looking through this .csv file, I can see that sure enough, all the double-quotes are turned from ” into \”.
This is fine, except that when I tried to use Excel to open the CSV file or FasterCSV (a nice Ruby library for working with CSV files), I find that the double quotes were not escaped “properly”. They are expecting to escape double-quotes with TWO double-quotes (”"). According to CSVreader.com, this is a known discrepancy between the way different programs escape double-quotes in CSV files. Great.
Well, don’t worry – here’s a sed 1-liner that you can use on the command line to convert those \” (backslash double-quote) into “” (two double-quotes):
$cat file.csv | sed -E 's/\\"/""/g' > file2.csv
Hope that helps someone out there!
