DEVELOPER ZONE ::
Login / Register
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.5, “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 filename 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:
Display a help message and exit.
The directory where character sets are installed. See Section 5.10.1, “The Character Set Used for Data and Sorting”.
--columns=, column_list-c column_list
This option takes a comma-separated list of column names as its value. The order of the column names indicates how to match data file columns with table columns.
Compress all information sent between the client and the server if both support compression.
--debug[=, debug_options]-# [debug_options]
Write a debugging log. The debug_options string often is 'd:t:o,.file_name'
--default-character-set=charset_name
Use charset_name as the default character set. See Section 5.10.1, “The Character Set Used for Data and Sorting”.
Empty the table before importing the text file.
--fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...
These options have the same meaning as the corresponding clauses for LOAD DATA INFILE. See Section 13.2.5, “LOAD DATA INFILE Syntax”.
Ignore errors. For example, if a table for a text file does not exist, continue processing any remaining files. Without --force, mysqlimport exits if a table does not exist.
--host=, host_name-h host_name
Import data to the MySQL server on the given host. The default host is localhost.
See the description for the --replace option.
Ignore the first N lines of the data file.
This option has the same meaning as the corresponding clause for LOAD DATA INFILE. For example, to import Windows files that have lines terminated with carriage return/linefeed pairs, use --lines-terminated-by="\r\n". (You might have to double the backslashes, depending on the escaping conventions of your command interpreter.) See Section 13.2.5, “LOAD DATA INFILE Syntax”.
Read input files locally from the client host.
MySQL Enterprise. For expert advice on the security implications of enabling LOCAL, subscribe to the MySQL Network Monitoring and Advisory. For more information see http://www.mysql.com/products/enterprise/advisors.html.
Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server.
Use LOW_PRIORITY when loading the table.
--password[=, password]-p[password]
The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one.
Specifying a password on the command line should be considered insecure. See Section 5.8.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use.
The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values. If you specify --replace, new rows replace existing rows that have the same unique key value. If you specify --ignore, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, an error occurs when a duplicate key value is found, and the rest of the text file is ignored.
Silent mode. Produce output only when errors occur.
For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.
Options that begin with --ssl specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See Section 5.8.7.3, “SSL Command Options”.
--user=, user_name-u user_name
The MySQL username to use when connecting to the server.
Verbose mode. Print more information about what the program does.
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))' testshell>eda 100 Max Sydow 101 Count Dracula . w imptest.txt 32 q shell>od -c imptest.txt0000000 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.txttest.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
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.
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.
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.
If you get an "access denied" error message,
you may want to try the --local option to mysqlimport.
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...
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
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.
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.
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
Add your own comment.