How To Upload Data to MySQL tables using mysqlimport

by Ramesh Natarajan on October 14, 2008

MySQL LogoUploading several rows of data from a text, csv, and excel file into a MySQL table is a routine task for sysadmins and DBAs who are managing MySQL database. This article explains 4 practical examples on how to import data from a file to MySQL table using both mysqlimport and “load data local infile” method. Instead of importing data, if you want to backup and restore MySQL database, please use mysqldump or mysqlhotcopy.

Create an employee table and employee.txt datafile

For the examples mentioned in this article, let us create a very simple employee table with three columns–employee number, employee name and job.

# mysql -u root -ptmppassword
mysql> use test
Database changed
mysql> create table employee
    -> (
    -> empno int,
    -> ename varchar(15),
    -> job varchar(10)
    -> );
Query OK, 0 rows affected (0.01 sec)

Create a test datafile employee.txt with fields delimited by tab as shown below.

# cat employee.txt
100     John Doe        DBA
200     John Smith      Sysadmin
300     Raj Patel       Developer

1. Upload tab delimited datafile to MySQL table

Use mysqlimport to import the employee.txt datafile to employee table in test database, as shown below:

# mysqlimport -u root -ptmppassword --local test employee.txt
test.employee: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Verify that the records got uploaded successfully.

# mysql -u root -ptmppassword
mysql> use test;
mysql> select * from employee;
+-------+------------+-----------+
| empno | ename      | job       |
+-------+------------+-----------+
|   100 | John Doe   | DBA       |
|   200 | John Smith | Sysadmin  |
|   300 | Raj Patel  | Developer |
+-------+------------+-----------+
3 rows in set (0.00 sec)


Note: In mysqlimport, the name of the datafile should match the name of the table. The extension of the datafile can be anything. In the above example, only employee.* datafile can be used to upload data to employee table. You’ll get the following error message when the filename is not same as tablename:

# mysqlimport -u root -ptmppassword --local test emp.txt
mysqlimport: Error: Table 'test.emp' doesn't exist, when using table: emp
[Note: The table name is employee. So, datafile name should be employee.*]

2. Import multiple datafiles into multiple MySQL tables

The following example uploads data from two different datafiles to two different tables. i.e It uploads employee.txt to employee table and manager.txt to manager table.

# mysqlimport -u root -ptmppassword --local test employee.txt manager.txt

3. Use LOAD DATA LOCAL INFILE to upload data to MySQL tables

The mysqlimport client is simply a command-line interface to the LOAD DATA LOCAL INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of “load data local infile” syntax. You can perfrom the same upload explained in example#1 using “load data local infile” instead of mysqlimport as explained below:

# mysql -u root -ptmppassword
mysql> use test;
mysql> LOAD DATA LOCAL INFILE '/home/ramesh/employee.txt'
    -> INTO TABLE employee
    -> FIELDS TERMINATED BY '\t'
    -> LINES TERMINATED BY '\n'
    -> (empno, ename, job);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from employee;
+-------+------------+-----------+
| empno | ename      | job       |
+-------+------------+-----------+
|   100 | John Doe   | DBA       |
|   200 | John Smith | Sysadmin  |
|   300 | Raj Patel  | Developer |
+-------+------------+-----------+
3 rows in set (0.00 sec)

4. Most frequently used mysqlimport options

The most frequently used mysqlimport options are shown in the example below. Most of these options are self explanatory.

  • compress: Compress all information sent between the client and the server
  • delete: This option is very handy when you want to empty the table before importing the text file
  • local: Read input files locally from the client host
  • lock-tables: Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server.
# mysqlimport \
    --user=root \
    --password=tmppassword \
    --columns=empno,ename,job \
    --compress \
    --delete \
    --fields-optionally-enclosed-by='"' \
    --fields-terminated-by='\t' \
    --fields-escaped-by='' \
    --lines-terminated-by='\n' \
    --local \
    --lock-tables \
    --verbose \
    test employee.txt

Output of the above mysqlimport command:

Connecting to localhost
Selecting database test
Locking tables for write
Deleting the old data from table employee
Loading data from LOCAL file: /home/ramesh/employee.txt into employee
test.employee: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
Disconnecting from localhost

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 6 comments… read them below or add one }

1 Ajith Edassery October 17, 2008 at 7:43 pm

@Ramesh,
One thing that never worked with me while using my Wordpress MySQL DB is that, if I take a data backup from my live database and restore it on my home wordpress setup (for experiments) it never works – ie. I can’t even restore though both are running some versions of MySQL as well as Wordpress…

After a couple of trials (and reinstall of WP) I gave up… I need to figure out why this error comes… in fact, for a long time now I am not having the exact replica of my live blog at my home PC.

Cheers,
Ajith

2 Anonymous February 20, 2009 at 12:23 am

it nice
thanks

3 Shritam Bhowmick February 19, 2013 at 4:36 am

Ajith, because the build is different (versions have build numbers).
Ch33rs !

4 Manikandan S May 20, 2013 at 7:16 am

Hi, nice! thanks for the post.

5 anony March 20, 2014 at 6:20 pm

I think “-ptmppassword” should read “-p tmppassword”

6 david July 27, 2014 at 12:40 am

Hi Ramesh,

Many thanks. I was struggling with mysqlimport but your example clarified.

Thanks again.

Leave a Comment

Previous post:

Next post: