Migrating information from any data store to another is rarely a one to one conversion. There are many tools out there to assist you to transfer your data to and from a data store. In this brief paper I will discuss the most convenient and accurate transfer method for your data between Microsoft Excel and MySQL database for this particular project.

With the Windows installation of MySQL, MySQL comes packaged with a MySQL Migration ToolKit. This toolkit makes migration of your data very simple and fun. Unfortunately, after multiple runs of the program, the data that is migrated if often truncated and is rendered useless because of the loss of precision in some instances. When using the toolkit to migrate data from an Microsoft Access database to MySQL, the Migration toolkit will try to map the data types used in MS Access to MySQL versions of the same data type. In doing so, some data is lost. As a result, a new method must be used to migrate data without the truncation of data.

Migrating Excel to MySQL The initial idea was to transfer the Excel spreadsheet to a MS Access table because these programs come with easy transfer options between the both. From there one could use the MySQL Migration ToolKit from Access to MySQL. This approach has the same problem. Believe it or not, data is also truncated migrating from Excel to Access and from Access to Excel.

The Better Solution.

The more reliable and more accurate approach is to use MySQL's very powerful "load data local infile" as a .csv command in the MySQL Query Browser. I will walk through the steps to perform this action. The Migration:

1) The first step in your Excel spreadsheet is to save the file as a new file with a file extension of .csv. This saves your data as is, without any data truncation.

2) In the MySQL Query Browser, create a table with the same column names as the Excel spreadsheet. Note: Be sure the columns are exactly in the same order. The create table command should look similar to this below:

DROP TABLE IF EXISTS `cacw`.`MWRD_Sed_Chem_H2O_2002`;
CREATE TABLE  `cacw`.`MWRD_Sed_Chem_H2O_2002` (
  `Street Location` varchar(255) default NULL,
  `Waterway` varchar(255) default NULL,
  `Channel Location` varchar(255) default NULL,
  `Date` varchar(50) default NULL,
  `TSW %` varchar(50) default NULL,
  `VTSW%` varchar(50) default NULL,
  `NH3_N` varchar(50) default NULL,
  `TKN` varchar(50) default NULL,
  `NO2+NO3` varchar(50) default NULL,
  `Tot Phos` varchar(50) default NULL,
  `Phenol` varchar(50) default NULL,
  `CN` varchar(50) default NULL,
  `Ag` varchar(50) default NULL,
  `As` varchar(50) default NULL,
  `Cd` varchar(50) default NULL,
  `Cr` varchar(50) default NULL,
  `Cu` varchar(50) default NULL,
  `Fe` varchar(50) default NULL,
  `Hg` varchar(50) default NULL,
  `Mn` varchar(50) default NULL,
  `Ni` varchar(50) default NULL,
  `Pb` varchar(50) default NULL,
  `Zn` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Another item to note, confirm the varchar(50) datatype is long enough to hold your data without any data truncation.

3) Create another sql command to load the .csv file into the table you just created. This command should look similar to the one below.

load data local infile 'E:\\CACW\\MWRD\\Sediment_Chemistry\\2002_Sed_Chem.csv' into table cacw.MWRD_Sed_Chem_H2O_2002
fields terminated by ','
enclosed by '"'
LINES TERMINATED BY '\n'
()

4) Remove any unneeded rows in the .csv file before executing the load data infile command. For example, your .csv file will probably contain the column names as the top row. This row is not needed, remove it. In addition, remove any unneeded rows at the end of the document. Excel often adds empty rows at the bottom without any data present.

5) It is always good practice to have copies of files and tables in case of any unknown failures that may occur.

6) Execute the load data infile command, and the result should be clean without any truncation errors.

7) Inspect the data for correctness!

We are not done yet. Next you may have to edit some information to make it useful.

Convert US date representation to ISO 8601 international standard date representation.

NOTE: instructions 8-11 pertain to date and time representations that are in different columns. DATETIME representations need to use a different conversion technique.

8) Following the successful migration of data, you are not quite done yet. The data may needs to be sorted by date. In the current format the date field is a plain string representation and needs to be converted to the MySql date representation. Within the CACW database schema use the stored-procedure USADate-To-ISODate to complete this task for a specific date representation . NOTE: Refer to the stored-procedure documentation prior to running the procedure.

The stored -procedure takes a US date string as "1/25/2000" and converts it to a ISO 8601 international standard date string as "2000-1-25"

9) Once complete, edit the table and change the data type of the date field to DATE. The result will be a valid MySql date representation that can be query and sorted.

10) Inspect the data for correctness!

11) Any columns that contain time(14:32:00) need to have the data type changed to TIME.