brock

How to Bulk Import into a MySQL Database

27 Apr 2009

This tutorial shows you how to take a large number of values and import them as cells in your MySQL database.

Here is a typical scenario: we have a list of account numbers at work that tell us when a patient is in network. If a patient is out-of-network when they come to our clinic, they'll have a hefty bill to pay out of pocket; so the staff searches against a MySQL database that I setup to determine if they are in-network or not.

This database has over 8,000 account numbers in it. From time to time, new account numbers need to be added to the database. I will get an e-mail with anywhere from three to fifty new account numbers to add. If it is a small number, I simply go into PhpMyAdmin and manually add the rows. But if there are more than ten new account numbers, it would take a long time to enter them all by hand. So here are two ways to do it.

Method #1

Create a text file with the new account numbers in it, each on it's own line. It should look like this:

1
2
3
4
5
6
7

Save this text file and upload it to your server where the SQL database resides. If you are on a unix server, the full path would look like this:

/home/username/accounts.txt

With it saved to the server, we need to execute SQL code in a database editor like PHPMyAdmin or using the Webmin MySQL Database Server software. Inside this software, you'll want to browse into the database (in this case, let's call it "intranet"). Inside the database, you'll have a table for all of your account numbers (let's call this table "accounts").

You'll want to execute SQL code. Most software packages have an option to "Execute SQL" which works a lot like a command line. In this box, you'll execute the following:

LOAD DATA LOCAL INFILE '/home/username/accounts.txt' INTO TABLE accounts FIELDS TERMINATED BY 'r' (numbers, id)

Here is what all of that means:

LOAD DATA LOCAL INFILE: This simply means to pull the data from the text file that we uploaded from the server and to use the data for our import.

'/home/username/accounts.txt' : This would be the full path and filename of the text file that has all of your new account numbers. The username value refers to the username on the unix server where the file was uploaded. If you are on a Windows server, it would look more like 'C:Documents and SettingsusernameMy Documentsaccounts.txt' or something similar.

INTO TABLE accounts : This simply means that we want to insert the data into our "accounts" table in our database.

FIELDS TERMINATED BY 'r' : If you are familiar with Excel, this is like your delimiter. Since I have all of the values on their own line, I use 'r' (which refers to a carriage return, or a new line). If you separate them all by commas, you would just use ',' instead.

(numbers, id) : These are the column names inside the table where the data will be stored. If you were to open my table, you would see "id" first, then "numbers". The "id" field is the primary key assigned to the row, and the "numbers" field is where my account numbers are. The "numbers" field is listed first because SQL will put the data in the first field. ID will then auto increment.

Method #2

The second method is to paste the new data into your MySQL editor (instead of uploading a text file).


Method #2

The second method is to paste the new data into your MySQL editor (instead of uploading a text file). This can be done relatively quickly once you get the hang of it.

TIP: I keep an Excel file as a template so that all I have to do when I am e-mailed the list of account numbers is paste in the new numbers. The formulas in the cells then format the text correctly, and I have the commands saved just above. I just take my mouse, copy all of column A, then paste it into the SQL editor and I am done.

Here are the commands you would use to add a column full of strings to a database. Please note: I am importing strings, so I use single quotes around the values. I believe if you were using integers you would not want to use quotes.

INSERT INTO accounts (numbers) VALUES
('F1A0'),
('HB01'),
('T601')

Where "accounts" is the table name and "numbers" is the column name where the data will be imported. The table it will be imported into has two columns, "id" and "numbers". Since "id" will auto-increment and is the primary key, it will generate an ID number for each new account. You can do this manually if you prefer by modifying it to the following:

INSERT INTO accounts (numbers, id) VALUES
('F1A0',''),
('HB01',''),
('T601','')...

This works for multiple columns of data. You would simply display the column titles in the order that the data is listed on your import. For example:

INSERT INTO accounts (numbers, id, company_name, contact_person, phone) VALUES
('F1A0','','Brooks Brothers','Joe Smith','212-555-1212'),
('HB01','','Talbots', 'Jane Smith', '212-555-2121'),
('T601','','Lexus', 'Jack Smith', '212-555-1122')...

Good luck - and remember - work off of a test database before you run any of these commands on your system. And backup your database before each new command is run!