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.

Help!

Answers


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)

Need Your Help

Android ImageButton in TableLayout

android tablelayout imagebutton

I need TableLayout and ImageButton in Java code but there is a problem so i am trying to do like that in xml but I am not understanding why the first button is acting like this.

How can I generate large, ranged random numbers in Swift?

swift random floating-point integer long-integer

I'm looking for an efficient method of generating large numbers (that includes floating point types!) in Swift, with arbitrary ranges (which may even be UInt.max or Int.max)