Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: LOAD DATA LOCAL Timestamps

    Hi all,

    I am using:
    LOAD DATA LOCAL INFILE '/tmp/data2.txt' INTO TABLE dataTable FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

    to upload delimited data to my database. Unfortunately though it uses the default timestamp of 0000-00-00 00:00:00 for my Timestamp data and not what is in the file. How can I get it to upload the Timestamp data from the file?

    The structure is as follows:
    TIMESTAMP|CONVERSION_ID|CLIENT_ID|Event TIMESTAMP

    Many Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    depends what you mean by timestamp

    i would load the data into a "holding" table which has columns that match the exact format of the data

    then once it's loaded, i would use INSERT SELECT to copy and transform the data into its final table

    in the SELECT part of the INSERT SELECT, i would use the STR_TO_DATE function to convert the datetime value, or else FROM_UNIXTIME if by "timestamp" you meant a unix epoch integer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    Hi,

    Its in the format: 20111010 23:55:38 in the data file.
    While the database stores dates in the format of: 2011-09-13 15:05:07
    a holding table would be impractical because this is something that will have to be done daily by some really untech savvy work colleagues.

    Thank you,

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry to hear that it will be impractical

    by the way, mysql does not store dates in the format of: 2011-09-13 15:05:07

    that is indeed one of the formats that allows you to enter datetime values into mysql, but the internal stored format of datetime values is completely different
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have a look at using LOAD DATA with variables. I have created a tutorial explaining something pretty much what you are looking to achieve here. It loads the data to a variable and then you perform an operation on the variable before storing in the table column.

    MySQL – LOAD DATA INFILE variable data IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice tutorial, very clearly written

    i did not know about SET in LOAD DATA :blush:

    in this case, there is no IF or variable required, just use STR_TO_DATE with a format that acknowledges that the year-month-day separators are missing

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2011
    Posts
    15

    Red face

    hi

    very good tutorial, but now what if i want to load data such in the table below and imTypeID is a foreign key which represent learning materials type such
    1. Tutorial
    2. Exercise
    3. Case Study

    i tried to load the data and it returns error that it cant load foreign key, when i remove the imTypeID column, data is loaded. is there is a way to resolve this problem. your advice is highly appreciated.

    +-----+-----------------+-------------+---------------+-------+------------+
    | imID | imTitle | imCreator | imDate | imURL | imTypeID |
    +-----+-----------------+-------------+---------------+-------+------------+
    | 1 | Arrays1 | Tom Thumb | 2011-08-21 | www | 1 |
    | 2 | Arrays2 | Tiny Tim | 2008-01-23 | www | 2 |
    | 3 | Arrays3 | Donald Duck | 2011-05-28 | www | 3 |
    +-----+-----------------+-------------+---------------+-------+------------+

    thanks
    regards
    Last edited by zbath81; 11-05-11 at 00:04.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zbath81 View Post
    i tried to load the data and it returns error that it cant load foreign key
    make sure the table being referenced by the imTypeID column actually has rows with those values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2011
    Posts
    15
    Quote Originally Posted by r937 View Post
    make sure the table being referenced by the imTypeID column actually has rows with those values
    do you mean that the foreign key column has entries?

    yes it has and still i get error, i tried to switch off foreign key constraint checking before loading the data and then switching it back on again after loading the data, as shown in the syntax below.....

    SET FOREIGN_KEY_CHECKS = 0;
    LOAD DATA LOCAL INFILE 'C:\localhost\test.csv'
    INTO TABLE instructionalMaterials
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    (imID,imTitle,imCreator,imDate,imURL,imTypeID);

    SET FOREIGN_KEY_CHECKS = 1;

    the data was loaded but the foreign key columns returns all 0 values.

    any advice...

    best regards
    zellal

  10. #10
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have a few questions about this:

    a) Does you test.csv have entries for the field imTypeID?
    b) Does the field imTypeID have a default which is 0?
    c) Can you provide sample script so that this can be tested?

    I think that the issue may be you have a default value of 0 which of course needs to have a corresponding entry in the parent table. It also suggests that the field imTypeID is not being populated due to it not existing in the csv file.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  11. #11
    Join Date
    Nov 2011
    Posts
    15
    this is the parent table for materials type

    +----------+------------+
    | imTypeID | imType |
    +----------+-------------
    | 1 | Tutorial |
    | 2 | Exercise |
    | 3 | Case Study |
    +---------+-------------+

    and yes the field imTypeID in the test.csv file has entries
    and this is the syntax i use to load the data

    LOAD DATA LOCAL INFILE 'C:\localhost\test.csv'
    INTO TABLE instructionalMaterials
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    (imID,imTitle,imCreator,imDate,imURL,imTypeID);

    attached below a screenshot of my entries file
    Attached Thumbnails Attached Thumbnails Untitled.jpg  

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please run this to verify something...
    Code:
    SELECT imTypeID
         , COUNT(*)
      FROM instructionalMaterials
    GROUP
        BY imTypeID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2011
    Posts
    15
    this is what i got...

    +----------+----------+
    | imTypeID | COUNT(*) |
    +----------+----------+
    | 1 | 16 |
    | 2 | 8 |
    +---------+-----------+

    i dont understand what does that mean

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, now please run the same query on your materials type table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2011
    Posts
    15
    +----------+----------+
    | imTypeID | COUNT(*) |
    +----------+----------+
    | 1 | 1 |
    | 2 | 1 |
    | 3 | 1 |
    | 4 | 1 |
    +---------+-----------+

    this is what i got...

Posting Permissions

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