OpenOffice ruined my CSV file - (how to save CSV on Linux with OpenOffice?)

Question

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?

Solution

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

1. Choose File > Save As.
2. In the File name box, type a name for the file.
3. 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".
4. 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.

Comments

[RobotHumans]

it probably interpreted some chunk of metadata as data...is there no ',' at all or just extra garbage around it?

[Arjan]

Any chance you selected some odd character set?

[mmmmmm]

CSV is not simple you have to deal with " and , in fields and some fileds have to have " around them/. Simpler is tab (or other character) delimited which is simple but means you cannot have the delimiter in any fields - which for tab is quite likely.

[Anuj]

Hmm, sounds unecessarily convoluted. In the end I saved the file as an Excel format - opened the file in Excel (on Windows), and then saved it as a CSV file. Before transferring the CSV file back from Windows to Linux - this is plain crazy! There has to be a better way (Note: I'm not criticising your answer - that is the way I did it before you suggested it. Its just that its very "sucky" to have to do things this way).

[JRT]

@Takashi, just created a CSV file (same method as above, doesn't seem to be any other way to create one). Just accept the default values, CSV file looks fine. You can remove step 3 by naming the file <filename>.csv this will do away with the need to touch the filetype drop-down and remove the 'Keep Current Format' confirmation by unchecking the tick box.