- 12 Migrating a Spreadsheet to MySQL
- 12.1 Introduction
- 12.2 The Spreadsheet File
- 12.3 Converting a Spreadsheet to a Text File
- 12.4 Creating a Table with Query Browser
- 12.5 Loading the Data into a MySQL Database Table
- 12.6 Creating A Temporary Table of Members
- 12.7 Creating a Temporary Member Accreditations Table
- 12.8 The Final Tables
- 12.9 Confirming Data Integrity
- 12.10 The Production Database
- 12.11 Updating a MySQL Database from a Spreadsheet
- 12.12 The Migration Script
We want to import the data directly into a table that mirrors the structure of the spreadsheet. The sample spreadsheet has the following fields with maximum required lengths as shown:
firstname – 50
lastname – 50
certification – 10
expirydate – 10
streetaddress1 – 50
streetaddress2 – 50
city – 50
state – 2
zipcode – 10
certificationnumber – 10
If you wish to follow along, create a spreadsheet with these fields and enter some sample data as shown in the table below.
Table 12.1. Spreadsheet format
| firstname | lastname | certification | expirydate | street1 | street2 | city | state | zip | certnum |
|---|---|---|---|---|---|---|---|---|---|
| John | Doe | RAC | 10-Jan-08 | 10 Mulberry St | New York | NY | 30263 | C-12345 | |
| John | Doe | ARM-1 | 28-Feb-09 | 10 Mulberry St | New York | NY | 30263 | A-44456 | |
| Jane | Doe | DAC | 10-Dec-09 | 10 Mulberry St | New York | NY | 30263 | D-4567 | |
| Bob | Smith | RAC | 02-Jan-07 | 10 Main St | Apt 10 | Detroit | MI | 20789 | C-6785 |
The only requirements are:
Any identical combination of the
firstname,lastname, andstreet1is always understood to apply to the same individual. The same individual can appear more than once in the file. However, ensure that the certification differs.Make sure that the certification numbers are unique.
Format the date as
01-Dec-07; that is, use two digits for the day, the standard month abbreviation, and a two digit year. Use a hyphen as a separator.Don't exceed the specified field lengths.
If you don't have a spreadsheet program at hand, you can create a tab-separated or comma-separated text file to match the structure defined above. Of course, if you do this, you won't need to export the data.
