Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  mysqlimport — A Data Import Program

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 13.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.

mysqlimport supports the following options, which can be specified on the command line or in the [mysqlimport] and [client] groups of an option file. For information about option files used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Table 4.12 mysqlimport Options

--bind-addressUse specified network interface to connect to MySQL Server
--columnsThis option takes a comma-separated list of column names as its value
--compressCompress all information sent between client and server
--debugWrite debugging log
--debug-checkPrint debugging information when program exits
--debug-infoPrint debugging information, memory, and CPU statistics when program exits
--default-authAuthentication plugin to use
--default-character-setSpecify default character set
--defaults-extra-fileRead named option file in addition to usual option files
--defaults-fileRead only named option file
--defaults-group-suffixOption group suffix value
--deleteEmpty the table before importing the text file
--enable-cleartext-pluginEnable cleartext authentication plugin5.7.10
--fields-enclosed-byThis option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-byThis option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-byThis option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-byThis option has the same meaning as the corresponding clause for LOAD DATA INFILE
--forceContinue even if an SQL error occurs
--helpDisplay help message and exit
--hostConnect to MySQL server on given host
--ignoreSee the description for the --replace option
--ignore-linesIgnore the first N lines of the data file
--lines-terminated-byThis option has the same meaning as the corresponding clause for LOAD DATA INFILE
--localRead input files locally from the client host
--lock-tablesLock all tables for writing before processing any text files
--login-pathRead login path options from .mylogin.cnf
--low-priorityUse LOW_PRIORITY when loading the table.
--no-defaultsRead no option files
--passwordPassword to use when connecting to server
--pipeOn Windows, connect to server using named pipe
--plugin-dirDirectory where plugins are installed
--portTCP/IP port number to use for connection
--print-defaultsPrint default options
--protocolConnection protocol to use
--replaceThe --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values
--secure-authDo not send passwords to server in old (pre-4.1) format5.
--shared-memory-base-nameThe name of shared memory to use for shared-memory connections
--silentProduce output only when errors occur
--socketFor connections to localhost, the Unix socket file to use
--sslEnable SSL for connection
--ssl-caPath of file that contains list of trusted SSL CAs
--ssl-capathPath of directory that contains trusted SSL CA certificates in PEM format
--ssl-certPath of file that contains X509 certificate in PEM format
--ssl-cipherList of permitted ciphers to use for SSL encryption
--ssl-crlPath of file that contains certificate revocation lists
--ssl-crlpathPath of directory that contains certificate revocation list files
--ssl-keyPath of file that contains X509 key in PEM format
--ssl-verify-server-certVerify Common Name value in server certificate against host name used when connecting to server
--use-threadsNumber of threads for parallel file-loading
--userMySQL user name to use when connecting to server
--verboseVerbose mode
--versionDisplay 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
100     Max Sydow
101     Count Dracula
w imptest.txt
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
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 |

Download this Manual
User Comments
  Posted by Tom Reinertson on May 17, 2002
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
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
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
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
If your mysqlimport's are not working anymore after the upgrade to mysql 4.0.22, try adding this to your my.cnf:

local = 1

Or add "--local" to your scripts...
  Posted by Philippe Jausions on February 4, 2005
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,

  Posted by David Pearce on February 9, 2006
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
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
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

  Posted by mick m on August 26, 2008
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 Handapangoda on May 14, 2009
This works fine importing csv file to table called tbl_temp_data
import large csv file in to mysql

INTO TABLE tbl_temp_data

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 have to change max packet size varible value (default is 1Mb)

using mysql-administrator GUI you can easily change this
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
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


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
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.
  Posted by Bill Swartz on February 2, 2011
Running mysqlimport v3.7 under Win32. Contrary to expected behavior, double quoting the textfile parameter does not cause the tool to accept directory or file names that contain whitespace.
  Posted by Paul Mars on May 23, 2012
Here's how I got mysqlimport to work on Windows:

  Posted by josé ambriz meza on December 27, 2012
En español, explicación de la importación

Sign Up Login You must be logged in to post a comment.