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

Code:

orderid  customerid  orderdate  city
800    15     15/02/09  London
801    22     22/02/09  Glasgow
802    15     20/03/09  London
803    24     21/03/09  Aberdeen
804    76     13/04/09  Grimsby

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

Code:

LOAD DATA INFILE 'C:\\ordertest.csv' INTO TABLE testorders
LINES terminated BY '\n';

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.

Original post blogged on b2evolution.