LOAD DATA INFILE syntax

Databases
1

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:


LOAD DATA INFILE
   'D:/Inetpub/wwwroot/_intalm.xls'
      REPLACE INTO TABLE _intalm
      FIELDS TERMINATED BY ';' 
      OPTIONALLY ENCLOSED BY '\\"' LINES TERMINATED BY '\\r\
'
      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

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:


/*
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

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

3

Thanks Sir. Are looking help.

I try this code:

LOAD DATA INFILE
   'D:/Inetpub/wwwroot/_intalm.xls'
      REPLACE INTO TABLE _intalm
      FIELDS TERMINATED BY '\	' 
      OPTIONALLY ENCLOSED BY '\\"' LINES TERMINATED BY '\	'
      IGNORE 12 LINES ;

New error:
Row 3 doesn’t contain data for all columns

4

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
’ ?

5

Thanks Sir.

Really I don’t see anything evident strange about the xls file… :shifty:
I modified the code with:

LOAD DATA INFILE
   'D:/Inetpub/wwwroot/_intalm.xls'
      REPLACE INTO TABLE _intalm
      FIELDS TERMINATED BY '\	' 
      OPTIONALLY ENCLOSED BY '\\"' LINES TERMINATED BY '\\r\
'
      IGNORE 12 LINES ;

     Affected rows: 0
     Time: 0.000ms

You can share the excel file?

6

do you mean, can i look at it, if you posted it? sure :slight_smile:

7

Thanks Sir.

I have good news :slight_smile: this code is working:

[SQL] 

LOAD DATA INFILE
   'D:/Inetpub/wwwroot/_intalm.csv'
      IGNORE INTO TABLE _intalm
      FIELDS TERMINATED BY ';' 
      OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\\r\
'
      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… :sick:

Any idea?