Cell formatting with CSV files in Calc

[berntie]

Hi, I'm having an issue with cell formatting in CSV files. Consider the
following simple CSV file (should be self explanatory):

Date;Item;Amount
01.12.2011;New laptop;-1.000,00
02.12.2011;Glass of beer;-3,00

(I'm using a German locale, so the comma is the decimal spearator.) If you
import that into Calc and format the date column with a rule like "NNNNTT.
MMMM JJJJ" and the amount column as a currency with a rule like "#.##0,00
[$EUR];[ROT]-#.##0,00 [$EUR]", then everything works fine (negative amounts
are red etc.) Save the CSV file and you end up with

"Date";"Item";"Amount"
Donnerstag, 01. Dezember 2011;"New laptop";-1.000,00 EUR
Freitag, 02. Dezember 2011;"Glass of beer";-3,00 EUR

in the file. Now open that file again with Calc and try to format the amount
column with the same rule: fail. New entries are formatted correctly, but
not existing entries. It works for the date column if you select date as the
column type in the import dialog, but you cannot choose currency as column
type.

I'm tempted to think that this is either a missing feature (no column type
"currency" in the import dialog) or incorrect saving/exporting behavior by
Calc (the cell format should only affect how the values are displayed, not
how they are stored in the file). So, what should I do:

* File a bug/feature request? If yes, where can I do that?
* Work around the issue? What would that workaround be??
* Or am I using Calc the wrong way?

Any suggestions appreciated...

Thanks, Bernhard

[planas]

Hi, I'm having an issue with cell formatting in CSV files. Consider the
following simple CSV file (should be self explanatory):

Date;Item;Amount
01.12.2011;New laptop;-1.000,00
02.12.2011;Glass of beer;-3,00

(I'm using a German locale, so the comma is the decimal spearator.) If you
import that into Calc and format the date column with a rule like "NNNNTT.
MMMM JJJJ" and the amount column as a currency with a rule like "#.##0,00
[$EUR];[ROT]-#.##0,00 [$EUR]", then everything works fine (negative amounts
are red etc.) Save the CSV file and you end up with

"Date";"Item";"Amount"
Donnerstag, 01. Dezember 2011;"New laptop";-1.000,00 EUR
Freitag, 02. Dezember 2011;"Glass of beer";-3,00 EUR

in the file. Now open that file again with Calc and try to format the amount
column with the same rule: fail. New entries are formatted correctly, but
not existing entries. It works for the date column if you select date as the
column type in the import dialog, but you cannot choose currency as column
type.

Correct

I'm tempted to think that this is either a missing feature (no column type
"currency" in the import dialog) or incorrect saving/exporting behavior by
Calc (the cell format should only affect how the values are displayed, not
how they are stored in the file). So, what should I do:

* File a bug/feature request? If yes, where can I do that?

File a feature request for a currency data type

* Work around the issue? What would that workaround be??

Work around appears to be import as standard, then format the column as desired. Also, once the data is imported save the Calc spreadsheet as *.ods to preserve formating.

I have seen with dates when I manual format an imported column Calc adds a ' to the original data treating the entry as text not a date. This occurs when I leave the import data type as standard not as date.

[Pedro_Lino]

berntie wrote

"Date";"Item";"Amount"
Donnerstag, 01. Dezember 2011;"New laptop";-1.000,00 EUR
Freitag, 02. Dezember 2011;"Glass of beer";-3,00 EUR

There is a basic problem in your procedure: you can not export formats in
the CSV file. So when you export the dates in the first column and you see
them in your csv file as long date, it means that Calc actually converted it
to the the text you are seeing. When you import it again, the cells contain
text.

As for currency, I'm not aware of a Currency data type. It is either Number
(Standard), Text or Date.

Can you explain why you want to export as CSV including formats? Maybe we
can throw same alternative solutions...

[berntie]

Pedro wrote

Can you explain why you want to export as CSV including formats? Maybe we
can throw same alternative solutions...

First of, I've unintentionally replied to your address only. Sorry for that.

I just want to export as CSV, I don't want to export the formats. Basically:

I want to edit a CSV. And by "editing" I mean "adding and/or modifying
lines". Just for convenience, It would be nice to have the amount column
formatted as a currency.

