Database Reference
In-Depth Information
CHAPTER 11
Importing and Exporting Data
11.0. Introduction
Suppose that a file named
somedata.csv
contains 12 data columns in comma-separated
values (CSV) format. From this file you want to extract only columns 2, 11, 5, and 9,
and use them to create database rows in a MySQL table that contains
name
,
birth
,
height
, and
weight
columns. You must make sure that the height and weight are positive
integers, and convert the birth dates from
MM/DD/YY
format to
CCYY-MM-DD
format. How
can you do this?
In one sense, that problem is very specialized. But it's not at all atypical because data
transfer problems with specific requirements occur frequently when you transfer data
into MySQL. Datafiles are not always nicely formatted and ready to load into MySQL
with no preparation. As a result, it's often necessary to preprocess information to put it
into a format acceptable for MySQL. The reverse also is true; data exported from MySQL
may need massaging to be useful for other programs.
Although some data preparation operations are so difficult that they require a great deal
of hand checking and reformatting, in most cases you can do at least part of the job
automatically. Virtually all such problems involve at least some elements of a common
set of conversion issues. This chapter and the next discuss what these issues are, how to
deal with them by taking advantage of the existing tools at your disposal, and how to
write your own tools when necessary. The idea is not to cover all possible situations (an
impossible task), but to show representative techniques and utilities. Use them as is or
adapt them for problems that they don't handle. (There are commercial data-handling
tools, but my purpose here is to enable you to do things yourself.) With respect to the
problem posed at the beginning of this Introduction, see
Recipe 12.15
for the solution
we arrive at.
The discussion begins with native MySQL facilities for importing data (the
LOAD
DATA
statement and the
mysqlimport
command-line program), and for exporting data (the
