The LOAD DATA INFILE command allows you to bulk insert data from a text file into a single table without having to write INSERT statements. For example, we could have loaded the data in the department table using this technique as follows . Listing 5.2 shows the contents of a data file containing department information. Listing 5.2 department_infile.txt42 Finance 128 Research and Development NULL Human Resources NULL Marketing This file is in the default LOAD DATA INFILE format, with each row listed on a separate line with tabs between column values. (This is configurable and we will see how in a minute.) We can load this information into the department table with the following LOAD DATA INFILE statement: load data local infile 'department_infile.txt' into table department; This facility is particularly useful for converting data from another database format, spreadsheet, or CSV (comma-separated values) file. The LOAD DATA INFILE statement requires the FILE privilege ”see Chapter 11, "Managing User Privileges," for further information, especially if you are having trouble running this command. The privilege to perform this is often restricted for good security reasons ”to stop people from loading in /etc/passwd, for example. The general form of the LOAD DATA INFILE statement is as follows: LOAD DATA [LOW_PRIORITY CONCURRENT] [LOCAL] INFILE ' fileName.txt ' [REPLACE IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\' ] ] [LINES TERMINATED BY '\n'] [IGNORE number LINES] [( col_name ,...)] The optional clauses are as listed here:
A common format to receive data in is CSV or comma-separated values. Many programs can read and write files of this type, but one notable example is Microsoft Excel. Listing 5.3 shows a small CSV file saved from Excel. Listing 5.3 new_programmers.csvName,Job,DepartmentID Julia Lenin,Programmer,128 Douglas Smith,Programmer,128 Tim O'Leary,Programmer,128 We can load this data into the employee table with the following query: load data infile 'e:\new_programmers.csv' into table employee fields terminated by ',' lines terminated by '\n' ignore 2 lines (name, job, departmentID); You can see that we have used more options to load this data than we did when the data was in the default format. A few points are worth noting:
|