Creating a table to match the fields as described in Section 12.2, “The Spreadsheet File” is a fairly straightforward matter. For importing the data our principal concern is to get the right information in the right fields without truncating data. By treating all fields as VARCHAR we can keep things simple and only need to worry about the order of the fields and their length.

As promised we'll use the MySQL Query Browser until we're ready to create a database dump. Query Browser is a fairly intuitive tool but for a quick overview find the documentation online at http://dev.mysql.com/doc/.

Start up Query Browser and enter your credentials and the server hostname and port — we haven't created a database yet so don't worry about the Default Schema text box. When the application opens, you'll find a list of schemata (databases) on the right. The cursor should be active in the text area at the top of the screen. This text area is used for entering queries, which are executed using the Execute button on the right. If a result set is returned, it shows in the main area in the center of the screen.

The first thing to do is create a database. Make sure that the Schemata tab on the right is selected, right click anywhere in this window, and choose the Create Schema option from the pop-up menu. Name the database association. To refresh the databases shown in the Schemata window, right click in this window and choose the refresh menu option. Next open a script window — we'll use this window as a scratch pad to save copies of the queries we create. Open a script tab by choosing the New Script Tab option from the File menu. After doing this two tabs, one labeled Resultset1 and the other New Script, should be visible on the left below the tool bar.

To create a table, right click the association database in the Schemata panel and choose Create Table from the pop-up menu.

This opens the table editor, in the default view with the Columns and Indices tab active. Enter the name alldata in the text box at the top of the table editor. Refer to the values shown in Section 12.2, “The Spreadsheet File”, enter a name for each column, choose VARCHAR as the data type, and specify a field length. You needn't worry about making any other changes at this point. After all, the alldata table is only temporary.

When you are finished, use the Apply Changes button. This button opens a dialog box showing the SQL code that will execute. Before executing this code, copy it and paste it into the script window. The code should look something like this:

CREATE TABLE `alldata` (
  `lastname` VARCHAR(50) NOT NULL,
  `firstname` VARCHAR(50) NOT NULL,
  `certification` VARCHAR(10) NOT NULL,
  `expirydate` VARCHAR(10) NOT NULL,
  `streetaddress1` VARCHAR(50) NOT NULL,
  `streetaddress2` VARCHAR(50) NOT NULL,
  `city` VARCHAR(50) NOT NULL,
  `state` VARCHAR(2) NOT NULL,
  `zipcode` VARCHAR(10) NOT NULL,
  `certificationnumber` VARCHAR(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Right click the association database in the Schemata pane and choose the Refresh Schemata option. The newly created table should appear beneath the association database, ready for imported data.