The road to using Mysql

I wanted to import the following data from a spreadsheet into my database table:

Code:

After saving the spreadsheet as csv (comma-delimited) my first attempt was using the LOAD DATA syntax as follows:

Code:

As I’d read that this seldom worked, I was not surprised to find problems in the data inserted (although it did at least make some attempt at populating the table from the file).
So, I decided to use the Import facility in phpMyAdmin, after first selecting the necessary table. A useful option here was to specify the number of records to skip from start of file – this allowed me to skip the header record (field names) that I didn’t want to import. However the default options supplied slipped me up. The “Fields Terminated By” box has a default of “;” when we need “,”. Anyway, after correcting this by entering the comma, I was able to import the records. Only one hitch – the date columns all showed 00/00/00 – this is because MySQL uses a different date fomat to Microsoft Excel.
The solution to this? I went back into the original Excel sheet and changed the format of the date field to yyyy-mm-dd before saving as a csv file (comma delimited) and going through the process again. In the end I was able to import the data as I wanted.
Other workarounds could have included using OpenOffice to create the spreadsheet and csv file, using ODBC queries on Mysql, using Navicat, or using a php program to read and import the data. I hope to look at these options in future articles.
Incidentally, in the past when transferring data from spreadsheets into databases I’ve used the following technique. I use the Excel concatenate function to build up a sql Insert statement from the data on one line, and then copy the function down. For instance, to insert the first two fields only I would do the following in Excel and then copy the sql statements across.

Code:

This results in the following:

Code:

Remember though to be careful regarding different data types; for example you’ll have to include the apostrophe marks in the concatenation when the field is varchar.