Ask Your Question
0

CSV files lose formatting after save. [closed]

asked 2013-09-30 10:19:33 +0200

oliver.nadj gravatar image

updated 2016-02-19 09:39:37 +0200

Alex Kemp gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 09:39:52.632834

Comments

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.

oweng gravatar imageoweng ( 2013-09-30 10:32:59 +0200 )edit

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

L-user gravatar imageL-user ( 2013-09-30 11:18:02 +0200 )edit

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 gravatar imageoliver.nadj ( 2013-10-01 09:15:53 +0200 )edit

@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.

oweng gravatar imageoweng ( 2013-10-02 01:13:30 +0200 )edit

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.

oliver.nadj gravatar imageoliver.nadj ( 2013-10-02 06:30:54 +0200 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2013-09-30 11:27:17 +0200

Pedro gravatar image

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.

edit flag offensive delete link more

Comments

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.

oweng gravatar imageoweng ( 2013-10-02 01:15:27 +0200 )edit

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, @Pedro

oliver.nadj gravatar imageoliver.nadj ( 2013-10-02 06:36:17 +0200 )edit

@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 ;) 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

Pedro gravatar imagePedro ( 2013-10-02 11:14:44 +0200 )edit

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

oliver.nadj gravatar imageoliver.nadj ( 2013-10-04 06:58:28 +0200 )edit

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.

u81 gravatar imageu81 ( 2014-04-30 12:49:26 +0200 )edit
0

answered 2014-02-09 18:23:54 +0200

adam r gravatar image

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: https://help.libreoffice.org/Calc/Importing_and_Exporting_CSV_Files

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.

edit flag offensive delete link more

Comments

@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?

Pedro gravatar imagePedro ( 2014-02-10 11:04:26 +0200 )edit

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'!).

MJE gravatar imageMJE ( 2014-02-11 14:02:17 +0200 )edit
0

answered 2013-09-30 11:13:13 +0200

L-user gravatar image

updated 2013-09-30 11:19:24 +0200

  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?

edit flag offensive delete link more

Comments

@L-user, we really need to stop answering the same questions :) 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 ;) )

Pedro gravatar imagePedro ( 2013-09-30 11:36:42 +0200 )edit

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 gravatar imageL-user ( 2013-09-30 12:24:48 +0200 )edit

@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 ;)

Pedro gravatar imagePedro ( 2013-09-30 12:54:40 +0200 )edit

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.

oliver.nadj gravatar imageoliver.nadj ( 2013-10-01 09:33:58 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-09-30 10:19:33 +0200

Seen: 52,196 times

Last updated: Feb 09 '14