CSV files lose formatting after save

I use a cash-register which can export and import products to CSV format. The exported (and the required) format looks like this:

"foo";"bar"
"lorem";"1"
"ipsum";"2"

After I editing and save even if I click on Use Text CSV Format button the formatting changes an looks like this:

foo	bar
lorem	1
dolor	3

Can I somehow tell the spreadsheet editor to keep the format as is?

What is your operating system and LO version? I can’t reproduce this here under GNU/Linux running v4.1.0.4. The saved file retains the required formatting.

Does it save correctly on the first time and then when you select File | Save As again you lose formatting? Is it?

my os is: Ubuntu 12.04.3 LTS
and the version of cacl is: LibreOffice 3.5.7.2 Build ID: 350m1(Build:2)

the edited document lose it’s format after the first save (i did not use save as).

cc @oweng, @L-user,

@oliver.nadj, I would suggest either upgrading (because this certainly does not occur using LO v4.1.0.4 or v4.1.2.2 under Ubuntu 10.04) or raising a Launchpad bug as it may be a problem with the Ubuntu-provided version of LO you are running.

hi @owneg,
i just upgraded LO to: 4.1.1.2 Build ID: 410m0(Build:2) but did not help. this behavior is still remain.

I can confirm that this happens under Windows XP en_US (using LO 4.1.2.2)

You can’t really “keep the format as is” but you can Save as (and overwrite the original file, so it’s almost the same as updating).

In the Save As dialog, check the last box “Edit filter settings” and then on the Export Text File dialog make sure you check the fourth box “Quote all text cells”

Please notice that cells containing numbers will not be quoted. Make sure this is not a problem with your cash-register software.

That is very strange. I have no problem here at all, using either Save or Save As… If numbers are imported as text (by having the Quoted field as text option checked) then these fields are written out the same way. New numerics are however written out without quotes.

i considered to mark this answer as correct one because i cannot mark both and it is a slightly exact than the other one.
Thx for both of you @L-user, @Pedro1

@oliver.nadj as i mentioned in my post: make sure that your cash-register doesn’t have a problem if values are not quoted. Answering your other comment: now you can become a “Ctrl+Shift+S” freak, you just need to use one more finger :wink: Alternatively you can ask for a new feature (e.g. an option for CSV files to keep the original text delimiters and separators) at Bugzilla

thx @Pedro1 for the suggestion. i just create a ticket https://bugs.freedesktop.org/show_bug.cgi?id=70111

The edit filter settings was the key for me. I had come across this issue for quite some time, but the edit filter settings > quote all text cells did the trick. Thanks. I can’t vote this answer yet but wanted to vouch its validity.

  1. File | Save as.

  2. In Save As dialog select "Text CSV (*.csv) and in the same dialog click on “Edit filter settings” checkbox.

  3. New dialog opens. Select appropriate format you would like to have (look at Field delimiter and Quote all text cells).

P.S. Some of the default values may be dependent on language settings. Check Tools | Options | Language Settings | Languages.

P.S.S. I tested this on LibreOffice 4.0.5 on Windows. What is your operating system and LibreOffice version?

@L-user, we really need to stop answering the same questions :slight_smile: This is a waste of time for both of us. To prevent both of us giving up on answering, I give up first. (I’m not writing this because I’m upset, I’m just being practical :wink: )

Don’t know does this askbot have something like notification when someone else is trying to answer? For example: 1. user click on Answer box and starts typing, 2. second user clicks on Answer box and got notification that someone else is typing the answer. This would be very very helpful…

@L-user, unfortunately askbot doesn’t work that way (in fact very few software tools have that feature). You seem to have more time available to collaborate on this platform so it is better for users that you answer them :wink:

i can nearly reproduce the wanted format in that mentioned way, but it is really annoying (i am a Ctrl+S freak) and the number values still lose theirs quotes.

There is some complicated behavior related to this. Here’s what I’ve identified on my system (4.1.4.2-4.fc19 on Fedora). You should be able to get the results you want using the following description, but it is clumsy and non-intuitive.

First, here’s the official description: Importing and Exporting CSV Files - LibreOffice Help

In my experience, the first time I save a file as CSV, I am given the “Export Text File” prompt, and I can select whatever delimiter I want (confirmed with gEdit). However, if I open the file to edit it, I do not see that prompt the next time that I save. For subsequent saves, tabs are used, even if I had used commas originally.

I think this may be connected to the “Text Import” dialog choices. If I import the text with multiple delimiters selected, then the next time it is saved with tabs. However, if I deselect the non-comma delimiters, then it saves with comma delimiters. My impression is that the default is to import with comma, tabs, and semicolons as delimiters.

If I want to specify the delimiters (again), I’ve found that I can select the “Edit Filter Settings” checkbox on the save dialog. After clicking OK and then confirming that I do really want to save the file with the selected name and format, the “Export Text File” prompt will show up again. My guess is that this is the recommended way to select the delimiter, though I found this to be non-intuitive and clumsy.

@adam-r, you can see in the comments above that a feature request has already been submitted so that original text delimiters and separators are kept (instead of automatically replacing them with tabs). However it probably makes sense to add another Feature Request so that LO accepts automatically CSVs with the standard separators (tab, comma AND semi-comma). Would you like to do that?

Simpler for the user if Libre Office saved (by default?) in the exact format found when the file was opened.
The filter omits some options - e.g. record delimiter ‘LF’ (without ‘CR’!).