SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    LOAD DATA INFILE syntax

    Hi all.

    I need import in mysql database the excel file with LOAD DATA INFILE syntax:
    http://dev.mysql.com/doc/refman/5.0/en/load-data.html

    I try in query mysql browser this code:
    Code:
    LOAD DATA INFILE
       'D:/Inetpub/wwwroot/_intalm.xls'
          REPLACE INTO TABLE _intalm
          FIELDS TERMINATED BY ';' 
          OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'
          IGNORE 11 LINES ;
    But the response is query returned no resultset and the TABLE _intalm is empty.

    Sir, Can You Help Me With This?

    In the import I need ignore the 1-11 lines and the last line (Num.) in the xls file.

    `_intalm.xls`

    Code:
    Ex		
    Fi		
    Pr		
    Co		
    Or		
    		
    [T, B, L]		
    14_12_110		
    14_12_110		
    		
     FROM:		
    ID		Or	St
     438		M	A
     456		M	C
     464		T	C
     460		T	A
     462		M	C
     Num.
    `_intalm` table:
    Code:
    /*
    Navicat MySQL Data Transfer
    
    Source Server         : test
    Source Server Version : 50045
    Source Host           : 1.7.4.1:3306
    Source Database       : test
    
    Target Server Type    : MYSQL
    Target Server Version : 50045
    File Encoding         : 65001
    
    Date: 2011-02-28 14:54:17
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for `_intalm`
    -- ----------------------------
    DROP TABLE IF EXISTS `_intalm`;
    CREATE TABLE `_intalm` (
      `ID` varchar(255) default NULL,
      `Or` varchar(255) default NULL,
      `St` varchar(255) default NULL,
      PRIMARY KEY  (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of _intalm
    -- ----------------------------

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,333
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    from just a cursory review, it would appear that you actually want to skip 12 lines, not 11

    and your fields are not terminated by a semicolon, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    from just a cursory review, it would appear that you actually want to skip 12 lines, not 11

    and your fields are not terminated by a semicolon, either
    Thanks Sir. Are looking help.

    I try this code:

    Code:
    LOAD DATA INFILE
       'D:/Inetpub/wwwroot/_intalm.xls'
          REPLACE INTO TABLE _intalm
          FIELDS TERMINATED BY '\t' 
          OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\t'
          IGNORE 12 LINES ;
    New error:
    Row 3 doesn't contain data for all columns

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,333
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    Row 3 doesn't contain data for all columns
    interesting

    what do you suppose it means?

    maybe there aren't two tabs on that line?

    and why did you change the LINES TERMINATED BY '\r\n' ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    interesting

    what do you suppose it means?

    maybe there aren't two tabs on that line?

    and why did you change the LINES TERMINATED BY '\r\n' ?
    Thanks Sir.

    Really I don't see anything evident strange about the xls file...
    I modified the code with:

    Code:
    LOAD DATA INFILE
       'D:/Inetpub/wwwroot/_intalm.xls'
          REPLACE INTO TABLE _intalm
          FIELDS TERMINATED BY '\t' 
          OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'
          IGNORE 12 LINES ;
    
         Affected rows: 0
         Time: 0.000ms
    You can share the excel file?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,333
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    You can share the excel file?
    do you mean, can i look at it, if you posted it? sure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    do you mean, can i look at it, if you posted it? sure
    Thanks Sir.

    I have good news this code is working:

    Code:
    [SQL] 
    
    LOAD DATA INFILE
       'D:/Inetpub/wwwroot/_intalm.csv'
          IGNORE INTO TABLE _intalm
          FIELDS TERMINATED BY ';' 
          OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
          IGNORE 12 LINES ;
    
          Affected rows: 7
          Time: 0.016ms
    1) Page found http://bugs.mysql.com/bug.php?id=18335;
    2) I need rename xls file in the csv file;
    3) I need change REPLACE with IGNORE in the query.

    Last problem remains:
    I need ignore the last line (Num.) in the xls file, this fails...

    Any idea?


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
  •