SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    xls -> csv date format problem

    I have an excel sheet with date columns in the format yyyy-mm-dd

    When I export this file to csv format the date format (with excel or openoffice) has been changed to mm/dd/yyyy

    Aside from the fact that mm/dd/yyyy doesn't make any sense to me, is there a way I can prevent excel or openoffice from converting the dates from yyyy-mm-dd to mm/dd/yyyy when I export to csv format?

    I need to import the csv file into a mysql database.

  2. #2
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I couldn't find any options in excel or openoffice to preserve the date format when converting to csv.

    However I did already solve my problem by copying the dates from the excel sheet to notepad and then from notepad to the csv file.

  3. #3
    SitePoint Zealot JEmLAC's Avatar
    Join Date
    Apr 2002
    Location
    Kansas City
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: xls -> csv date format problem

    Originally posted by jamesbond
    I have an excel sheet with date columns in the format yyyy-mm-dd

    When I export this file to csv format the date format (with excel or openoffice) has been changed to mm/dd/yyyy

    Aside from the fact that mm/dd/yyyy doesn't make any sense to me, is there a way I can prevent excel or openoffice from converting the dates from yyyy-mm-dd to mm/dd/yyyy when I export to csv format?

    I need to import the csv file into a mysql database.
    I created a simple two-column, four-row Excel 97 spreadsheet that looked like
    Code:
    One	2002-08-27
    Two	2002-08-28
    Three	2002-08-29
    Four	2002-08-30
    Five	2002-08-31
    The date field I set the format to "yyyy-mm-dd" by right clicking the range, selecting format, selecting custom and entering the format in the field. When I did a save as to CSV, this was the result:
    Code:
    One,2002-08-27
    Two,2002-08-28
    Three,2002-08-29
    Four,2002-08-30
    Five,2002-08-31
    The date format was preserved, so if you're setting the date format of the field, I'm not sure where you're problem lies.
    Morning person by habit, not by nature.

  4. #4
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The date format was preserved, so if you're setting the date format of the field, I'm not sure where you're problem lies.
    Neither do I

    The date format was set to YYYY-MM-DD.

    I didn't try it with Excel 97, I only tried it with Excel 2000 and the spreadsheet software in Openoffice.

    Maybe Excel 97 behaves differently, or it might have to do with settings in Windows.

    A quick search in google showed I am not the only one who encountered this 'issue'.

  5. #5
    SitePoint Zealot JEmLAC's Avatar
    Join Date
    Apr 2002
    Location
    Kansas City
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh, could be version-specific. I haven't tried Open Office yet, although a friend did download it for me. I'm going to try it on my Linux box. I've heard conflicting things about how the windows version co-exists with MS Office and I'd rather not take the chance of messing something up. What's your experience been?

    If Open Office is having an issue with this, it might be worth submitting a bug report at openoffice.org. Just a thot.
    Morning person by habit, not by nature.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •