By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Importing into MySQL

P: n/a
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?
Jan 8 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Tue, 08 Jan 2008 19:02:57 +0100, Alan M Dunsmuir
<al**@moonrake.demon.co.ukwrote:
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?
xls -csv -LOAD DATA INFILE
--
Rik Wasmus
Jan 8 '08 #2

P: n/a
NC
On Jan 8, 10:02 am, Alan M Dunsmuir <a...@moonrake.demon.co.ukwrote:
>
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 ='" & A3 &
"';"

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

Cheers,
NC
Jan 9 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.