Database Reference
In-Depth Information
Loading Data Basics
To load data intoMySQL or MariaDB, you need an administrative user account thathas
The
LOAD DATA INFILE
statementloads data from a text file. It's a versatile SQL state-
ment with several options and clauses. We'll look at them throughout this chapter. The fol-
lowing command is the minimum we would enter from the
mysql
client to load the data
from the
Clements-Checklist-6.9-final.csv
file data file from Cornell into the
clem-
ents_list_import
table:
LOAD DATA INFILE '
/tmp/Clements-Checklist-6.9-final.csv
'
INTO TABLE rookery.clements_list_import
FIELDS TERMINATED BY ',';
Notice in the SQL statement here that the file path and name are enclosed in quotes. You
can use single or double quotes. Notice alsothe
FIELDS
clause. In this clause, we define
the parameters of the fields, how they are identified. For the CSV file we're importing,
fields are deliminated from each other with a comma. For this, we add to the
FIELDS
clause the
TERMINATED BY
subclause and a comma within quotes.
There are other subclauses and other clauses, but this is the least required for the
LOAD
DATA INFILE
statement. However, this SQL statement as we've constructed it will cause
problems and generate warning messages.
Watching for Warnings
If you ran the
LOAD DATA INFILE
statement inthe previous section, you may have no-
ticed many warnings. The following output shows the message generated by running that
SQL statement, and the firstfew warnings:
Query OK, 32187 rows affected, 65535 warnings (0.67 sec)
Records: 32187 Deleted: 0 Skipped: 0 Warnings: 209249
SHOW WARNINGS;
+---------+------+--------------------------------------------------------------+
| Level | Code |
Message |
+---------+------+--------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'sort 6.9' for column
'id' at row 1 |
| Warning | 1265 | Data truncated for column 'col2' at row
1 |
