LOAD DATA INFILE - Returns empty datetime

[defIEd]

Hello,

I have a table with about 1 million rows. I want to use LOAD DATA INFILE because it's fast. Everything works, except: the datetime (last column of the row) is empty (0000-00-00 00:00:00).

This is the query I used:

LOAD DATA INFILE '/usr/tmpsql/db.sql' INTO TABLE track FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

This is the table structure:

CREATE TABLE `track` (
  `id` int(11) NOT NULL auto_increment,
  `nr` int(11) NOT NULL default '0',
  `ip` int(10) unsigned NOT NULL default '0',
  `ndate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `ip` (`ip`),
  KEY `ndate` (`ndate`),
  KEY `nr` (`nr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

the /usr/tmpsql/db.sql contains all INSERT commands like:

INSERT INTO `track` VALUES (4463027, 35225, 1154087817, '2006-08-24 05:56:57');
INSERT INTO `track` VALUES (4463026, 47519, 1083028739, '2006-08-24 05:56:57');

Anyone knows how to solve this?

[r937]

you would not LOAD that sql file, you would execute it

[defIEd]

you would not LOAD that sql file, you would execute it

Thank you, it's working now

[newbieLeo]

hi ,
i also got the problem

at first i execute the command text as follows

cmd.CommandText = "Load Data Local Infile '" + filePath + "' Into Table testtable1 (date,col1,col2) ;";

but when it load to database table ,the first row of date fields always return 0000-00-00 00:00:00

then i solved the problem with following code

cmd.CommandText = "Load Data Local Infile '" + filePath + "' Into Table testtable1 Lines STARTING BY '##Start##' (date,col1,col2) ;";

Hope it help.
Thanks