Mysql LOAD DATA INFILE - input data unexpectedly truncated
I'm trying to load data into a table (obviously?). My table looks like this:
CREATE TABLE IF NOT EXISTS `condensed` ( `id` bigint(20) NOT NULL, `src` enum('C_X','C_AH','C_AO','C_B','H_X','H_AH','H_AO','H_B') NOT NULL, `hash` int(11) default NULL, `ihash` int(11) default NULL, `last_updated` datetime default NULL, PRIMARY KEY (`id`,`src`), UNIQUE KEY `covering` (`id`,`src`,`hash`) ) ENGINE=MyISAM DEFAULT CHARSET=ascii;
I've got data files with look like this:
320115816,'C_X',692983698,854142703,20120216220954 320124536,'C_X',588472049,1059436251,20100527232845 320120196,'C_X',452117509,855369958,20101118105505 ...
But when I load it using
LOAD DATA INFILE '/path/to/data.csv' IGNORE INTO TABLE `condensed` (id, src, hash, ihash, last_updated);
it only loads the first two columns (hash, ihash and last_updated are null).
320115816,'C_X',NULL,NULL,NULL 320124536,'C_X',NULL,NULL,NULL 320120196,'C_X',NULL,NULL,NULL ...
I do get a lot of warnings (presumably because mysql is discarding the 3 columns from the input set and assigning defaults)
Query OK, 20 rows affected, 100 warnings (0.00 sec) Records: 20 Deleted: 0 Skipped: 0 Warnings: 100
(I intend to load several milion records - not just 20)
I get the same problem using mysqlimport.
Omitting an explicit field list from the LOAD DATA statement (same fields and order in database as in files) resulted in the same outcome.
MySQL version is 5.0.22, there are no non-printable characters in the input file.
Just add some improvement to Thilo's answer if you are using Windows.
LOAD DATA INFILE '/path/to/data.csv' IGNORE INTO TABLE `condensed` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\'' LINES TERMINATED BY '\r\n' --Windows right line terminator (id, src, hash, ihash, last_updated)
It worked for me. It solved all my truncating problems on Windows. Also have a look at this : http://forums.mysql.com/read.php?79,76131,76871
Never managed to resolve this. I ended up writing a wee php script to map the data into the db.
It's worth noting that, if the field MySQL is complaining about happens to be the final one in the table, there's a chance that you need to fix the FIELDS TERMINATED BY. On Windows I had to tell it \n instead of \r\n.
I was having similar problems with a CSV file created on an IBM mainframe that was moved to a Windows file server before being loaded. I was getting a truncation warning on all rows except the last. Mainframe file looked okay. Adding '\r\n' cleared the problem.
I think the quotes around only the enum field are confusing the import. Try this:
LOAD DATA INFILE '/path/to/data.csv' IGNORE INTO TABLE `condensed` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\'' (id, src, hash, ihash, last_updated)