Skipping Datafile Columns

10.13.1 Problem

Your datafile contains columns that should be ignored rather than loaded into the table.

10.13.2 Solution

That's not a problem if the columns are at the ends of the input lines. Otherwise, you'll need to preprocess the datafile before loading it.

10.13.3 Discussion

Extra 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 (though it may indicate a nonzero warning count).

Skipping columns in the middle of lines is a bit more involved. Suppose you want to load information from a Unix password file /etc/passwd, which contains lines in the following format:


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:

 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:


However, we must also tell LOAD DATA to skip the second field that contains the password. That's a problem, because LOAD DATA always wants to load successive columns from the datafile. You can tell it which table column each datafile column corresponds to, but you can't tell it to skip columns in the file. To deal with this difficulty, we can preprocess the input file into a temporary file that doesn't contain the password value, then load the temporary file. Under Unix, you can use the cut utility to extract the columns that you want, like this:

% 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 information about the cut command.) Then use LOAD DATA to import the resulting passwd.txt file into the passwd table like this:

mysql> LOAD DATA LOCAL INFILE 'passwd.txt' INTO TABLE passwd

The corresponding mysqlimport command is:

% mysqlimport --local --fields-terminated-by=":" cookbook passwd.txt

10.13.4 See Also

cut always displays output columns in the same order they occur in the file, no matter what order you use when you list them with the -f option. (For example, cut -f1,2,3 and cut -f3,2,1 produce the same output.) Recipe 10.20 discusses a utility that can pull out and display columns in any order.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: