ProblemYour datafile contains columns that should be ignored rather than loaded into the table. SolutionThat's not a problem if the columns are at the ends of the input lines. Otherwise, you can use a column list with LOAD DATA that assigns the columns to be ignored to a dummy user-defined variable. DiscussionExtra columns that occur at the end of input lines are easy to handle. If a line contains more columns than are in the table, LOAD DATA just ignores them (although it might produce a nonzero warning count). Skipping columns in the middle of lines is a bit more involved. Suppose that you want to load information from a Unix password file /etc/passwd, which contains lines in the following format: account:password:UID:GID:GECOS:directory:shell Suppose also that you don't want to bother loading the password column. A table to hold the information in the other columns looks like this: CREATE TABLE passwd ( account CHAR(8), # login name uid INT, # user ID gid INT, # group ID gecos CHAR(60), # name, phone, office, etc. directory CHAR(60), # home directory shell CHAR(60) # command interpreter ); To load the file, we need to specify that the column delimiter is a colon, which is easily handled with a FIELDS clause: FIELDS TERMINATED BY ':' However, we must also tell LOAD DATA to skip the second field that contains the password. To do this, add a column list in the statement. The list should include the name of each column to be loaded into the table, and a dummy user-defined variable for any column to be ignored: mysql> LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE passwd -> FIELDS TERMINATED BY ':' -> (account,@dummy,uid,gid,gecos,directory,shell); The corresponding mysqlimport command should include a --columns option: % mysqlimport --local \ --columns="account,@dummy,uid,gid,gecos,directory,shell" \ --fields-terminated-by=":" cookbook /etc/passwd See AlsoAnother approach to ignoring columns is to preprocess the input file to remove columns. Section 10.19 discusses a utility that can pull out and display data-file columns in any order. |