| View previous topic :: View next topic |
| Author |
Message |
Dana_Rea
General User
Joined: 01 Nov 2005
Posts: 32
|
 Posted: Mon Jan 30, 2006 6:03 am Post subject: Changing the Internal Date Format |
 |
|
I have some data that I need to export to csv with a specific date format. Dates in Calc always seem to fall back to dd/mm/yy no matter what date values you enter into a cell, the formatting options you have choosen or Mandrakes Locale date setting.
How can I force Calc to store dates as YYYY-MM-DD (ISO)? |
|
| Back to top |
|
 |
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2478
Location: Chonburi Thailand Asia
|
| Posted: Mon Jan 30, 2006 6:57 am Post subject: |
|
|
Format of CSV exported dates depend on locale language settings.
Change it to Swedish to get an date exported as yyyy-mm-dd
Or change the column from a date (and it's representation) to a text column.
Does this help?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
Dana_Rea
General User
Joined: 01 Nov 2005
Posts: 32
|
| Posted: Mon Jan 30, 2006 7:11 am Post subject: |
|
|
| probe1 wrote: | Format of CSV exported dates depend on locale language settings.
Change it to Swedish to get an date exported as yyyy-mm-dd
Or change the column from a date (and it's representation) to a text column.
Does this help? |
That is along the lines of what I'm thinking. Do you mean chaning to swedish by using:
| Code: |
export LC_TIME=en_SE
|
I don't seem to have the locale file for Sweden though, I guess I'd have to find a copy and compile it.
For the second point, when I override a date to text like:
'2005-01-01
It converts it to the numeric date value for some reason, i.e. 36422
I have around 30,000 dates to work with, so I guess i'd have to write some kind of macro to convert them all. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Jan 30, 2006 7:52 am Post subject: |
|
|
Have a look at Tools > Options > Lang
Ther are 3 lang-selections
1) UI (normally just the installed lang version),
2)Next level refers to the system-locale to be used (eg. which is the decimal separator when interpreting input),
3) is the default lang to be used for output when a part of the doc has no lang set explicitly.(having an option "This doc only")
4th level is within document, when setting the lang of some element explicitly.
I think you have to set the second lang.
No, I just tested with the default style of a new doc set to swedish. (Press F11 and find the default style of this doc or set the third tools-option to swedish).
Now the number 12.4 has a comma as decimal separator, which may be not what you want: 2006-01-12,"12,4"
Since you are using some kind of non-windows, you should use some combination GNU-tool (sort, wc, grep)or script-lang(perl,sed) to change one or more csv on the fly. Calc is not very flexible here. |
|
| Back to top |
|
|
Dana_Rea
General User
Joined: 01 Nov 2005
Posts: 32
|
| Posted: Mon Jan 30, 2006 8:29 am Post subject: |
|
|
| Thanks for the input, I have some csv processing code available so I can use that. I can also try to change the constraint on the date format that I will have to use. |
|
| Back to top |
|
|
Dana_Rea
General User
Joined: 01 Nov 2005
Posts: 32
|
| Posted: Mon Jan 30, 2006 10:00 am Post subject: |
|
|
Arg, as usual there is a simple solution to this problem.
1. Make the date look the way you want it.
2. Copy it.
3. Paste it into a text editor and save.
4. Voila.
I love it when 3 hour problems have 1 minute solutions  |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
Dana_Rea
General User
Joined: 01 Nov 2005
Posts: 32
|
| Posted: Mon Jan 30, 2006 11:14 am Post subject: |
|
|
Well, I guess it depends how the editor handles the clipboard.
I've just been using KWrite, which seems to work ok when I want to permanently evaluate equations by cutting and pasting. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Jan 30, 2006 11:30 am Post subject: |
|
|
| When I yank a spreadsheet-selection from system-clipboard into emacs, I get a perfect tab-delimited table. |
|
| Back to top |
|
|
|
