MySQL CSV import example

I just ran into a situation where I needed to import some CSV data into a MySQL database table. I already had the data I needed in a CSV file format, and I needed to import the data in that file into my MySQL database table.

A few more specifics about the problem and the solution:

• The MySQL table named is "words".
• The MySQL table consists of three columns named "id", "english", and "spanish".
• The CSV file is named "english-spanish.txt".
• There are two fields in this CSV file: an english word, and then the spanish version of that word. The two words are separated by a [Tab] character.
• The CSV 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").

My MySQL database table

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   |     |         |                |
+---------+--------------+------+-----+---------+----------------+

MySQL CSV import example

With that background in mind, I ran the following "MySQL CSV import" 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, which is pretty cool.