Importing from Excel into MySQL

A question from comp.lang.php:

A client has sent me a (Windows) Excel Spreadsheet (.xls) file containing a block of data he wants included as a table in a MySQL database in a (Linux-based) PHP/MySQL Web application I’m developing for him.

I have both Windows and Linux available on my own machine.

What is the recommended route to get this .xls file into MySQL?

Option One

  1. Format all dates in the spreadsheet as yyyy-mm-dd.
  2. Save the spreadsheet as CSV (comma-separated variables) file or tab-delimited text file.
  3. Import CSV or text into MySQL using LOAD DATA INFILE query (be sure to specify proper delimiters and enclosures).

Option Two

  1. In your spreadsheet, construct an INSERT query for the first record, something like this:
    = “INSERT INTO myTable SET id =” & A2 & “, description ='” & B2 & “‘;”
  2. Use Copy and Paste to construct similar queries for all other records.
  3. Copy the column of queries to Windows Clipboard and paste the queries into your MySQL client program. Alternatively, paste the queries into Notepad, save the file as, say, mydata.sql and run the queries from command line:
      mysql [your usual options] < mydata.sql

* * * * *

Leave a Reply

Your email address will not be published. Required fields are marked *