Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

That's my first question here, i'll try to be not too noobee.

I have a MySQL database in which I have several tables on a distant server, usual stuff.

I have a lot users, let's say between 50 and 400, that 'd like to access the database through views in Excel. I would have define first in select clauses those views.

I have created an ODBC connection and it works just fine .... on my laptop only! I have my select clauses from mySQL DB in Excel. When I send the document to anybody they get rejected because the component is not defined.

My users cannot afford to go through the whole setup of the component to use the files afterwards.

I'm looking for a work around where my users just have to open the excel file and hit refresh all to get their data

We all use Excel 2010.

Thank you for your help Pierre-Louis

share|improve this question
    
This should work fine, I have done this the same way. Do you get an error message? –  Dave Rook Jul 18 '12 at 13:48
    
Installing an ODBC component makes it work just fine ... on my laptop only. They do get an error message, something about the component missing which is, I have to admit, obvious. –  Pierre-Louis Jolicard Jul 19 '12 at 5:54
add comment

2 Answers

up vote 1 down vote accepted

The way I've got round this same issue in the past is to make use of a friendly web service.

If you can get access to a web server on the network, set up a simple query service using your favourite web scripting language (PHP, PERL, Python, JavaScript/Node.js) that returns a tabular view of the data.

That way you no longer need an ODBC connection on each PC. Instead you do a web query which is embedded in the spreadsheet itself. Web queries can also contain parameters based on other cells so it is easy to create personalised queries for each person running the spreadsheet.


To create a web service like this, you have a couple of options. You could pay for a hosted web site on the Internet. Even a few dollars per year will get you a PHP driven web host. In this case, jump down to step 4.

Alternatively, if having your data exposed to the internet is not feasible, you'll need to set up your own. Here are the basics:

1) You will need a web server. It is very easy to set one up if you have a PC on your network - there are loads of help sites out there for doing this. Use Apache on Ubuntu or Debian Linux as this is the simplest to set up with the best community support. Alternatively, set up a simple Windows server with IIS.

2) You need a scripting language. PHP is recommended as the simplest entry point and is very well supported. Again there is plenty of help out there. If using Ubuntu or Debian, Apache and PHP are in the software repositories: apt-get install apache2 php5 should do the necessary. Here I am assuming that MySQL is already set up somewhere.

3) You need to set up a PHP driven web site. Again, plenty of help out there, I wont repeat all of that here

Before continuing, one quick asside. You can actually do all of this from a single workstation if you wish and if your needs are simple as they are here. Have a look at EasyPHP which gives a very simple way to set up everything you need. It is designed for development PCs but, as long as your security needs are simple, it is fine for this. For similar alternatives, search for "WAMP".

4) Now you need to use PHP to query your MySQL tables. Whilst you could write this yourself, it would mean learning PHP! Not that hard but certainly time consuming. So I'll recommend using a library. Check out one of: DaDaBik or PHPmyEdit. Either of these can be used to build simple interfaces to database tables and would be suitable for this task. I've used PHPmyEdit to build a temporary interface to a national system registration application for a very large public body.

5) You should be looking to end up with a web page that returns an HTML table with the data you need for your spreadsheet. Now, in the template spreadsheet, you insert a web query pointing at that page, select the table of results. The query is embedded in the spreadsheet, anyone with access to that web page can now use the data.

Finally, if you need to allow filtering, adjust the web page to allow query parameters to be passed, you'll need to check out the documentation of the library you chose but it shouldn't be that difficult to work out. query parameters can be added into the web query in the spreadsheet.

share|improve this answer
    
That would work ... if I had any knowledge in one of those techno. Do you have examples of what you did that a noob like me could copy? –  Pierre-Louis Jolicard Jul 18 '12 at 15:11
    
Hmm, that's not going to be so easy. Firstly you will need a web server that you can add some scripts to - do you have that? Also, where are the users? On an internal network or over the Internet? And how sensitive is the data? –  Julian Knight Jul 18 '12 at 15:20
    
The excel file will be distributed through email or posted our internal SharePoint besides the data is of very little interest outside our organization. I don't bother too much about security at this stage. Finding a web server though can be a little more challenging for me. –  Pierre-Louis Jolicard Jul 18 '12 at 15:36
    
Bingo! Sharepoint - do you have someone supporting SharePoint? They should be able to create a web part that connects to the SQL data and returns it as a SharePoint list. You can connect to the list from Excel. When showing the list in SharePoint (in IE), select edit in Excel, this will automatically open Excel and create a web query. –  Julian Knight Jul 18 '12 at 15:56
    
I can look for something like that, thank you, I'll keep you posted –  Pierre-Louis Jolicard Jul 18 '12 at 16:10
show 3 more comments

Create a copy of the Excel file and send to users. Rememeber though, that if you have the select statement in the Excel document, it is editable, and the password information is in plain text.

share|improve this answer
    
I tried that but when they try to refresh they get asked for the ODBC component –  Pierre-Louis Jolicard Jul 18 '12 at 15:09
    
Can you provide exact error message? Are you saying the computer can't use ODBC or that it is a fault with Excel? –  Dave Rook Jul 19 '12 at 7:55
    
My computer is using the ODBC component I configured well. I sent the excel to someonelse who didn't configure anything and she got rejected with: [Microsoft][OBDC Driver Manager] Data source name not found and no default driver specified –  Pierre-Louis Jolicard Jul 19 '12 at 8:56
    
I would assume the issue is that the driver is missing more than anything else. Can you confirm on client PC by going to (XP instructions) Control Panel -> Administrative tools -> Data Sources. Do you have "Excel Files" under User DSN? If not, click on Add and see if you can add it. I know not ideal but may be more manageable and future proof (you may even be able to write a script which does this at one point). –  Dave Rook Jul 19 '12 at 9:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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