Cell formatting with CSV files in Calc

classic Classic list List threaded Threaded
18 messages Options
berntie berntie
Reply | Threaded
Open this post in threaded view
|

Cell formatting with CSV files in Calc

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
Jay Lozier Jay Lozier
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

On 12/03/2011 03:05 PM, berntie wrote:

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

> * Or am I using Calc the wrong way?
>
> Any suggestions appreciated...
>
> Thanks, Bernhard
>
> --
> View this message in context: http://nabble.documentfoundation.org/Cell-formatting-with-CSV-files-in-Calc-tp3557689p3557689.html
> Sent from the Users mailing list archive at Nabble.com.
>


--
Jay Lozier
[hidden email]


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Pedro Pedro
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

In reply to this post by berntie
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 berntie
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

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 berntie
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

In reply to this post by Jay Lozier
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 Pedro
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

In reply to this post by berntie
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 berntie
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

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 Pedro
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

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 berntie
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

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 Pedro
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

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 berntie
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

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.
Jay Lozier Jay Lozier
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

In reply to this post by berntie
On 12/03/2011 05:43 PM, berntie wrote:
> 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.
>
> --
> View this message in context: http://nabble.documentfoundation.org/Cell-formatting-with-CSV-files-in-Calc-tp3557689p3558180.html
> Sent from the Users mailing list archive at Nabble.com.
>


--
Jay Lozier
[hidden email]


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

In reply to this post by berntie
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 :)

--- On Sun, 4/12/11, berntie <[hidden email]> wrote:

From: berntie <[hidden email]>
Subject: [libreoffice-users] Re: Cell formatting with CSV files in Calc
To: [hidden email]
Date: Sunday, 4 December, 2011, 1:06


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.


--
View this message in context: http://nabble.documentfoundation.org/Cell-formatting-with-CSV-files-in-Calc-tp3557689p3558338.html
Sent from the Users mailing list archive at Nabble.com.

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

berntie berntie
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

In reply to this post by Jay Lozier
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
"Ing. Jiří Hladůvka" "Ing. Jiří Hladůvka"
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

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

--
Ing. Jiří Hladůvka - REVIDA

http://www.revida.sk
mailto:[hidden email]


--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Paul D. Mirowsky Paul D. Mirowsky
Reply | Threaded
Open this post in threaded view
|

Re: Cell formatting with CSV files in Calc

In reply to this post by berntie
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.


On 12/3/2011 3:05 PM, berntie wrote:

> 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
>
> --
> View this message in context: http://nabble.documentfoundation.org/Cell-formatting-with-CSV-files-in-Calc-tp3557689p3557689.html
> Sent from the Users mailing list archive at Nabble.com.
>

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

berntie berntie
Reply | Threaded
Open this post in threaded view
|

Thanks

In reply to this post by berntie
Thanks for all your tips, guys. They are appreciated.  :-)

Bernhard

Tom Tom
Reply | Threaded
Open this post in threaded view
|

Re: Thanks

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 :)


--- On Fri, 30/12/11, berntie <[hidden email]> wrote:

From: berntie <[hidden email]>
Subject: [libreoffice-users] Thanks
To: [hidden email]
Date: Friday, 30 December, 2011, 13:18

Thanks for all your tips, guys. They are appreciated.  :-)

Bernhard



--
View this message in context: http://nabble.documentfoundation.org/Cell-formatting-with-CSV-files-in-Calc-tp3557689p3620975.html
Sent from the Users mailing list archive at Nabble.com.

--
For unsubscribe instructions e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted