Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    12

    Unanswered: Help with Load Data Infile

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    12
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sysenm View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2010
    Posts
    12
    Quote Originally Posted by r937 View Post
    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,

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2010
    Posts
    12
    Thank you r937 !

    The file is attached. BTW, I am working on a windows machine.
    Attached Files Attached Files
    • File Type: txt s.txt (408 Bytes, 39 views)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2010
    Posts
    12
    Thank you very much.

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

Posting Permissions

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