up vote 1 down vote favorite
share [g+] share [fb]

I have a MySQL database and have a lot of users that want to use Excel to process this data. I know how to do two things that I don't want:

  1. I know how to import a MySQL database into Excel.
  2. I know how to "open" a table via ODBC from Excel and this table is now refreshable.

The second thing is close to what I want, but I would like all the tables to be in an Excel file (probably each table in a different tab). Is there a way to do this?

Bonus points if someone can tell me how to make a custom select query for a tab.

link|improve this question
40% accept rate
Sounds simple enough. If you can code you should be able to write a PHP script to get it done in less than 50 lines of code. – typoknig Jun 7 '11 at 20:16
Do you want the users to be able to update the mysql data? – slotishtype Jun 7 '11 at 20:26
do you want the custom select query to be taken from a cell's content? Or do you want to develop the query string with controls? You could do pretty elaborate custom queries without much difficulty. slotishtype has provided a good start for the data access – datatoo Jun 8 '11 at 4:42
@typoknig - using PHP will mean the user will have to do more than just press refresh inside Excel. And can you export with PHP so that once imported into Excel all the tables will be in different tabs? @slotishtype - no, that is not the requirement @datatoo - doesn't matter, if I have to, I will develop the queries for them and then send the file to them. – duality_ Jun 9 '11 at 17:57
@duality_ I know you can export to Excel in PHP, I'm sure there is a way to put different tables in different tabs too, but I have not done it personally. Instead of "refreshing" inside Excel you would just run the PHP script again and make a new Excel sheet based off of the current data in the database. – typoknig Jun 10 '11 at 0:08
show 1 more comment
feedback

1 Answer

up vote 0 down vote

If you do not need to edit the data (in excel) the following solution will work. BTW. I tried it out. It is an updated version of this. This is on windows 7 64x, please choose the appropriate connector.

1) Download the ODBC connector from HERE.

2) Install the connector.

3) Open Administrative tools -> Data Sources and click ADD.

4) Choose the Mysql ODBC driver and click finish.

5) Add an appropriate data source name, credentials and server configuration.

6) Click Test - and you should see connection successful.

Go to Excel. This is for excel 2007.

7) Open a new workbook.

8) Click the Data tab - From other sources.

9) Click ODBC DSN as your option and click next.

10) you should see your Database connection (data source name).

11) Choose your schema and table.

12) Repeat for each table on a different worksheet.

13) To refresh click Data -> Refresh all. This will pull the data from the database including any changes.

Best of luck.

link|improve this answer
This is not what I wanted and like I wrote in the question: I already know how to do this. The problem is that I have many tables and this would be tedious. But I guess I'm gonna have to go with this... – duality_ Jun 9 '11 at 18:01
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.