Loading Data from a File

You can load data from an external file into MySQL using the LOAD DATA INFILE command. The data in your file must be in a structured formatthe default format is one record on each line, with values separated by tabs.

The file shown in Listing 15.1 contains a file that could be loaded into the products table using the following command:

 LOAD DATA INFILE '/home/chris/listing15.1.txt' INTO TABLE products; 

Listing 15.1. Sample Tab-Separated Data File

 BLUE    Blue product    3.0     13.99 RED     Red product     3.5     25.00 GREEN   Green product   4.5     6.50 

Paths

If no path is given for the filename in LOAD DATA INFILE, MySQL looks for it in the MySQL system directory.

The previous command works because the columns in the data file map precisely to the columns in the products table. If you instead needed to specify the columns because the data file values were in a different order or some were omitted, you could do this by giving a list of columns in parentheses at the end of the command.

The following statement is identical to the previous example, using a full insert in the LOAD DATA INFILE statement:

 LOAD DATA INFILE '/home/chris/mysql10/listing15.1.txt' INTO TABLE products (product_code, name, weight, price); 

If your data file is not tab-separated, you must specify the separation method in the LOAD DATA INFILE statement using the TERMINATED BY and ENCLOSED BY keywords.

Supposing that you have a comma-separated data file with each value enclosed in quotes, you would use the following command to indicate the separation:

 LOAD DATA INFILE '/home/chris/mysql10/listing15.1.txt' INTO TABLE products FIELDS TERMINATED BY ',' ENCLOSED BY '"' (product_code, name, weight, price); 

Line Terminators

If your data file does not use the newline character to separate records, you can specify the line terminator using LINES STARTING BY and TERMINATED BY.

Sometimes you will be working with a data file that contains one or more header lines. Often when you extract data from another program, the first line of the output contains the column names used in that program. Listing 15.2 shows an example of a comma-separated data file that includes a line of headings.

Listing 15.2. Comma-Separated Data File with Column Headings

 "ProdCode","ProdName","ProdWeight","ProdPrice" "BLUE","Blue product",3.0,13.99 "RED","Red product",3.5, 25.00 "GREEN","Green product",4.5, 6.50 

To ignore one or more lines at the top of a data file, use the IGNORE keyword followed by the number of lines and the LINES keywordyou must use the plural LINES even if you want to skip only one line. The following statement successfully loads only the data records from Listing 15.2:

 LOAD DATA INFILE '/home/chris/mysql10/products.txt' INTO TABLE products FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES (product_code, name, weight, price)