I do not require the formatted values to be written to the CSV file upon
saving, but _if_ they are written to the file then I'd need a possibility to
format those values as a currency when I open the file the next time. That's
what I cannot accomplish as for now.

[berntie]

Jay Lozier wrote

* File a bug/feature request? If yes, where can I do that?

File a feature request for a currency data type

Where can I do that?

Jay Lozier wrote

* Work around the issue? What would that workaround be??

Work around appears to be import as standard, then format the column as
desired. Also, once the data is imported save the Calc spreadsheet as
*.ods to preserve formating.

Thanks for the suggestion but saving as *.ods is not an option for me as I
need the resulting file to be a text file.

[Pedro_Lino]

berntie wrote

First of, I've unintentionally replied to your address only. Sorry for
that.

No problem

berntie wrote

I just want to export as CSV, I don't want to export the formats.
Basically:

I want to edit a CSV. And by "editing" I mean "adding and/or modifying
lines". Just for convenience, It would be nice to have the amount column
formatted as a currency.

I do not require the formatted values to be written to the CSV file upon
saving, but _if_ they are written to the file then I'd need a possibility
to format those values as a currency when I open the file the next time.
That's what I cannot accomplish as for now.

Ok. That is what I thought you were trying to do. What you need to do is

1) You CAN'T use the Save button
2) You must always Save As (although you can overwrite your csv file)
3) In the Save As dialog, always check the bottom box "Edit filter settings"
4) On the next dialog choose "Keep current format" (meaning save as CSV)
5) On the Export Text File dialog, make sure you UNCHECK the "Save cell
content as shown" option (this tells Calc not to save long date as the text
you see on screen but save the value) and CHECK the "Quote all text cells"
option (just to make sure all text is properly saved

That's it. Since you want to save as CSV you will have to repeat this
procedure at save time and the format procedure at opening...
Or you could have two copies, one in ODS properly formatted and a copy in
CSV which you exported following the 5 previous steps after adding new data.

Hope this helps

[berntie]

Pedro wrote

What you need to do is

1) You CAN'T use the Save button
2) You must always Save As (although you can overwrite your csv file)
3) In the Save As dialog, always check the bottom box "Edit filter
settings"
4) On the next dialog choose "Keep current format" (meaning save as CSV)
5) On the Export Text File dialog, make sure you UNCHECK the "Save cell
content as shown" option (this tells Calc not to save long date as the
text you see on screen but save the value) and CHECK the "Quote all text
cells" option (just to make sure all text is properly saved

Works like a charm.

Pedro wrote

That's it. Since you want to save as CSV you will have to repeat this
procedure at save time and the format procedure at opening... I can live
very well with that.

Pedro wrote

Hope this helps It certainly does. Thank you very much.

Oh, and by the way, can you confirm that data validation does not work with
CSV files?

[Pedro_Lino]

berntie wrote

Oh, and by the way, can you confirm that data validation does not work
with CSV files?

I'm glad I could help

What do you mean? Data validation is performed by Calc, it is not affected
by the file format.

Can you explain what you did, what happened and what you expect to happen?

[berntie]

Pedro wrote

berntie wrote

Oh, and by the way, can you confirm that data validation does not work
with CSV files?

I'm glad I could help

What do you mean? Data validation is performed by Calc, it is not affected
by the file format.

Can you explain what you did, what happened and what you expect to happen?

When I open my CSV file, select the date column and go to "Data -> Validity"
and tell Calc to disallow dates before, say, 01.01.2010, then I can
nonetheless enter, e.g., 01.01.2009.

[Pedro_Lino]

berntie wrote

When I open my CSV file, select the date column and go to "Data ->
Validity" and tell Calc to disallow dates before, say, 01.01.2010, then I
can nonetheless enter, e.g., 01.01.2009.

Did you check the "Show error message" option in the Error Alert tab?
When you type anything but a date after 01.01.2010 it should display a
message box saying "Invalid value" and the value or text that you typed is
simply deleted (which is not very nice )

[berntie]

Pedro wrote

berntie wrote

When I open my CSV file, select the date column and go to "Data ->
Validity" and tell Calc to disallow dates before, say, 01.01.2010, then I
can nonetheless enter, e.g., 01.01.2009.

Did you check the "Show error message" option in the Error Alert tab?
Yes, I did check the box.

Pedro wrote

