Database Reference
In-Depth Information
Preparing to Import
To import data intoMySQL or MariaDB, the data needs to be in a compatible format. Both
database systems will accept a simple text file in which the values are delimited in some
way. The easiest way to deal with incompatible data in any format is to load it in its origin-
al software and to export it to a delimited text file. Most applications can do this. They will
usually separate field values by commas and separate records by hard returns, putting each
row on a separate line. Some applications will allow you to set the delimiters to your
choice. If that's an option, you might use the bar (i.e.,
|
) to separate fields because it won't
typically be used within the data, and separate records with a new-line.
For some examples related to the
rookery
database, let's get a large data text file to use.
Cornell University is famous forornithology. They also publish books on birds through
Cornell University Press. One of their publications is
The Clements Checklist of World
Birds
byJames F. Clements. The list of birds from this publication is
on its website
in a
spreadsheet and in a comma-separated values (CSV) format. Every August, an updated list
is posted on the site for people and organizations to use freely on their own sites and in
their databases to promote the study and appreciation of birds.
Suppose we want to compare the latest list to our
birds
table to see whether there are any
new species. This may seem intimidating, but it can be done without much trouble. To par-
ticipate, download the CSV file from Cornell's site or
MySQL Resources
. For the ex-
amples that follow, I downloaded the
Clements-Checklist-6.9-final.csv
file.
WARNING
If you use FTP to upload a text file to the server, be sure to upload it in ASCII mode and not binary mode.
If the text file was created with a program that uses binary characters or binary hard returns, these will
cause problems when loading the data.
After you download the Cornell data text file, open it with a text editor to see how the con-
tent looks. You will need to know how the lines and fields are organized and delineated.
Some excerpts follow from the Cornell data file that I downloaded:
sort 6.9,Clements 6.9 change,2014 Text for website,
Category,Scientific name,English name,Range,
Order,Family,Extinct,Extinction Year,sort 6.8,sort 6.7,page 6.0,,,,,
...
4073,new species,"Walters (1991) and Cibois et al. (2012) proposed
recognition of Prosobonia ellisi Sharpe 1906, with English name