Ubuntu – OpenOffice ruined the CSV file – (how to save CSV on Linux with OpenOffice?)

csvopenofficeopenoffice-calcUbuntu

I am running OpenOffice 3.2 on Ubuntu.

I had a CSV (comma separated value) file which I had manually created. I opened it in OpenOffice, and entered about 100 items (to make use of using formulas to calculate values, etc.).

I then saved the file as Text (CSV). When I checked the contents of the saved file, I discovered unhappily that it was like no other CSV file I had seen before. Instead of ',', OpenOffice seemed to be using some strange character sequence (e.g. +ACI) to separate the columns.

Why?!. CSV is supposed to be one of the simplest data formats, so why can't OpenOffice save data in simple CSV?. This has ended up costing me a lot of time, as I have to hunt around for a solution for what would have been a trivial operation.

Does anyone know how to fix this?

Best Answer

Open the file again. Then, according to the help:

Choose File > Save As.

In the File name box, type a name for the file.

In the File type list, select Text CSV and click Save.
You may see a message box "This document may contain formatting or content that cannot be saved in the Text CSV file format. Do you want to save the document in this format anyway?" Click "Keep Current Format".

In the Export of text files dialog, select the options you want and then click OK.

If indeed you saw the warning in Step 3 (like because the file was using formulas now, which will be lost when saving as CSV), and you then selected "Save in ODF Format", then you would simply not have saved as CSV at all.

And as for:

CSV is supposed to be one of the simplest data formats

If it makes you feel any better: for a start, CSV needs to handle commas too. To do that, often text is quoted if it holds a comma. But then what if the text holds quotes too? And worse: in Excel on Windows even importing from CSV depends on your Regional Settings, like if to use commas or semi-colons or some other separator.

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.

Best Answer

Related Solutions

How to stop OpenOffice Calc from rounding numbers

Saving to CSV file always adds quotation marks in OpenOffice

Why do you need double quotes?

Why does Calc add them?

How can you avoid them?

Related Question