Automatically open MySQL query results in Excel | 8 comments | Create New Account
MySQL for Excel is an Excel Add-In that is installed and accessed from within the MS Excel’s Data tab offering a wizard-like interface arranged in an elegant yet simple way to help users browse MySQL Schemas, Tables, Views and Procedures and perform data operations against them using MS Excel as the vehicle to drive the data in and out MySQL Databases. MySQL for macOS is available in a number of different forms: Native Package Installer, which uses the native macOS installer (DMG) to walk you through the installation of MySQL. For more information, see Chapter 2, Installing MySQL on macOS Using Native Packages. You can use the package installer with macOS.
MySQL 5.1 for Mac OS X — for MacOS 10.4-10.6, Intel only, recommended Version MySQL 5.5 for Mac OS X — for MacOS 10.4-10.6, Intel only. MAMP / XAMPP on Mac OS X. MAMP and XAMPP are complete web development packages. They are designed to give developers an easy way to serve up PHP pages using Apache and MySQL right on their Mac.
We develop affordable, easy to use ODBC drivers for Mac OS, connecting Excel and FileMaker to Microsoft SQL Server, Oracle, Access, Sybase, MySQL and PostgreSQL.
Click here to return to the 'Automatically open MySQL query results in Excel' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Change 1 most shells don't like spaces, and i received an error the first time i tried this, so the line should be: pen -a /Applications/Microsoft Office X/Microsoft Excel /tmp/sql_excel.tmp Change 2 since i have 10 different databases that i routinely work on, on the same server, i changed the mysql command a bit so that command line parameter one is the database and two is the query mysql -h myhost -u userid -ppassword -e '$2' $1 > /tmp/sql_excel.tmp and of course, i changed teh echo command to spit out $2 instead of $1 Additional option. I play with mysql data so often that i aliased the command and some common queries in my .cshrc file
005{00000000to the other 2 posts that popped up after this. I should add that, i have my MySQL connections tunneled through SSL. AND the user that i login in as in MySQL for this, has SELECT only permissions. no DELETE no UPDATE, and DEFINITELY no DROP.
This should be OK if it's meant to be used as a stand-alone machine and not at the server-level. There are at least 2 security-related problems with this script: 1) The password to the MySQL database is written in plaintext and the script is set to world-readable. A slightly better idea is to put it in ~/bin and set it to execute only. Of course if connecting over a network the MySQL password would have to be encrypted as well. 2) There is no check on /tmp/sql_excel.tmp. Someone could stick a macro virus in there or make use of race conditions to change the legitimate file with a doctored one (previously proven to work on Linux, not sure on OS X though). Again, it's safer to do stuff in your own home directory.
Additional point. The SQL commands are not validated. Someone could just type this: sql_excel 'DROP DATABASE xxx' And it would get executed.
Any possibility of using some variant of this tip with FileMaker? Why can't we just have simple, usable ODBC connectivity like the rest of the world? Sure I know SOME ODBC support is built-in....but thats all theory - I have never heard of anyone actually getting it to work!
Mysql For Mac
This may work better for you if you include double quotes (') in the lines where you have to call directories. open -a /Applications/Microsoft Office X/Microsoft Excel /tmp/sql_excel.tmp becomes open -a /Applications/'Microsoft Office X'/'Microsoft Excel' /tmp/sql_excel.tmp
As you may or may not be aware, Micro$oft released a Query tool for Excel, but it is lacking an ODBC client!!!! Losers!!! The Windows version has one! Anyhow, I'm a little miffed here... I got the one working from OpenLink software that works well as in I can run SQL queries to my MySQL database running on my Linux server, but it's $495!!!! What's up with that?!!! (I'm running the 30 day demo) Anyhow, I'm in the process now of downloading OpenOffice (ie. Sun's StarOffice) crossing my fingers that it has an ODBC client with it. If anyone knows of a Freeware ODBC driver for OSX, that would make my day!
http://www.macosxhints.com/article.php?story=2002121206540038 Here's the solution!!! Works like a charm and saves you $495!!!
Latest Version:
phpMyAdmin 5.0.1 LATEST
Requirements:
Mac OS X 10.1 or later
Author / Product:
phpMyAdmin devel team / phpMyAdmin for Mac
Old Versions:
Filename:
phpMyAdmin-5.0.1-all-languages.zip
MD5 Checksum:
652e483660c6e5abc2669b81155ec52f
Details:
phpMyAdmin for Mac 2020 full offline installer setup for Mac
phpMyAdmin for Mac is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. phpMyAdmin supports a wide range of operations on MySQL, MariaDB and Drizzle. Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, etc) can be performed via the user interface, while you still have the ability to directly execute any SQL statement. Download phpMyAdmin for macOS is a PHP script meant for giving users the ability to interact with their MySQL databases. Features and Highlights
Intuitive web interface
Support for most MySQL features:
Browse and drop databases, tables, views, fields and indexes
Create, copy, drop, rename and alter databases, tables, fields and indexes
Maintenance server, databases and tables, with proposals on server configuration
Execute, edit and bookmark any SQL-statement, even batch-queries
Manage MySQL users and privileges
Manage stored procedures and triggers
Import data from CSV and SQL
Export data to various formats: CSV, SQL, XML, PDF, ISO/IEC 26300 - OpenDocument Text and Spreadsheet, Word, Excel, LATEX and others
Administering multiple servers
Creating PDF graphics of your database layout
Creating complex queries using Query-by-example (QBE)
Searching globally in a database or a subset of it
Transforming stored data into any format using a set of predefined functions, like displaying BLOB-data as image or download-link
And much more...
Note: Requires MySQL and PHP. Also Available: Download phpMyAdmin for Windows