SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast defIEd's Avatar
    Join Date
    Feb 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LOAD DATA INFILE - Returns empty datetime

    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:
    HTML Code:
    LOAD DATA INFILE '/usr/tmpsql/db.sql' INTO TABLE track FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
    This is the table structure:
    HTML Code:
    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:

    HTML Code:
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you would not LOAD that sql file, you would execute it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast defIEd's Avatar
    Join Date
    Feb 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you would not LOAD that sql file, you would execute it
    Thank you, it's working now

  4. #4
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •