Bulk loading into a database

Another data insertion method is to bulk-load data from a formatted ASCII text file. A formatted text file is usually a comma-delimited (also known as a comma-separated) or tab-delimited file, where the values to be inserted are separated by comma or tab characters, respectively.

The statement LOAD DATA INFILE can bulk-load data from a file. This is nonstandard SQL. For example, consider the following customer information that has been exported from a legacy spreadsheet program:

0,"Marzalla","Dimitria","F","Mrs","171 Titshall Cl","","","St
Albans","WA","7608","Australia", "(618)63576028","","",

The data might be saved in the file customer.cdf. Note that the attribute values are in the same order as the attributes in the winestore customer table; most export wizards in spreadsheet software allow data to be reorganized as it is exported. Also, note that the first value is 0 and, because this value will be inserted into the cust_id attribute, the auto_increment feature assigns the next available cust_id value; inserting 0 has the same effect as inserting NULL.

The file can be inserted into the customer table using the statement:

LOAD DATA INFILE 'customer.cdf' INTO TABLE customer
           LINES TERMINATED BY '\n';

If quotation marks form part of an attribute, they must be escaped using backslashes:

"RMB 123, \"The Lofty Heights\""

Spreadsheet software often automatically escapes quotation marks in strings when data is exported.