Re: Dumping MySQL result set to a spreadsheet
Neale Banks wrote: Should we presume you haven't noticed the, admittedly not widely publicised, Database Access features of OpenOffice.org? You can use odbc or jdbc (I can only vouch for the latter, on Linux) to connect to the mysql server from within OOo. Should work just fine on your XP box. To get started, open a new spreadseet (or any other kind of document) and select View-Data Sources (or F4). Once you've defined the database connection and the query your original question reduces to dragging the query result and dropping it into the spreadsheet :-) Hmmm, I've never quite been able to get there (00o 1.1.0): I'm able to connect to MySQL (easily) but I get a selection of tables for the wrong database. So close! Any hints? A while back I actually splurged and spent $5.50 on a Perl program someone wrote to do this [http://www.churm.com/konvertr/index.php] and it works just fine - writing to Excel. Very quick, beats point and click. I guess I should have written something like that myself, but it's hard to beat 5 bucks, and I just could not write such a program fast enough to make doing it myself a better deal (and if one writes all the tools along the way, one may lose sight of the goal). Eric Pederson Eric at zomething dot com P.S. I also wanted something to quick summarize my field definitions across the database for review, so I made a little script in Python which produces a HTML summary of the table definitions. I find it helpful documentation. http://www.zomething.com/describe_MySQL.htm You need Python on your machine to run it. http://www.python.org Feedback invited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping MySQL result set to a spreadsheet
SELECT rest of query into OUTFILE some place the mysql user can write to Should get you a tab delimited file there are more options for using different delimiters and field encapsulations, etc. Its in the manual. curtis On Sun, 29 Feb 2004, Joshua Beall wrote: Hi All, I am wondering if anyone can point me to a utility that will let me dump the result of a MySQL query to a file I can open in MS Excel or (preferably) OpenOffice.org Calc. phpMyAdmin lets you do this if you want to dump a SELECT * FROM table, but if I want to fine tune it I cannot (unless I am missing something) MySQL CC lets me save the result of a query to a test file right click-save results, but this format does not quite conform to either Excel or Calc's text format. I could fix it by hand, but before I would do that, I think I would write a PHP script that would generate the right output. But before I spend any time working on doing that, I am wondering if anyone knows of a way to do this already? I am running MySQL 4.0.12-standard on Mandrake 9.2 on the server, and my workstation is an XP Pro box. I use MySQL CC 0.9.1-beta and phpMyAdmin 2.5.0, but am completely willing to upgrade more recent versions of either of these tools, or try a new one. Thanks for any feedback! Sincerely, -Josh p.s. How do I use my newsreader to post to these mailing lists? I see that news.gmane.org seems to carry the mysql lists, but it would not let me post, telling me (even after I was subscribed to the list with the same email address I use in my newsreader): Outlook Express could not post your message. Subject 'Dumping MySQL result set to a spreadsheet', Account: 'news.gmane.org', Server: 'news.gmane.org', Protocol: NNTP, Server Response: '441 You are not allowed to approve postings', Port: 119, Secure(SSL): No, Server Error: 441, Error Number: 0x800CCCA9 -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping MySQL result set to a spreadsheet
At 17:45 -0500 2/29/04, Joshua Beall wrote: Hi All, I am wondering if anyone can point me to a utility that will let me dump the result of a MySQL query to a file I can open in MS Excel or (preferably) OpenOffice.org Calc. phpMyAdmin lets you do this if you want to dump a SELECT * FROM table, but if I want to fine tune it I cannot (unless I am missing something) MySQL CC lets me save the result of a query to a test file right click-save results, but this format does not quite conform to either Excel or Calc's text format. I could fix it by hand, but before I would do that, I think I would write a PHP script that would generate the right output. But before I spend any time working on doing that, I am wondering if anyone knows of a way to do this already? I am running MySQL 4.0.12-standard on Mandrake 9.2 on the server, and my workstation is an XP Pro box. I use MySQL CC 0.9.1-beta and phpMyAdmin 2.5.0, but am completely willing to upgrade more recent versions of either of these tools, or try a new one. If you're willing to use Perl, you can use the Spreadsheet::ParseExcel::Simple and Spreadsheet::WriteExcel::FromDB modules. An example that shows how to dump a MySQL table in Excel format is in the MySQL Cookbook recipes distribution: http://www.kitebird.com/mysql-cookbook/ Get the distribution, unpack it, and look in the transfer directory for the mysql_to_excel.pl script. The from_excel.pl and to_excel.pl scripts in the directory convert between Excel and tab-delimited data. For example, if you dump an arbitrary query result, you can convert it to a spreadsheet with to_excel.pl. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping MySQL result set to a spreadsheet
On Sun, 29 Feb 2004, Joshua Beall wrote: I am wondering if anyone can point me to a utility that will let me dump the result of a MySQL query to a file I can open in MS Excel or (preferably) OpenOffice.org Calc. [...] But before I spend any time working on doing that, I am wondering if anyone knows of a way to do this already? I am running MySQL 4.0.12-standard on Mandrake 9.2 on the server, and my workstation is an XP Pro box. I use MySQL CC 0.9.1-beta and phpMyAdmin 2.5.0, but am completely willing to upgrade more recent versions of either of these tools, or try a new one. Should we presume you haven't noticed the, admittedly not widely publicised, Database Access features of OpenOffice.org? You can use odbc or jdbc (I can only vouch for the latter, on Linux) to connect to the mysql server from within OOo. Should work just fine on your XP box. To get started, open a new spreadseet (or any other kind of document) and select View-Data Sources (or F4). Once you've defined the database connection and the query your original question reduces to dragging the query result and dropping it into the spreadsheet :-) HTH, Neale. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
