Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.2Mb
PDF (A4) - 38.2Mb
PDF (RPM) - 37.1Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 9.0Mb
Man Pages (TGZ) - 206.2Kb
Man Pages (Zip) - 314.7Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
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.14 mysqlimport Options

Format Description Introduced Deprecated
--bind-address Use specified network interface to connect to MySQL Server
--columns This option takes a comma-separated list of column names as its value
--compress Compress all information sent between client and server
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--delete Empty the table before importing the text file
--enable-cleartext-plugin Enable cleartext authentication plugin 5.7.10
--fields-enclosed-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--force Continue even if an SQL error occurs
--help Display help message and exit
--host Connect to MySQL server on given host
--ignore See the description for the --replace option
--ignore-lines Ignore the first N lines of the data file
--lines-terminated-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--local Read input files locally from the client host
--lock-tables Lock all tables for writing before processing any text files
--login-path Read login path options from .mylogin.cnf
--low-priority Use LOW_PRIORITY when loading the table.
--no-defaults Read no option files
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--replace The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values
--secure-auth Do not send passwords to server in old (pre-4.1) format 5.7.4 5.7.5
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--silent Produce output only when errors occur
--socket For connections to localhost, the Unix socket file to use
--ssl Enable encrypted connection
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists
--ssl-crlpath Directory that contains certificate revocation list files
--ssl-key File that contains X509 key
--ssl-mode Security state of connection to server 5.7.11
--ssl-verify-server-cert Verify host name against server certificate Common Name identity
--tls-version Protocols permitted for encrypted connections 5.7.10
--use-threads Number of threads for parallel file-loading
--user MySQL user name to use when connecting to 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
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:

[mysqlimport]
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,

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

-Luke
http://lukewendling.com
  Posted by mick monaghan 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

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

--fields-optionally-enclosed-by=\"
  Posted by josé ambriz meza on December 27, 2012
En español, explicación de la importación
https://www.youtube.com/watch?v=qh8-iX2wddM

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