Importing and Exporting Data - MySQL - page 381

• The file contains separate date and time fields that must be combined into date-
and-time values for insertion into the DATETIME column.
• The file contains a name field, which must be split into separate first and last name
values for insertion into the first_name and last_name columns.
• The file contains a weight in pounds, which must be converted to kilograms for
insertion into the weight_kg column. (1 lb. equals .454 kg.)
• The file contains state names, but the table contains two-letter abbreviations. The
name can be mapped to the abbreviation by performing a lookup in the states
table.
To handle these conversions, skip the first line that contains the column labels, assign
each input column to a user-defined variable, and write a SET clause to perform the
calculations:
mysql> LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t
-> IGNORE 1 LINES
-> (@date,@time,@name,@weight_lb,@state)
-> SET dt = CONCAT(@date,' ',@time),
-> first_name = SUBSTRING_INDEX(@name,' ',1),
-> last_name = SUBSTRING_INDEX(@name,' ',-1),
-> weight_kg = @weight_lb * .454,
-> st_abbrev = (SELECT abbrev FROM states WHERE name = @state);
After the import operation, the table contains these rows:
mysql> SELECT * FROM t;
+---------------------+-----------+------------+-----------+-----------+
| dt | last_name | first_name | weight_kg | st_abbrev |
+---------------------+-----------+------------+-----------+-----------+
| 2006-09-01 12:00:00 | Wills | Bill | 90.8 | NV |
| 2006-09-02 09:00:00 | Deft | Jeff | 68.1 | OK |
| 2006-09-04 03:00:00 | Hobbs | Bob | 102.15 | UT |
| 2006-09-07 08:00:00 | Banks | Hank | 79.45 | TX |
+---------------------+-----------+------------+-----------+-----------+
LOAD DATA can perform data value reformatting, as just shown. Other examples showing
uses for this capability occur elsewhere. (For example, Recipe 11.4 uses it to map NULL
values, and Recipe 12.13 rewrites non-ISO dates to ISO format during data import.)
However, although LOAD DATA can map input values to other values, it cannot outright
reject an input record that is found to contain unsuitable values. To do that, either
preprocess the input file to remove these records or issue a DELETE statement after
loading the file.