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:
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.
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.
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
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.
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'
SET FOREIGN_KEY_CHECKS = 1;
the data was loaded but the foreign key columns returns all 0 values.
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.