When you type anything but a date after 01.01.2010 it should display a
message box saying "Invalid value" and the value or text that you typed is
simply deleted (which is not very nice ) Maybe it's not nice, but it's
what I want.

But you know what's weird? Now that I did try it again, it worked. Though, I
have absolutely no clue what I did wrong before... Anyway, the problem seems
to be on my side of things, so for me, this issue is closed.

Thanks again for your help.

[planas]

Jay Lozier wrote

* File a bug/feature request? If yes, where can I do that?

File a feature request for a currency data type

Where can I do that?

You will need a free bugzilla account to use this (a link is provided)
https://www.libreoffice.org/get-help/bug/

Jay Lozier wrote

* Work around the issue? What would that workaround be??

Work around appears to be import as standard, then format the column as
desired. Also, once the data is imported save the Calc spreadsheet as
*.ods to preserve formating.

Thanks for the suggestion but saving as *.ods is not an option for me as I
need the resulting file to be a text file.

Ok, ods saves the entire spreadsheet while saving as csv only saves the current worksheet and loses all the formating information.

What are you actually trying to do? It sounds like your trying do something that a database may be better at doing.

[Tom_Davies]

Hi
Csv doesn't store many different options for formatting.  Basically it can only just about cope with text or number.  It's not really a proper spreadsheet format.  The plus side is that even a text-editor can open it and the file-size tends to be very small.

When you import into a spreadsheet program you often get a chance to let the program know which columns need to be defined with whatever greater level of sophistication you need.  The sort of sophistication that proper spreadsheet formats (ods or xls or xlsx) routinely save in with the file.  I think Calc might even remember your choices for the next csv you open or perhaps just for the same file-name but either way it could be hopelessly inappropriate by then if extra columns have been inserted or anything like that.
Regards from
Tom

[berntie]

Jay Lozier wrote

What are you actually trying to do? It sounds like your trying do
something that a database may be better at doing.

I'm just trying to keep some kind of record of my daily expenses. While
there's some hassle involved, LibreOffice provides all the features I need
for that purpose (pivot-tables are most important), so a real RDBMS would be
far too complex for my requirements.

Concerning the file format, CSV files have the huge advantage of being
* a single file,
* small,
* and plain text files.

(Tom has already pointed out some of that.) Especially the last point is
important, since you can check text files into version control and diff
them. This would require far more efforts with a binary format like ODS
(always (de-)compressing might me scriptable, but that's too cumbersome for
me).

So much for my motivation.

Cheers
berntie

[Hladuvka_Jiri]

Dne 28.12.2011 18:28, berntie napsal(a):

Jay Lozier wrote

What are you actually trying to do? It sounds like your trying do
something that a database may be better at doing.

I'm just trying to keep some kind of record of my daily expenses. While
there's some hassle involved, LibreOffice provides all the features I need
for that purpose (pivot-tables are most important), so a real RDBMS would be
far too complex for my requirements.

Concerning the file format, CSV files have the huge advantage of being
* a single file,
* small,
* and plain text files.

...

If you prefer to edit plain text file and process its data in Calc, you have two
other ways:

1) do not import the file. Simply copy the text in which the Tabs are the delimiters
and then "Paste as unformatted text" into the pre-formatted sheet - there is an icon in the toolbar for it.

2) Create an extra sheet (name it e.g. CSV) which is linked to your external regular csv file.
In your main sheet (name it e.g. DATA) and put everywhere formulas (e.g. in DATA.A1 enter =CSV.A1)
Then format cells in DATA as you need.

Define your pivot tables source area big enaugh to cover future hundereds of rows

Regards,
Jiri

[Paul_D_Mirowsky]

I work with csv/txt in Writer as a filter function.

I substitute commas for spaces, insert commas for field separation and add missing column information. It is very fast and have set up recording macros to do it based on the original files Calc import problems. Not pleasant, but works well once understood and set.

It may be best to separate the EUR to a different field/column. Then use some form of "if" statement in Calc to format your numbers in column A based on column B. (This part has not been tested by me.)

It may not be very elegant, but because your data set is consistent, may temporarily answer your problem.

[berntie]

Thanks for all your tips, guys. They are appreciated.

Bernhard

[Tom_Davies]

Hi
It has been an interesting thread.  It's been good to gain an insight into what other people are doing with CSVs.  Also congrats on fixing your issue.
Regards from
Tom