OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Convert date field to text for csv export

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
boscopup
Newbie
Newbie


Joined: 30 May 2005
Posts: 4

PostPosted: Mon May 30, 2005 5:22 pm    Post subject: Convert date field to text for csv export Reply with quote

I'm having a problem exporting a spreadsheet to csv. The file contains two date columns that need exporting. One is in the format YYYY-MM-DD for import into a MySQL date field. The other is just the year portion, which I created from the previous date column. When exporting as csv, it exports the MM/DD/YYYY format instead of what is being shown in the spreadsheet. Is there any way to convert these columns to text fields? If I try to just change them, they show the single number value of the date. Not what I want. I tried creating a second column that does =TEXT(A1, "YYYY-MM-DD"), but then I can't delete the column I got it from, which makes it difficult to export just the text field column. Smile

Is there an easy solution? I'm thinking I may have to export two copies of the date columns, then open the csv, delete the extra columns, then save again... ?
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon May 30, 2005 6:44 pm    Post subject: Re: Convert date field to text for csv export Reply with quote

boscopup wrote:
I tried creating a second column that does =TEXT(A1, "YYYY-MM-DD"), but then I can't delete the column I got it from, which makes it difficult to export just the text field column. Smile


I didn't try the year only column, but for the above, when saved as a CSV then re-loaded into Calc, I find that column A [the original date] is changed; column B with your formula [with a semi-colon instead of a comma] is brought in as a text item. Then column A can be deleted, leaving only the text column B.

David.
Back to top
View user's profile Send private message
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Mon May 30, 2005 9:53 pm    Post subject: Re: Convert date field to text for csv export Reply with quote

boscopup wrote:
... I tried creating a second column that does =TEXT(A1, "YYYY-MM-DD"), but then I can't delete the column I got it from, which makes it difficult to export just the text field column....


You can replace formulas in second column with text. Use Copy and then Edit > Paste Special > Strings. Be sure to deselect Formulas! Then export data as csv.

Hope this helps!
Uros
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Mon May 30, 2005 9:56 pm    Post subject: Reply with quote

Format of CSV exported dates depend on locale language settings.
Change it to Swedish to get an date exported as yyyy-mm-dd
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
boscopup
Newbie
Newbie


Joined: 30 May 2005
Posts: 4

PostPosted: Tue May 31, 2005 5:07 am    Post subject: Got it working Reply with quote

Thanks all! I got it working by doing the TEXT formula in a separate column for both date columns (formatting one "YYYY-MM-DD" and one "YYYY"), then saving as csv, then importing back into OOo, making those columns "text" during import, then deleting the original date columns and saving as csv again. Kind of a pain, but it works.
Back to top
View user's profile Send private message
UtahMonogamist
Newbie
Newbie


Joined: 31 May 2005
Posts: 1

PostPosted: Tue May 31, 2005 7:39 am    Post subject: Re: Convert date field to text for csv export Reply with quote

Yet another solution: properly exporting MySQL dates (ISO 8601 in format "YYYY-MM-DD") to CSV but with US settings is a concern for me. I succeeded by exporting the CSV in Western Europe ISO-8859-15/EURO format in the export dialogue.

NOTES:

As suggested by probe1, I decided to install Swedish localization (patched OOo with a Swedish localization file), then tried setting Swedish file format just for the document rather than resetting global localization in Tools/Options/Language Settings/Languages (keeping $USD and other settings as appropriate for me). Whether this played a part in the successful date export or not, I'm not sure (though I'd guess this was actually due to my export filter ISO choice) -- just adding the note in case it helps.

If one is working in a file with an international file localization set, one can format problematic columns for localized use (eg setting column decimal formats with a period rather than in comma notation, setting currency characters, etc). Set column formats in the Cell Format dialog from the spreadsheet file using the preferred localization from the "Language" combo on the right, then export once everything looks good for use.
Back to top
View user's profile Send private message Send e-mail
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Tue May 31, 2005 2:10 pm    Post subject: Re: Convert date field to text for csv export Reply with quote

UtahMonogamist wrote:

As suggested by probe1, I decided to install Swedish localization

Stop! Never talked of an installation needed....
I was talking of a temporary change of a setting in Tools>Options>Language Settings>Language>AreaScheme, setting this to swedish results in the date format YYYY-MM-DD on CSV export.
I didn't cross check decimal separators or else (and this wasn't mentioned in the OP).

UtahMonogamist wrote:
I succeeded by exporting the CSV in Western Europe ISO-8859-15/EURO format in the export dialogue.

this doesn't work for me with AreaScheme "Default": export is done in german date format dd.mm.yyyy

Are we all talking of version 1.1.4?

How about some MySQL date field insert options? Convert functions?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
theking2
Newbie
Newbie


Joined: 12 Apr 2006
Posts: 2

PostPosted: Wed Apr 12, 2006 9:15 am    Post subject: Re: Convert date field to text for csv export Reply with quote

probe1 wrote:


I was talking of a temporary change of a setting in Tools>Options>Language Settings>Language>AreaScheme, setting this to swedish results in the date format YYYY-MM-DD on CSV export.
I didn't cross check decimal separators or else (and this wasn't mentioned in the OP).
?


It's interesting that there is an excellent ISO standard for date and times that even US citizens might welcome ISO 8601. I'm surprised that such a clearly defined standard is not included in the settings when saving CSV files.
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group