Can I link Excel to mySQL database?

[pmcinvest]

I'm currently exporting a text file to which my Excel spreadsheet is linked. I'd like to set up a link to the database that gives me realtime data in Excel. Can't find any information about how that is done.

Can it be done? How is it done? Should it be done?

Thanks

EDIT: Some discussion follows about 'how' to do this. At this date (12/22/06) I am still unsuccessful making an ODBC connection. I did find another thread that answers my third question "Should it be done?" and that answer is "NO". This thread, http://www.bluehostforum.com/showthread.php?t=688 , states that SSH is the better way to connect remotely for security and efficiency.

EDIT: Found a site with programs to link mySQL to Access and Excel (and many other tools too). see http://www.bluehostforum.com/showthread.php?t=4278

[aceofspades]

Yes CSV it than upload it through phpMyAdmin. Pretty simple.

Excel has an option to save as CSV

[areidmtm]

I think that he wants it to go the other way, in which you'll want to set up an MySQL ODBC connection to your databases. I'm not sure if excel can do that but I know that you can do that in access, which makes me think that excel can do it to.

Search Google for MySQL ODBC to Excel (http://www.google.com/search?q=MySQL+ODBC+to+Excel)

[pmcinvest]

Thanks areidmtm, that's exactly what I want to do. I already have a driver for myodbc but I installed version 5.0 to make sure I had the latest.

My problem is as follows:
I go to Excel2000, Data, Get External Data, Choose Data Source, SQLite UTF-8 Datasource*, Browse, My Network Places, <my BH IP>. I can see my entire BH directory tree but can't find my database.

I found the Connector/ODBC in Control Panel/Administrative Tools but am not sure how to fill it out other than user and password.

[areidmtm]

make sure that the username trying to access the database has the propor rights set in cPanel.

cPanel >> MySQL database, then add the user to the DB

[pmcinvest]

Ok, we seem to be getting closer. I corrected the username and password (they weren't the same as my un/pw at bluehost). The problem now seems to be the server name. I've used default(host), my dedicated IP address and my .com address but all get "cannot connect" error.

[felgall]

Have you asked Bluehost support to allow access to your database from other than localhost? They don't turn on that access unless you request it.

[pmcinvest]

aah ha! That must be it. I'll ask them, thank you.

[pmcinvest]

To make a connection to your database on your server you will have to whitelist your IP address (go to CPanel->Update Billing Info->Update Now->and in the "Customer Preferences" section click edit preferences, and then add your IP and click Save. You will then be able to connect to your database with the username and password that you specified for the database in CPanel->MySQL Databases.

[pmcinvest]

Ok, I've whitelisted my IP but still cannot get an ODBC link from Excel to the db. Anyone have a clue what I'm doing wrong? Is the database suppose to be a file that I can see in the directory? I am seeing the directory but no db file.

[pmcinvest]

I'm now making contact with the database, if not actually connecting to it. I'm getting an error message

1045 Access denied for user 'my username'@'adsl-my IP.dsl.irvnca.pacbell.net'
[using password: YES]

even though I set up the correct password. The only IP bluehost will let me whitelist is "my IP/32" with no way to customize the address.

Anyone know what's going on?

Am I correct in assuming that the db is in my directory? Is it actually located at some other IP address unrelated to my web site? From what I can tell, my db is in URL/var/lib/mysql/. That subdirectory does not show in the directory tree of My Network Places or FTP. And, yes, my firewall trusts my IP.

Uh, do I have to activate SSH? Tech support didn't mention that in their response but I suspect that is what's going on.

[pmcinvest]

No one on this forum and no one at tech support seems to know why I'm not connecting to my db. I sent tech support screen shots of the steps I'm taking and haven't heard from them in over 24 hours.

So far I've verified that I have to connect to the IP address of my site on port 3306. I've tried the username and database name with the prefix and without the prefix. Nothing will connect.

I do get an error message of [using password: YES]
Does this mean 'using the password "YES" ' or does it mean 'Yes, using a password'?

[Pethens]

One thing you didn't mention doing which I think is necessary: after going to cPanel > Mysql Databases, at the bottom of the form there is a section called "
Access Hosts", you need to add the connecting machine from your isp here, ie "adsl-my IP.dsl.irvnca.pacbell.net". Or just set it to % for testing purposes.

This may help but in my case it just hangs after I get past the access denied message, so I would be interested in hearing from anyone who has connected successfully.

Regards,

Stephen

[pmcinvest]

Pethens, Thank you. I was missing that step of adding an Access Host.

After adding my IP as an Access Host with all the text before and after it, I discovered a second IP address available in Billing Info that wasn't there before and it was labeled as my Class C IP. I added it to the whitelist and tried again. Still unable to connect.

I then experimented with the % wildcard, but still no success. I then tried adding just the IP but no luck. Finally I added the first two numbers of the IP and wildcards for the second two numbers in the format "xxx.xxx.%.%"

Here's the 'success' part - I'm now connecting to the database but getting an error message that "This datasource contains no visible tables".

Has anyone out there successfully made a remote connection to their database?