Re: LOAD DATA INFILE and Skipping Columns?

[Shantanu Oak]

1) Extra columns that occur at the end of input lines are ignored.

2) Populate the data in a temporary table and then insert the records in the main table.

3) Under Unix, you can use the CUT utility to extract the columns that you want.

% cut -d":" -f0,3- etc/passwd > passwd.txt

The -d option specifies a field delimiter of : and the -f option indicates that you want to cut column one and all columns from the third to the end of the line. The effect is to cut all but the second column. (Run man cut for more info) Then use LOAD DATA to import the resulting passwd.txt file into the passwd table.

mysql> LOAD DATA LOCAL INFILE 'passwd.txt' INOT TABLE passwd FIELDS TERMINATED BY ':';

The corresponding mysqlimport command is:
% mysqlimport --local --fields-terminated-by=":" recipes passwd.txt