How to Add a Bunch of MySQL Data
If you have a large amount of data to enter in your MySQL database and it’s already in a computer file, you can transfer the data from the existing computer file to your MySQL database.
Because data in a database is organized in rows and columns, the text file being read must indicate where the data for each column begins and ends and where the end of a row is. Here’s how you create that table structure:
Columns: To indicate columns, a specific character separates the data for each column. By default, MySQL looks for a tab character to separate the fields. However, if a tab doesn’t work for your data file, you can choose a different character to separate the fields and tell MySQL that a different character than the tab separates the fields.
Rows: Also by default, the end of a line is expected to be the end of a row — although you can choose a character to indicate the end of a line if you need to. A data file for an Inventory table might look like this:
Rock<TAB>Classic<TAB>Steely Dan<Tab>Aja<Tab>10.99 RockTAB>Pop<TAB>Semisonic<Tab>All About Chemistry<Tab>11.99 Rock<TAB>Classic<TAB>Beatles<TAB>Abbey Road<Tab>9.99
A data file with tabs between the fields is a tab-delimited file. Another common format is a comma-delimited file, where commas separate the fields. If your data is in another file format, you need to convert it into a delimited file.
To convert data in another software’s file format into a delimited file, check the manual for that software or talk to your local expert who understands the data’s current format. Many programs, such as Excel, Access, and Oracle, allow you to output the data into a delimited file.
For a text file, you might be able to convert it to delimited format by using the search-and-replace function of an editor or word processor. For a truly troublesome file, you might need to seek the help of an expert or a more experienced programmer.
You can leave a field blank in the data file by including the field separators with no data between them. If the field is not defined as NOT NULL, the field is blank. If the field is defined as NOT NULL, loading the data file fails and an error message is returned.
If one of the fields is an AUTO_INCREMENT field, such as a SERIAL field, you can leave it blank and MySQL will insert the AUTO_INCREMENT value. For instance, the following data file contains data to be loaded into the Customer table.
,Smith,John,,Austin,TX,88888,,, ,Contrary,Mary,,Garden,ID,99999,,, ,Sprat,Jack,,Pumpkin,NY,11111,,,
This data file is comma delimited. Each row starts with a comma, leaving the first field blank for the customerID field, which is SERIAL. Other fields in the row are also blank and will be blank in the database after the data file is loaded.
The SQL statement that reads data from a text file is LOAD. The basic form of the LOAD statement is
LOAD DATA INFILE "path/datafilename" INTO TABLE tablename
The statement loads data from a text file located on your server. If the filename doesn’t include a path, MySQL looks for the data file in the directory where your table definition file, called tablename.frm, is located.
By default, this file is located in a directory named for your database, such as a directory named CustomerOrderInformation. This directory is located in your data directory, which is located in the main directory where MySQL is installed. For example, if the file was named data.dat, the LOAD statement might look for the file at C:Program FilesMySQLMySQL Server 5.0dataCustomerOrderInformationdata.dat.
The basic form of the LOAD statement can be followed by optional phrases if you want to change a default delimiter. The options are
FIELDS TERMINATED BY 'character' FIELDS ENCLOSED BY 'character' LINES TERMINATED BY 'character'
Suppose that you have the data file for the Customer table, except that the fields are separated by a comma rather than a tab. The name of the data file is customer.dat, and it’s located in the same directory as the database. The SQL statement to read the data into the table is
LOAD DATA INFILE "customer.dat" INTO TABLE Customer FIELDS TERMINATED BY ','
To use the LOAD DATA INFILE statement, the MySQL account must have the FILE privilege on the server host.
You can also load data from a text file on your local computer by using the word LOCAL, as follows:
LOAD DATA LOCAL INFILE "path/datafilename" INTO TABLE tablename
You must include a path to the file. Use forward slashes for the path, even on a Windows computer, such as “C:/data/datafile1.txt”. If you get an error message when sending this statement, LOCAL might not be enabled.
To look at the data that you loaded — to make sure that it’s correct — use an SQL query that retrieves data from the database. Use the following query to look at all the data in the table so that you can check it:
SELECT * FROM Customer