mysqlimport, Importing Data from Text Files
-------------------------------------------
`mysqlimport' provides a command-line interface to the `LOAD DATA
INFILE' SQL statement. Most options to `mysqlimport' correspond
directly to the same options to `LOAD DATA INFILE'. *Note `LOAD DATA':
LOAD DATA.
`mysqlimport' is invoked like this:
shell> mysqlimport [options] database textfile1 [textfile2....]
For each text file named on the command line, `mysqlimport' strips any
extension from the filename and uses the result to determine which
table to import the file's contents into. For example, files named
`patient.txt', `patient.text', and `patient' would all be imported into
a table named `patient'.
`mysqlimport' supports the following options:
`-c, --columns=...'
This option takes a comma-separated list of field names as an
argument. The field list is used to create a proper `LOAD DATA
INFILE' command, which is then passed to MySQL. *Note `LOAD DATA':
LOAD DATA.
`-C, --compress'
Compress all information between the client and the server if both
support compression.
`-#, --debug[=option_string]'
Trace usage of the program (for debugging).
`-d, --delete'
Empty the table before importing the text file.
`--fields-terminated-by=...'
`--fields-enclosed-by=...'
`--fields-optionally-enclosed-by=...'
`--fields-escaped-by=...'
`--lines-terminated-by=...'
These options have the same meaning as the corresponding clauses
for `LOAD DATA INFILE'. Note:`LOAD DATA'.
`-f, --force'
Ignore errors. For example, if a table for a text file doesn't
exist, continue processing any remaining files. Without `--force',
`mysqlimport' exits if a table doesn't exist.
`--help'
Display a help message and exit.
`-h host_name, --host=host_name'
Import data to the MySQL server on the named host. The default host
is `localhost'.
`-i, --ignore'
See the description for the `--replace' option.
`-l, --lock-tables'
Lock *ALL* tables for writing before processing any text files.
This ensures that all tables are synchronized on the server.
`-L, --local'
Read input files from the client. By default, text files are
assumed to be on the server if you connect to `localhost' (which
is the default host).
`-pyour_pass, --password[=your_pass]'
The password to use when connecting to the server. If you specify
no `=your_pass' part, `mysqlimport' you will be prompted for a
password.
`-P port_num, --port=port_num'
The TCP/IP port number to use for connecting to a host. (This is
used for connections to hosts other than `localhost', for which
Unix sockets are used.)
`-r, --replace'
The `--replace' and `--ignore' options control handling of input
records that duplicate existing records 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 don't specify either option, an error occurs when a
duplicate key value is found, and the rest of the text file is
ignored.
`-s, --silent'
Silent mode. Write output only when errors occur.
`-S /path/to/socket, --socket=/path/to/socket'
The socket file to use when connecting to `localhost' (which is the
default host).
`-u user_name, --user=user_name'
The MySQL user name to use when connecting to the server. The
default value is your Unix login name.
`-v, --verbose'
Verbose mode. Print out more information what the program does.
`-V, --version'
Print version information and exit.
Here is a sample run using `mysqlimport':
$ mysql --version
mysql Ver 9.33 Distrib 3.22.25, for pc-linux-gnu (i686)
$ uname -a
Linux xxx.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 i586 unknown
$ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
$ ed
a
100 Max Sydow
101 Count Dracula
.
w imptest.txt
32
q
$ 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
$ mysqlimport --local test imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
$ mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id | n |
+------+---------------+
| 100 | Max Sydow |
| 101 | Count Dracula |
+------+---------------+