Database Reference
In-Depth Information
Before begining an import, you will need to put the CSV file on the server and in a direct-
ory accessible by MySQL. It's a good security habit to put data files in non public direct-
ories. But to keep it simple, for our purposes, we'll use the
/tmp
directory to hold tempor-
arily the data text files for importing.
The next task in preparing to import the
Clements-Checklist-6.9-final.csv
file is to create a
table into which to import it. It contains more rows and more columns than we need, but
importing 32,000 lines from a CSV file will take only seconds. So the size is not a prob-
lem.
We could import the data directly into an existing table, but it's best to create a new table
that we'll use only for the import. We can execute an
INSERT INTO...SELECT
state-
ment later to copy the data from the import table we create into an existing table. Execute
the following on your server to create the import table:
CREATE TABLE
rookery
.
clements_list_import
(
id
INT
,
change_type
VARCHAR
(
255
),
col2
CHAR
(
0
),
col3
CHAR
(
0
),
scientific_name
VARCHAR
(
255
),
english_name
VARCHAR
(
255
),
col6
CHAR
(
0
),
`order`
VARCHAR
(
255
),
family
VARCHAR
(
255
),
col9
CHAR
(
0
),
col10
CHAR
(
0
),
col11
CHAR
(
0
),
col12
CHAR
(
0
),
col13
CHAR
(
0
),
col14
CHAR
(
0
),
col15
CHAR
(
0
),
col16
CHAR
(
0
),
col17
CHAR
(
0
));
This
CREATE TABLE
statementcreates a table with one column for each field of a line
in the data text file. The columns are in the same order as the fields in the data text file.
For the fields that we won't need, we've assigned generic names for the related columns
with a data type of
CHAR(0)
— a fixed character field with a width of 0 characters — so
that the data for those fields won't be stored. There's a better way to do this. We could just
import the columns we want. But we'll cover that later in this chapter. For this example,
we'll use this simple method and focus on the other fields.
For the fields we want, I've assigned names for the columns close to the field names from
the data text file and a data type of
VARCHAR(255)
. Notice that we had to put the
or-
der
field within backticks. That's because the word
order
is areserved word (e.g., the
ORDER BY
clause). We can use it for a column name, as long as we always refer to it in
this way. Otherwise it will confuse MySQL and cause an error.
