Database Reference
In-Depth Information
Using mysqlimport
If you regularlyreceive a data text file in the same format, you might find it useful to cre-
ate a simple shell script to load the data into MySQL. For such a task, you can use the
mysqlimport
utility. It will execute the
LOAD DATA INFILE
statement with any options
you include.
For an example of how this utility may be used, let's use one of the recent examples of the
LOAD DATA INFILE
statement, where we loaded data from the
birdwatcher-pro-
spects.csv
file. For this utility, though, we will have to rename the file to the same as the
import table, so the file's name will be
birdwatcher_prospects.csv
. I'll explain this in a
moment. For now, try executing the following from the command line on your server:
mysqlimport -user='
marie_dyer
' --password='
sevenangels
' \
--replace --low-priority --ignore-lines='1' \
--fields-enclosed-by='"' --fields-terminated-by='|'
--fields-escaped-by='\\' \
--lines-terminated-by=']\r\n' \
--columns='prospect_name, prospect_email, prospect_country' \
birdwatchers '
/tmp/birdwatcher_prospects_import.csv
'
As you can see, all of the options are the sames as their counterparts, but in lowercase let-
ters and preceded by two hyphens. The order of options doesn't matter, except that the
database and filename are last. After the filename, you can list multiple text files separ-
ated by spaces, and they will be processed in order by
mysqlimport
.
The prefix of the filename must be the same as the table — the dot and the extension are
ignored. This rule lets the command determine the table into which to load data. Because
table names cannot include a hyphen, which could be mistaken for a minus sign, we had
to use an underscore.
The
mysqlimport
utility works thesame as
LOAD DATA INFILE
; in fact, internally it
calls that SQL statement. As mentioned, you can include it in a shell script or an entryin
crontab to automate the loading of data from a data text file that is periodically replaced
on the server.