The next couple of options describe the layout of the file. These same options are available in the
mysqlimport utility. They just look a little different in this context. First, the keyword FIELDS is used. If
this word is used, the MySQL parser expects to see at least one of the following:
TERMINATED BY character
ENCLOSED BY character
ESCAPED BY character
These keywords and their arguments are used in the same way as the mysqlimport options. The
TERMINATED BY phrase describes what separates the fields in the file. By default, it is the tab
character (\t). The ENCLOSED BY phrase describes the encompassing characters. If the fields are
enclosed with quotation marks, you would specify that they are using this command. Finally, the
ESCAPED BY phrase describes the escape character. By default, it is the backslash character (\).
Using the previous example from the mysqlimport utility, that same file would be imported the
following way using the LOAD DATA INFILE statement:
LOAD DATA INFILE "/home/mark/Orders.txt" REPLACE
INTO TABLE Orders
FIELDS
TERMINATED BY ','
ENCLOSED BY '"';
One feature that is available in the LOAD DATA INFILE statement that is not in the mysqlimport
utility is the ability to name specific columns into which to put the data. This is really important if you
have a partial import of the data. For example, you are upgrading from Access to MySQL and, in the
process, you have added columns to the MySQL database to accommodate some additional
requirements. The data that you have in your existing database is still usable but, because the columns
don't match in number any more, you can't use the import utility anymore. However, LOAD DATA
INFILE can come to your rescue. The following shows how to LOAD DATA into the column that you
want:
LOAD DATA INFILE "/home/Order.txt"
INTO TABLE Orders
(Order_Number, Order_Date, Customer_ID);As you can see, you can specify as many columns as you want. The column names are also enclosed
in parentheses and separated by commas. If you forget either one, MySQL will complain.Importing Data from Microsoft Access
The next step is to put what you've learned into action. You are going to import data from a Microsoft Access
database. There are several third-party tools out there to accomplish this, but you are going to do it the old
fashioned way—through hard work and ingenuity.
There are several ways to accomplish this. The easiest way is to create a Data Source Name (DSN)
and use ODBC. This is not always the best way. Sometimes, you might need to add a couple of steps to
save you time in the long run.
The first way to import data into MySQL from Access is to set up a DSN. Before you can do this,
though, you need to install the Open Database Connectivity (ODBC) driver. This is a technology
designed by Microsoft to create an environment where data can be shared or dispersed easily between
applications without regard to the source of that data. ODBC acts as a layer between any application
and any database. It acts as an interpreter from application to database and from database to
application. The actual software that acts as the interpreter for the database is called a driver. Each
database has its own driver.
The ODBC interface and DSN will be covered on Day 13, "How to Use MyODBC." Today, you'll learn
how to do it by exporting data into text files and bringing those files into MySQL. This is not the
preferred way, but there may be times when you're not able to install an ODBC driver on a machine and
use a DSN to point to a MySQL database. It is at that time when doing it this way will be a good
experience to have under your belt.
The next section assumes that you have access to Microsoft Access. If you do not, read the section
anyway. There are plenty of screen shots to help you understand the techniques that are involved in
moving data from one database to another.