Hello Jay,
I will give you a couple ideas.. I have never tried these options to do this
job but they should work.
First option is, you can use SELECT ... INTO OUTFILE .... syntax. Excel
uses tab character as the column delimiter and new line character as the row
delimiter if you import a txt file. So you can issue a statement like:
SELECT .... INTO OUTFILE "...." FIELDS TERMINATED BY "\t" LINES
TERMINATED BY "\n";
And then import this text file into the Excel ..
See: http://www.mysql.com/doc/S/E/SELECT.html for this.
The second option I had in mind is, you can start your mysql command line
tool with the
--html option. This will return all your queries in HTML format.. Just save
your query results using tee command and import that HTML file into Excel.
See: http://www.mysql.com/doc/m/y/mysql.html for this.
Hope this helps..
Gurhan
-----Original Message-----
From: Jay Blanchard [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 01, 2002 9:01 AM
To: [EMAIL PROTECTED]
Subject: MySQL to Excel ?
Howdy,
I need to write some data out to Excel spreadsheets for some of our managers
to muddle with for projections. The query works fine...
SELECT RecordID, RecordDate, count(*) AS Quantity
FROM tblFOO
Group By RecordID, RecordDate
It returns;
+----------+------------+----------+
| RecordID | RecordDate | Quantity |
+----------+------------+----------+
| 100101 | 2002-03-21 | 6675 |
| 100101 | 2002-03-22 | 6794 |
| 100101 | 2002-03-23 | 2916 |
| 100101 | 2002-03-24 | 1215 |
| 100101 | 2002-03-25 | 6152 |
| 100101 | 2002-03-26 | 5398 |
+----------+------------+----------+
as expected (Only a subset here, there are hundreds of rows). I want to
place this data like this in an Excel table (bad ascii art example to
follow);
+------------+--------+--------+--------+
| RecordID | 100101 | 100120 | 100131 |
+------------+--------+--------+--------+
| Date | | | |
+------------+--------+--------+--------+
| 2002-03-01 | | 12 | 130101 |
+------------+--------+--------+--------+
| 2002-03-21 | 6675 | | 1113 |
+------------+--------+--------+--------+
| 2002-03-22 | 6794 | 287 | 29045 |
+------------+--------+--------+--------+
| 2002-03-23 | 2916 | 6 | 233427 |
+------------+--------+--------+--------+
RecordID along the top row, dates down the side, quantities at the
intersection that they belong. I could get an OBDC connection from Excel to
MySQL and then write a complicated macro to do this, or if posssible (which
is what I am really looking for) I would like to SELECT..INTO..OUTFILE an
Excel file with the proper formatting.
Any suggestions?
Thanks!
Jay Blanchard
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
