Importing into MySQL

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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  

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

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

Re: Importing into MySQL

On Tue, 08 Jan 2008 19:02:57 +0100, Alan M Dunsmuir  

Quoted text here. Click to load it

xls -> csv -> LOAD DATA INFILE
Rik Wasmus

Re: Importing into MySQL

Quoted text here. Click to load it

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 ='" & A3 &

2. Use Copy and Paste to construct similar queries for all other

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


Site Timeline