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.
Search WWH ::




Custom Search