When creating a CSV file, do you need to escape certain characters?

Question

I am generating a CSV file from Ruby. The problem is a column string will contain double quotes, single quotes. How can I escape these things?

"Mary had a little so called \"lamb\"","34","none"
"something is not \"right\"","23","none"

Each column is enclosed in double quotes followed by comma (and no space), and written into a file.

Additionally, how do you insert CSV into MySQL? Would you need to use something like PHP's mysql_real_escape_string?

Answer 1

Writing CSV data is easy, the simplest way is to replace every instance of a double quote with 2 double quotes and then surround the whole thing in double quotes. Alternatively, if your data does not contain double quotes, commas, carriage returns, linefeeds, or leading/trailing space, you don't have to surround the data with quotes or worry about escaping. You can find more information here.

Parsing CSV is much more complex, especially if you try to handle various forms of malformed data out there, in this case you almost certainly want to use an existing module.

Answer 2

Use FasterCSV. Do not roll your own CSV generation.

http://fastercsv.rubyforge.org/