Database Reference
In-Depth Information
11.2. Importing CSV Files
Problem
You want to load a file that is in CSV format.
Solution
Use the appropriate format specifiers with
LOAD
DATA
or
mysqlimport
.
Discussion
Datafiles in CSV format contain values that are delimited by commas rather than tabs
and that may be quoted with double-quote characters. A CSV file
mytbl.txt
containing
lines that end with carriage return/linefeed pairs can be loaded into
mytbl
using
LOAD
DATA
:
mysql>
LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl
->
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
->
LINES TERMINATED BY '\r\n';
Or like this using
mysqlimport
:
%
mysqlimport --local --lines-terminated-by="\r\n" \
--fields-terminated-by="," --fields-enclosed-by="\"" \
cookbook mytbl.txt
11.3. Exporting Query Results from MySQL
Problem
You want to export the result of a query from MySQL into a file or another program.
Solution
Use the
SELECT
…
INTO
OUTFILE
statement, or redirect the output of the
mysql
program.
Discussion
The
SELECT
…
INTO
OUTFILE
statement exports a query result directly into a file on the
server host. To capture the result on the client host instead, redirect the output of the
mysql
program. These methods have different strengths and weaknesses; get to know
them both and apply whichever one best suits a given situation.
