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

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?

Takashi

Posted 2010-12-07T13:46:37.800

Reputation: 135

1it probably interpreted some chunk of metadata as data...is there no ',' at all or just extra garbage around it? – RobotHumans – 2010-12-07T13:55:12.520

Any chance you selected some odd character set?

– Arjan – 2010-12-07T15:27:19.337

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. – user151019 – 2010-12-07T17:31:53.597

Answers

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.

Arjan

Posted 2010-12-07T13:46:37.800

Reputation: 29 084

@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. – JRT – 2010-12-07T17:56:30.077

Can you still open it and get it to show correctly in Open Office? You can tell it which character delimits the files when you open it.

If so, you might try saving it first as another format, for instance Excel, then open that file and save as csv. Then you'll get a choice as to which field delimiter you want to use.

Lizzan

Posted 2010-12-07T13:46:37.800

Reputation: 262

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). – Takashi – 2010-12-07T15:12:58.727

I had a similar problem, but noticed the funny 'ACI' encoding went away when I changed the encoding in the Text Import dialog (looks like this) from UTF-7 to UTF-8.

beardc

Posted 2010-12-07T13:46:37.800

Reputation: 121