LOAD DATA INFILE select columns

I have a text file that has all it's columns separated by \t (tab). There are 34 columns in total, but I only need to read and insert the first two columns, namely ID and name. The table structure is as follows:

CREATE TABLE temp.itemname ( id SMALLINT(5) NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY (id) ) ENGI开发者_StackOverflow社区NE = InnoDB;

What query could I use to select and insert only first two columns and cut to the next line after that?

load data infile 'itemname.txt' into table itemname

throws error 1366: Incorrect integer value: '17' for column 'id' at row 1

and I can't get past that. Until I figure this out I can't do anything... Also, the file is 18094 lines long, so a manual insert would be exhausting to say the least, and I have other similar files too.

In the load command try listing the fields into which you are importing and the rest of the line will be ignored:

LOAD DATA INFILE 'itemname.txt' INTO TABLE itemname FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (id,name);

The terminators (\t and \n) are not strictly necessary in this case, since these are the defaults. If your input uses different terminators you need to specify these in the command as well.

You can specify the columns of the tables you want the data to be inserted in.

Here is the syntax of the command:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

For more information check the docs.