SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    ny
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    From a CSV file into Mysql - How to transfer dates?

    I remember from a Sitepoint book about the
    advantage of formatting dates as 10-digit integers
    to render PHP, Mysql and javascript go easier.

    I acted accordingly.

    Now, however, I need help:
    How do I get dates from a CSV file
    into their corresponding Mysql integer-formatted fields;
    What form must these CSV data take?

    Thank you!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    there are three ways to accomplish this:

    first is to load it in to excel or whatever and format the column as yyyy-mm-dd h:mm:ss, then save.

    second is to create a staging table in the database where this column is varchar. load the CSV file in to that table, then use INSERT ... SELECT to move the rows to the correct table, and use date formatting or string manipulation functions to get the dates in there correctly.

    third option is just like the second, but instead of a staging table, use LOAD DATE INFILE to reformat the dates. here's an example from the mysql manual:
    Code:
    LOAD DATA INFILE 'file.txt'
      INTO TABLE t1
      (column1, @var1)
      SET column2 = @var1/100;
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    ny
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you longneck.
    Not quite sure what you said.

    Here's how I used to do the inserts:

    I'd get the right values from a csv (Excel) file.
    I do that by opening that CSV file with a text editor (TextWrangler),
    take the headings (or titles of columns) and
    incorporate them (with a "replace", or simple edits) into the
    INSERT clause,
    then take the rest of the file data, and
    (again with replace and edits) trasform
    the data to fit into as many VALUES clauses as there are rows.
    That's how I construct my entire sql INSERT statement.

    It's perhaps longer than the LOAD DATA way
    but the latter never seemed to work for me.

    But now that I need to fill a Mysql (10-digit) integer field
    with a date, that shows up in the CSV file as a string that looks like
    this: "2008-12-23" I ran into problems because the wrong
    value shows up in the table.

    I hope you follow me.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ah, i completely missed the part about 10 digit integers, a.k.a., unix timestamps.

    i must disagree with the recommendation that unix timestamps are easier to deal with. considering the ease with which mysql DATE, TIME and DATETIME values can be converted to/from unix timestamps using the from_unixtime() and unix_timestamp() formats, and the added benefit of being able to easily process the mysql native DATE, TIME and DATETIME types, storing unix timestamps natively yields very little benefit.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •