I maintain a searcheable MySQL database of member’s coordinates on the Nepean Sailing Club web site that is sent to me by email as a Microsoft Excel export from an Access database. Here is how I go about it…

The database is called bluebookdb, and it contains one table, bluebook. I will use these two names throughout.

On my laptop

The following assumes that MySQL community server is running on your system. if not install and start it.

if the bluebookdb database does not exist, create it with
mysql -uroot -p (and enter the DB root password or whatever account you use with full privileges)
mysql> create database bluebookdb;

if the bluebook table does not exist, create with:
mysql -uroot -p (and enter the DB root password or whatever account you use with full privileges)
mysql> select bluebookdb;
mysql> CREATE TABLE `bluebook` (`sortkey` varchar(45) character set latin1 NOT NULL); (here you would use your own table name and first key)

Now, I

1. save the excel file as CSV file, comma separated, say in c:\www\newnsc\nsc_members\bluebook\dbase\bluebook.csv
2. open a command Prompt Window, and set the directory where my csv file is, c:\www\newnsc\nsc_members\bluebook\dbase
3. in the command Prompt, import this CSV file in the local MySQL Database (bluebookdb) with MySQLImport command:
   mysqlimport -d -i -v -l -L -uroot -p --fields-optionally-enclosed-by="\"" --fields-terminated-by="," --ignore-lines=1 bluebookdb bluebook.csv
4. at the command Prompt, export that new table with the mySqlDump command as an executable SQL command set:
   mysqldump --opt --user=root --password bluebookdb bluebook > bluebook.sql
   NOTE: to RECREATE the Database as well as the table, add the switch –create-options after mysqldump

On the NSC Web Server

Finally, I Log in the NSC unix server, and use the phpmyadmin tool to load the dumped sql file into the bluebook table – browse for the sql file on your pc, use all the defaults, including ’0′ line skipped. If you do not have phpmadmin, use whatever MySQL tool you have to import sql files.

…and that is it. OK, it is not quite 100 steps, but it sure feels like it the first time!