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","","[email protected]", "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.
