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!

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

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,

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

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

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

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

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

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.

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

please run this to verify something...

this is what i got...

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

i dont understand what does that mean

okay, now please run the same query on your materials type table

+----------+----------+
| imTypeID | COUNT(*) |
+----------+----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
+---------+-----------+

this is what i got...