Saving to CSV file always adds quotation marks in OpenOffice

csvopenoffice-calc

I'm trying to use OpenOffice Calc for editing CSV files with TAB as delimiter. No matter what I do, Calc is insisting on adding quotation marks (") to the strings. If a cell content is number only, it's fine.

Is there a way to not let Calc do this? I mean, the TAB is enough for any parser, right?

Best Answer

Why do you need double quotes?

I mean, the TAB is enough for any parser, right?

No, it technically isn't! As mentioned in my answer about Excel's CSV format, the Common Format and MIME Type for CSV Files (RFC 4180) specifies that:

… each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.

So far, so good. Calc obviously chooses to always enclose it in double quotes.

Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes

… so this is where a double quote has to be in order for the CSV to be parsed correctly again.

Why does Calc add them?

Calc is probably doing that as a safety measure for all text fields. On the contrary, what Excel is doing is somewhat ambiguous.

How can you avoid them?

I found a forum post that suggests using a macro to disable text delimiters:

Dim oDocCsv As Object       
Dim CsvProperties(2) As New com.sun.star.beans.PropertyValue 

.... 

CsvProperties(0).Name    = "FilterName" 
CsvProperties(0).Value   = "Text - txt - csv (StarCalc)" 
CsvProperties(1).Name    = "FilterOptions" 
CsvProperties(1).Value   = "124,0,ANSI,2" 

.... 

oDocCsv.storeAsURL("test.csv", CsvProperties())

The important option here is the line with 124,0,ANSI,2. According to the Filter Options reference, the second part (i.e. the 0) disables text delimiters upon export.

Related Question