Skip navigation links
MySQL 5.0 Reference Manual :: 4 MySQL Programs :: 4.5 MySQL Client Programs :: 4.5.5 mysqlimport — A Data Import Program
4.5.6 mysqlshow — Display Database, Table, and Column Information »
Section Navigation      [Toggle]

4.5.5. mysqlimport — A Data Import Program

The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of LOAD DATA INFILE syntax. See Section 12.2.6, “LOAD DATA INFILE Syntax”.

Invoke mysqlimport like this: 

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file's contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient.

mysqldump supports the following options, which can be specified on the command line or in the [mysqldump] and [client] option file groups. mysqldump also supports the options for processing option files described at Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.

Table 4.6. mysqlimport Options

Format Config File Description Introduction Deprecated Removed
--columns=column_list columns This option takes a comma-separated list of column names as its value      
--compress compress Compress all information sent between the client and the server      
--debug[=debug_options] debug Write a debugging log      
--default-character-set=charset_name default-character-set Use charset_name as the default character set      
--delete delete Empty the table before importing the text file      
--fields-enclosed-by=string fields-enclosed-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE      
--fields-escaped-by fields-escaped-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE      
--fields-optionally-enclosed-by=string fields-optionally-enclosed-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE      
--fields-terminated-by=string fields-terminated-by -- This option has the same meaning as the corresponding clause for LOAD DATA INFILE      
--force force Continue even if an SQL error occurs      
--help   Display help message and exit      
--host=host_name host Connect to the MySQL server on the given host      
--ignore ignore See the description for the --replace option      
--ignore-lines=# ignore-lines Ignore the first N lines of the data file      
--lines-terminated-by=string lines-terminated-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE      
--local local Read input files locally from the client host      
--lock-tables lock-tables Lock all tables for writing before processing any text files      
--low-priority low-priority Use LOW_PRIORITY when loading the table.      
--password[=password] password The password to use when connecting to the server      
--pipe   On Windows, connect to server via a named pipe      
--port=port_num port The TCP/IP port number to use for the connection      
--protocol=type protocol The connection protocol to use      
--replace replace The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values      
--silent silent Produce output only when errors occur      
--socket=path socket For connections to localhost      
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs      
--ssl-capath=directory_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format      
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection      
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption      
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection      
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server      
--user=user_name, user The MySQL user name to use when connecting to the server      
--verbose   Verbose mode      
--version   Display version information and exit      

Here is a sample session that demonstrates use of mysqlimport: 

shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
shell> ed
a
100     Max Sydow
101     Count Dracula
.
w imptest.txt
32
q
shell> od -c imptest.txt
0000000   1   0   0  \t   M   a   x       S   y   d   o   w  \n   1   0
0000020   1  \t   C   o   u   n   t       D   r   a   c   u   l   a  \n
0000040
shell> mysqlimport --local test imptest.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
shell> mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id   | n             |
+------+---------------+
|  100 | Max Sydow     |
|  101 | Count Dracula |
+------+---------------+

User Comments

Posted by Tom Reinertson on May 17 2002 2:24am[Delete] [Edit]

Keep in mind that your imported text file should
have some value for empty fields. I regularly
build tables using msqlimport to import
tab-delimited text files. My tables contain
integer fields, some of which are
auto_incremented and some are not. MYSQL will
let you represent empty fields as null text
strings, i.e., two tab characters back-to-back,
but I found this increments the warning count.
To solve this problem you must use some value
for empty fields. Since auto_increment fields
use 0 or NULL, one would think, incorrectly,
that you could use 0 or \N to represent a null
value in the import text file. You must 0 for
an auto_increment field. Using \N increments
the warning count. You should use \N for other
numeric fields where you want a null value.
This problem is especially perplexing because of
MySQL's inability to report the text of a
warning. It only reports a warning count.

Posted by Murali Mohan on November 5 2002 8:58pm[Delete] [Edit]

Before you invoke mysqlimport command with
appropriate options, please check that the 'FILE'
privilege is granted to you.
I wasted time facing the 'Access Denied on
table_name' error
because of the same.

Posted by Subburaj Palanichamy on January 29 2003 1:51am[Delete] [Edit]

Mysqlimport - access_to_mysql.txt - Usage - reg.

While converting the data from Microsoft Access database to Mysql, I have used the access_to_mysql.txt tool. In my database, some of the tables were were linked with another microsoft access database for which password has been set. Hence while converting the data, it displayed an error.

