5

As far as I can see OpenOffice, when it comes to save a file as a csv-file, encloses all strings in quote-characters.

So is there any need for an escape character?

and related to this question:

Does OpenOffice have a default escape character?

sid_com
  • 24,137
  • 26
  • 96
  • 187
  • 1
    This is not really programming related. In csv, the quote is the escape character - "my name is ""fred""" –  Jan 26 '10 at 12:04
  • 1
    CSV Does not really have escape characters. If a field contains a comma, it must be quoted, and if it contains a quote, the field must be quoted as must the quote - "a single "" quote" is a field containing three words and a single quote. The CSV standard (such as it is) does not recognise things like \". –  Jun 09 '11 at 10:32

3 Answers3

4

I'm also wondering if there is a way to choose the escape character when saving OpenOffice as csv. phpmyadmin was not accepting a 9,000 line 50+ column spreadsheed in .ods format and there doesn't seem to be a way to choose the escape character when saving as CSV.

So I had to save as csv, open in word, and use some find/replace tricks to change the escape character to \ (back slash). Default is to use double quotes to escape double quotes, and phpmyadmin won't accept that format.

To properly convert the file to use \ (back-slash) to escape double-quotes, you have to do this:

  1. Pick a placeholder character string, e.g. 'abcdefg', that does not occur anywhere in the csv.
  2. Find/replace """ (three double-quotes in a row) with the placeholder. This is to prevent possibly incorrect results in the next step.
  3. Find/replace "" (two quotes in a row, representing one quote that should be escaped), with \" (back-slash double-quote). If you did this without find/replacing """ it's conceivable you could get a result like "\" instead of \"". Better safe than sorry.
  4. Find/replace the placeholder string with \"" (back-slash double-quote double-quote).

That will work, unless you happen to have more than one double-quote in a row in your original text fields, which would result in as many as five double-quotes in a row in the resulting .ods or .xlsx csv file (two double-quotes for each escaped double quote, plus another double quote if its at the end of the field).

Community
  • 1
  • 1
Buttle Butkus
  • 9,206
  • 13
  • 79
  • 120
2

Escaping in quotes makes life easier for tools parsing the CSV file.

Kees de Kooter
  • 7,078
  • 5
  • 38
  • 45
0

In a recent version of LibreOffice (3.4.4), the CSV export was not handled correctly by phpMyAdmin. Since LibreOffice doesn't provide an escape character, the phpMyAdmin's default "CSV" import feature "Columns escaped with:" didn't work well. The data was always inconsistent.

However, using the option CSV using LOAD DATA did work, only if the value in Columns escaped by option was removed. I presume phpMyAdmin uses the default MySQL LOAD DATA command, and thus the control is passed to MySQL for data processing. In my scenario it resulted in accurate data import.

Vivek Kapoor
  • 121
  • 2