Help with Load Data Infile

sysenm · #1 (**permalink**) 11-21-10, 17:19

Hi all,

I am trying to load a data file into a table. The table structure is as follows:
____________________
Col1 | col2 | col3 |
-----------------------
Col1 is auto incremented, primary key

My query is
LOAD DATA INFILE 'c:\\s.txt' INTO TABLE my_table (col2,col3);

I get a 1261 error (row 1 does not contain data for all columns) . Any guideline what i am doing wrong here.

Thank you!

r937 · #2 (**permalink**) 11-21-10, 17:33

could we have a look at the first few lines of data in the s.txt file, please

i think you may have forgotten to specify how the fields are separated

the default is a tab

sysenm · #3 (**permalink**) 11-21-10, 17:47

Thanks for quick reply, The s.txt is like following.

xxxContext601CBR 0.811
xxxContext602CBR 0.813
xxxContext603CBR 0.800
xxxContext604CBR 0.877
xxxContext605CBR 0.867

Its tab. I have also tried using different seperators and specifying that with "Fields terminated by" and "lines terminated by" but its not working.

r937 · #4 (**permalink**) 11-21-10, 17:54

Quote:

Originally Posted by sysenm

Its tab.

hmm, well, you must've converted your tabs to spaces when pasting here, because i don't see no tabs in those records

also, "its not working" is not enough information to go on

sysenm · #5 (**permalink**) 11-21-10, 18:01

Quote:

Originally Posted by r937

hmm, well, you must've converted your tabs to spaces when pasting here, because i don't see no tabs in those records

also, "its not working" is not enough information to go on

I am using the defaults (tab for field separation and end-of-line for line separation. I am getting the same error "row 1 does not contain data for all columns" over and over,

r937 · #6 (**permalink**) 11-21-10, 18:05

okay, here's an idea

grab the first dozen rows or so of your data, save as a text file, attach it to your post, and i'll test it here to see if i can get it working

sysenm · #7 (**permalink**) 11-21-10, 18:12

Thank you r937 !

The file is attached. BTW, I am working on a windows machine.

r937 · #8 (**permalink**) 11-21-10, 18:32

well, it worked fine for me

i created a test table --

Code:

CREATE TABLE sysenm
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, foo VARCHAR(99)
, bar DECIMAL(5.4)
);

here's how i loaded it --

Code:

LOAD DATA INFILE 'C:/Documents and Settings/aldebaran.winxp/Desktop/s.txt'
INTO TABLE sysenm
FIELDS TERMINATED BY '\t'
( foo, bar )

and to prove i loaded it, i ran this query --

Code:

SELECT LEFT(foo,13) as qux
     , COUNT(*) AS rows
     , AVG(bar) AS avg_bar
     , MAX(bar) AS max_bar
  FROM sysenm
GROUP 
    BY qux

which produced these results --

Code:

qux          rows  avg_bar    max_bar
xxxContext601  4  0.83075000  0.8550
xxxContext602  4  0.85075000  0.8660
xxxContext603  3  0.84566667  0.8810
xxxContext604  3  0.86200000  0.8770
xxxContext605  3  0.83766667  0.8670

sysenm · #9 (**permalink**) 11-21-10, 18:49

Thank you very much.

I 'll check my table defination, thats the only thing left to check now. Thanks again

Internet Services

Web Development

Digital Marketing

Consumer Tech