Bulk Importing Data - Learning MySQL and MariaDB - page 402

Exercises
For the exercises in this chapter, you will need to download the employees.csv and birder-
list.csv files from the MySQL Resources site . You should copy it to the /tmp directory, or
another directory on your server that is accessible by the mysql system user.
I generated the employees.csv file by using the SELECT...INTO OUTFILE statement to
export data from the employee database. This is a large sample database created origin-
ally by the staff at MySQL, and is free for download .
1. Open the employees-list.csv file with a text editor to see how it's formatted. Then
create an import table to match it. When you're finished, use the LOAD DATA
INFILE statement to load the list of employees into the import table you created.
2. Open the birder-list.csv in a text editor to determine how it's formatted. It contains
a list of people who live in Italy and are prospects for our site. Create in the
birdwatchers database an import table with columns with these names and in
this order: id , formal_title , name_first , name_last , country , and
email . Make the id column an automatically incremented key column.
Construct a LOAD DATA INFILE statement to load the data from the birder-
list.csv file into the import table you create. Be sure to provide a list of column
names with this SQL statement. Use the SET clause to set the value of form-
al_title when loading the data. Female Italian names generally end with the
lettera. Male Italian names end generally with the lettero, but sometimes withi, or
e. Use these assumptions to have MySQL make a reasonable guess as to the per-
son's title of either Ms. or Mr. when loading the data. When ready, run the LOAD
DATA INFILE you constructed to load the data into the import table.
When finished, execute a SELECT statement to make sure the data loaded prop-
erly. If it didn't, delete the data in the import table and try again until you get it
right. Once you've successfully loaded the data, run a INSERT
INTO...SELECT statement to add the names to the humans table.
3. Using the SELECT...INTO OUTFILE statement, export a list of birds with the
word Least in their common name to a text file named little-birds.csv . Export the
common and scientific name of the birds, as well as the scientific names of the
family and order to which they belong. Enclose the fields in double quotes, and
separate them with commas. End the lines with a semicolon, but without a line
ending (i.e., no \n or \r ). This should cause the CSV file to write all of the text to
one long line. After you've exported the data, open the file with a text editor to
verify that the data is contained on one line.