To over come this, I opened the database which has the linked table and removed the password set for that database.

Once password is removed, all the tables and data was successfully transferred to C:\temp\mysqldump.txt file.


Posted by Bli Bla on June 30 2004 3:07pm[Delete] [Edit]

If you get an "access denied" error message,
you may want to try the --local option to mysqlimport.

Posted by Olivier Müller on November 1 2004 2:32pm[Delete] [Edit]

If your mysqlimport's are not working anymore after the upgrade to mysql 4.0.22, try adding this to your my.cnf:

[mysqlimport]
local = 1

Or add "--local" to your scripts...

Posted by Philippe Jausions on February 4 2005 3:57pm[Delete] [Edit]

If you are one of the many people trying to import a CSV file into MySQL using mysqlimport under MS-Windows command/DOS prompt, try the following:

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv

Between quotes " and backslashes \ it can really give you a hard time finding the proper combination under Windows...

I usually run this command from the folder containing the YOUR_TABLE.csv file.

If you have a header in your .csv file with the name of columns or other "junk" in it, just add a --ignore-lines=X to skip the first X lines (i.e. --ignore-lines=1 to skip 1 line)

If your fields are (optionally) enclosed by double-quotes " and which themselves are doubled inside a value (i.e. a double double-quote "" = 1 double-quote ") then also use --fields-escaped-by=\ (default) and NOT --fields-escaped-by="""

I hope this helps someone,

-Philippe

Posted by David Pearce on February 9 2006 12:49am[Delete] [Edit]

If your use the --delete or -D commands AND you are importing multiple files to the same database, the screen messages indicate it clears the table before each import file. If this is true, you will only ever have the data from the last imported file.

Posted by Tom Wasiluk on October 20 2006 12:09pm[Delete] [Edit]

If you want to skip columns from the import file
try using something like --columns=col1,@x,col2

This will skip the second column, sending it to
the variable @x instead of the destination table.

Posted by Luke Wendling on November 10 2006 10:26pm[Delete] [Edit]

I was getting a 'field too long' error, but using the --columns flag as below worked for me on Windows.

(for localhost import)
mysqlimport --fields-terminated-by=, --lines-terminated-by="\r\n" DBNAME FILENAME -u USERNAME -p --delete --columns=code,city,state,county

-Luke
http://lukewendling.com

Posted by mick m on August 26 2008 6:41pm[Delete] [Edit]

I found I had to use the **complete path** to the table file - rather than using "./mytable.txt":

./bin/mysqlimport --columns=title,body --fields-terminated-by="||" kb /usr/local/mysql/s_kb_entry.txt

Otherwise mysqlimport went looking for the table.txt file in the data directory.

Posted by Manoj Prasanna on May 14 2009 8:38am[Delete] [Edit]

This works fine importing csv file to table called tbl_temp_data
import large csv file in to mysql

LOAD DATA LOCAL INFILE C:\test.csv
INTO TABLE tbl_temp_data
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'

If your csv file is less than 1MB and with default mysql configuration this works good, but
if you have csv file size larger than 1MB then it will not import that file.you have to change max packet size varible value (default is 1Mb)

using mysql-administrator GUI you can easily change this
value
In mysql-administrator GUI --> startup varables --> Advanced Networking tab --> data/memory size group box --> Max packet size = ""

.................. have fun with mysql .........



Posted by A C on June 3 2009 3:11pm[Delete] [Edit]

When I used mysqlimport I found both DOS and Linux were just bombing out with showing me the usage blurb again without any reason. I finally figured out that

--fields-optionally-enclosed-by="""

is incorrect, you need to escape the middle " as shown in this full example:

mysqlimport --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv

the above example also assumes you have copied your YOUR_TABLE.csv file into the data directory for YOUR_DATABASE, though I think I read you can specify an absolute path to YOUR_TABLE.csv also.

Posted by Joe Voirol on March 22 2010 8:18pm[Delete] [Edit]

The --local option is VERY important if you want to import a file from anywhere else other than /var/lib/mysql/table_name directory. If you use --local then you can use the FULL path to the file that you want to import and you do not have to make any changes to the directory permissions for the /var/lib/mysql directory to move files around. It took me a little while to understand what this option meant.

I thought I would share this as it was very frustrating.

Add your own comment.