converting excel spreadsheet to MySql Database

I have a Horse Racing Database that has the results for all handicap races for the 2010 flat season. The spreadsheet has now got too big and I want to convert it to a MySQL Databse. I have looked at many sites about normalizing data and database structures but I just can't work out what goes where, and what are PRIMARY KEYS,FOREIGN KEYS ETC I have over 30000 lines in the spread开发者_Go百科sheet. the Column headings are :-

RACE_NO,DATE,COURSE,R_TIME,AGE,FURS,CLASS,PRIZE,RAN,Go,BHB,WA,AA,POS,DRW,BTN,HORSE,WGT,SP,TBTN,PPL,LGTHS,BHB,BHBADJ,BEYER

most of the columns are obvious, the following explains the less obvious BHB is the class of race,WA and AA are weight allowances for age and weight,TBTN is total distance beaten,PPL is Pounds per length, the last 4 are ratings.

I managed to export into MySQL as a flat file by saving the spreadsheet as a comma delimited file but I need to structure the data into a normalized state with the proper KEYS.

I would appreciate any advice

many thyanks

Davey H

To do this in the past, I've done it in several steps...

1. Import your Excel spreadsheet into Microsoft Access
2. Import your Microsoft Access database into MySQL using the MySQL Workbench (previously MySQL GUI Tools + MySQL Migration Toolkit)

It's a bit disjointed, but it usually works pretty well and saves me time in the long run.

It's kind of an involved question, and it would be difficult to give you a precise answer without knowing a little bit more about your system, but I can try and give you a high level overview of how Relational Database Mangement Systems (RDBMS's) are structured.

A primary key is some identifier for a particular record - usually it is unique to that record. In this case, your RACE_NO column might be a suitable primary key. That way, you can identify every race by its unique number.

Foreign keys are numbers that describe the relationships between other objects/tables in your database. For example, you may want to create a table that lists all the different classes of races. Each record in that table would have a primary key, unique to that class. If you wanted to indicate in your "races" table which class each race was, you might have a column for each record called class_id. The value of that column would be populated with primary keys from the "classes" table. You can then use join operations to bring all the information together into one view.

For more on data structures and mysql, I suggest the W3C tutorials on SQL: http://www.w3schools.com/sql/sql_intro.asp

Before anything else, You need to define your data: You have to fit every column into a value space known to MySQL.

• Numeric value http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
• Textual value http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html
• Date/Time value http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html