[Solved] Colour formatting gone after close/open CSV file

[ripriprip]

I'm so upset.

I spent abt 15 hours yesterday creating a spreadsheet in open office 3.4.0 calc.

I started with a csv file that was already in the spreadsheet design that I wanted, then I added a lot of rows and colour coded the rows to reflect parts they represent.
i had almost 2000 lines, using abt 12 different cell and font colours.

I open the file today and all the colour coding is gone!!

How can I recover the colour formatting without spending another 15 hours doing this all over again, and then will I loose it?

Should I have saved the file in a different format other then csv to save the colours, if so, that's fine for next time, but how can I recover all this now.

Thanks
Tammy

[acknak]

Sorry, but your work is most likely lost. You can check in OOo's temporary file location (see [Tutorial] The OOo user profile) but once you saved the file, any temporary copies are deleted, so I don't imagine there's any hope of finding a copy with the colors.

OOo has a terrible design in this situation. When you open a csv file, it looks like a regular spreadsheet and you can do all the formatting like a regular spreadsheet, but when you save the file, OOo saves it as csv, and all the formatting is lost.

I've been bit by this many, many times over the ten years I've been using OOo. I just got bit by it again the other day. It's just too easy to lose your work.

I recommend that if you need to work with csv files in Calc, you start by File > New > Spreadsheet, then do Insert > Sheet from File and choose the csv. That way, OOo knows you're working with a full spreadsheet and not just a text file.

[MrProgrammer]

OOo has a terrible design in this situation. When you open a csv file, it looks like a regular spreadsheet and you can do all the formatting like a regular spreadsheet, but when you save the file, OOo saves it as csv, and all the formatting is lost.

Actually, I think it handles this pretty well, though my experience is with OO 3.2, and I don't know how later releases handle it. With OOo 3.2 when I open a CSV file and add some color formatting, when I save I get a prompt "This contains formatting which may be lost; do you want to save as ODS instead?" or words to that effect. So I'm forced to acknowledge that my formatting may be lost and I can save as ODS first to preserve everything, then as CSV and reopen the file to check right away before spending any more time with formatting. However, after acknowledging once that formatting may be lost, subsequent saves do not prompt again. I don't know how much better it could be, except if OOo could show the user precisely which formatting would be lost without reopening the file.

It's too bad the the OP lost 15 hours of work but OOo, at least my version, does warn about this. It also warns those who build multi-sheet files and then save as CSV with the warning "Only the active sheet was saved". That seems pretty clear to me.

[acknak]

The fact that it's even possible for an experienced user to accidentally lose work means it's a poor design.

I'm not saying it couldn't be worse, I'm only saying that it's too easy for an accident to happen. The warning is better than nothing, of course, although I suspect that many users don't fully understand it. And, even if they do, it's all too easy to save the file, do some work, save again (with no warning this time) and then close the file--and lose the work.

The better way to handle it is to make the "save" action 100% safe and secure (that means only ODF, I think). Any format that has a chance to lose any work should use a separate action path, something like File > Export as ___, and that operation should not change the saved status of the file. Sure, it is slightly more complicated--although no different than what OOo already does for pdf--but far less likely to lead to accidentally losing your work.

I was pleased to see that another application I use, that also manages lots of different file types, has recently switched to exactly this model. It is a bit odd at first, but it means that you get a simple, understandable "you haven't saved your file" warning exactly when it's needed.

Understandably, the OOo developers have never been very enthusiastic about major changes to such an important function. I don't have much hope that it's going to change any time soon.

[ripriprip]

Thanks you so much for your thoughtful replies.

Am I correct to assume that if I save the csv file as an excel type file then I will keep the colour formatting? I guess I could save it as a OO file, but then I become concerned that other people will not be able to open it, or again the file will lose something when they open it in an MSO program, or something else for Mac.

For now, that's what I'm doing, I hope it works.

Tammy

[jrkrideau]

Thanks you so much for your thoughtful replies.

Am I correct to assume that if I save the csv file as an excel type file then I will keep the colour formatting?

No, not for sure.
Probably it would be okay but there is no guarantee. The translators from Calc to Excel and vis versa are reverse engineered and while very good for basic work (such as what you seem to be doing) are by no means perfect.

Every time you open the file or save it it is possible for tiny errors to creep in and evenually make the file unusable plus Calc does things that have no Excel equivalant so if you save in Excel format that part is lost.

The general advice here from experienced users is to always save the file in AOO format and only save in another format such as MS when you need to sent the file to someone who does not use A00.

Even if you have a problem with a file in AOO there is a good chance that you can recover most or all of the data as you can unzip the file and pull out the data (well at least some of our really experienced members can do it for you or me). If an Excel file goes bad you pretty well have lost everything

I guess I could save it as a OO file, but then I become concerned that other people will not be able to open it, or again the file will lose something when they open it in an MS program, or something else for Mac.

For now, that's what I'm doing, I hope it works.

Tammy

It is possible that something will be lost any way since there is no guarantee the translation is perfect. If you have the original file in AOO format you can try other ways of saving, etc,. If you have saved it only in Excel then it's gone.

Always save in OO format and do a Save As to whatever format others are using only when you need to send them a file. Support for newer MS formats is dicey so you also should always save to MicroSoft Excel 97/2000/XP (.xls) format.

[acknak]

Right. Safest practice is to always work & save in AOO's native format.

Then, if you need a copy in some other format (csv, .xls), save off extra copies from the "master copy".

[John_Ha]

OOo has a terrible design in this situation. When you open a csv file, it looks like a regular spreadsheet and you can do all the formatting like a regular spreadsheet, but when you save the file, OOo saves it as csv, and all the formatting is lost.

Acknak

It isn't bad AOO design - it is because the user has deliberately disabled the warning which, by default, both AOO and LO give

When I open a .csv file and Save, or click the save icon, I get this pop-up which warns me formatting and content may be lost if I save as a .csv file and urges me to save as a .ods file.

You can switch it back ON in Tools > Options > Load/Save.

Also, users should know that a .csv file is intended purely as a temporary file to be used solely to transfer data between applications like to and from a database and to be deleted immediately after use. A .csv file is not a spreadsheet file format any more than a .txt file is a proper document file format.