Search code examples
mysqlload-data-infile

Mysql - Load Data Infile formatting a date

Edited: I can store the records of a file with only one line with this format.

0,2016-03-31-13:48:16.435,0.705486,1,F2221-222

But when I try to store a file with 2 or more lines i get an error.

0,2016-03-31-13:48:16.435,0.705486,1,F2221-222
1,2016-03-31-13:48:16.435,0.56606096,2,F2221-222

I am using:

load data infile 'flight3021' 
into table signal_times
fields terminated BY ','
(signaltime_id,@var1,value,signal_id,flight_number)
SET time = STR_TO_DATE(@var1,'%Y-%m-%d-%H:%i:%S.%f')

Error:

Cannot add or update a child row: a foreign key constraint fails (`cmsdbdev`.`signal_times`, CONSTRAINT `FK_r2e4qo5suuwrlkr08ryebv14i` FOREIGN KEY (`flight_number`) REFERENCES `flight_data` (`flight_number`))

The strange thing is that if I do normal insertion of this 2 lines it works, so its related with a FK constraint violation error. I think it has to be an issue with the load data infile.

Thanks.

Solution

  • '2016-03-31-13:16:24.861' is loaded as "'2016-03-31-13:16:24.861'", which is why it is not a valid datetime.

    I use a TRIM function to try to work around that.

    For multiple lines, lines terminated by is needed.

    load data infile '/home/jinggang.sjg/data1' 
into table signal_times
fields terminated BY ',' ESCAPED BY ""
lines terminated by '\r\n'
(signaltime_id,@var1,value,signal_id,flight_number)
SET time = STR_TO_DATE(@var1,'%Y-%m-%d-%H:%i:%S.%f');