PHP

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","","dimitria@lucaston.com",
"1969-08-11","35000"

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
           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
           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.