I just ran into a situation where I needed to import some data into a mysql database table. I had the data I needed in a text file, and I needed to import the data in that file into my database table.

A few more specifics about the problem and the solution:

• The table named is "words".
• The table consists of three columns named "id", "english", and "spanish".
• The file is named "english-spanish.txt".
• There are two fields in this file: an english word, and then the spanish version of that word. The two words are separated by a [Tab] character.
• The file is located in the same directory I'm running the mysql command line prompt from.
• Before running the following command I logged into mysql and switched to my database (named "spanglish_development").

To be as clear as possible here's a MySQL description of my database table:

mysql> use spanglish_development;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> desc words;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI |         | auto_increment |
| english | varchar(100) | NO   |     |         |                |
| spanish | varchar(100) | NO   |     |         |                |
+---------+--------------+------+-----+---------+----------------+

With that background in mind, I ran the following command at the mysql command prompt (after logging in to mysql and switching to my database):

load data local infile 'english-spanish.txt'
    -> into table words
    -> fields terminated by '\t'
    -> lines terminated by '\n'
    -> (english,spanish);

Everything here is very important, but note how I made sure to skip the "id" field in the import list at the end of that command.

On my MacBook Pro this command successfully imported over 7,000 records into my table in less than one second.