Importing and Exporting Data - MySQL - page 380

Specifying input column order
LOAD DATA assumes that columns in the datafile have the same order as the columns in
the table. If that's not true, specify a list to indicate the table columns into which to load
the datafile columns. Suppose that your table has columns a , b , and c , but successive
columns in the datafile correspond to columns b , c , and a . Load the file like this:
mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl (b,c,a);
mysqlimport has a corresponding --columns option to specify the column list:
% mysqlimport --local --columns=b,c,a cookbook mytbl.txt
Preprocessing input values before inserting them
LOAD DATA can perform limited preprocessing of input values before inserting them,
which sometimes enables you to map input data onto more appropriate values before
loading them into your table. This is useful when values are not in a format suitable for
loading into a table (for example, they are in the wrong units, or two input fields must
be combined and inserted into a single column).
The previous section shows how to specify a column list for LOAD DATA to indicate how
input fields correspond to table columns. The column list also can name user-defined
variables, such that for each input record, the input fields are assigned to the variables.
You can then perform calculations with those variables before inserting the result into
the table. Specify these calculations in a SET clause that names one or more col_name =
expr assignments, separated by commas.
Suppose that a datafile has the following columns, with the first line providing column
labels:
Date Time Name Weight State
2006-09-01 12:00:00 Bill Wills 200 Nevada
2006-09-02 09:00:00 Jeff Deft 150 Oklahoma
2006-09-04 03:00:00 Bob Hobbs 225 Utah
2006-09-07 08:00:00 Hank Banks 175 Texas
Suppose also that the file is to be loaded into a table that has these columns:
CREATE TABLE t
(
dt DATETIME ,
last_name CHAR ( 10 ),
first_name CHAR ( 10 ),
weight_kg FLOAT ,
st_abbrev CHAR ( 2 )
);
To import the file, you must address several mismatches between its fields and the table
columns: