MySQL / Excel

[DougStroud]

I am working on connecting my online webstore database, which is in a MySQL database to Excel.
I have no background in MySQL, so I am learning this as I go in order to get the data into Excel.

In order to connect Excel to MySQL I needed to install MySQL and install the DB file. I do not know how to do this, so if there are any MySQL aficionados out there, please lend a hand :wink:
I have been looking around for reference material on MySQL and have not found the exact information I am looking for.

I have installed MySQL on my home computer and it seems to be operating correctly.
I have the backed up MySQL file from my server downloaded on my machine, I now need to insert this DB file into a new Database that I created, named Online_Store.
What would be the command, or procedure to accomplish this?

Thanks,

Doug S.

 

[ChrisM]

To get MySQL and Excel talking, you need to use an ODBC connection. There are 2 ways to do that:

1. Download the MYSQL ODBC driver from the MySQL website and install it on all machines that need to pull data in to excel.

2. There is a website, called (something like) VB-MySQL.com. It has some custom controls for connecting to MySQL without using the regular ODBC driver. Based on your post, I think this would be beyond your technical ability.

I would stick with option 1.

Once it's installed, you now have 2 MORE options. Create a DSN connection to your database or hardcode the connection information using ADO/MS Query.

It's easier to create the DSN connection, but again, you'll need to do this on each machine that uses the database. To create, go to Control Panel, choose Administrative Tools, then Data Sources. Add a User DSN, using the MySQL driver. You'll need db name, login, and pw at this time. You can test the connection as well.

Once that's done, go to Excel, go to Data, Import External Data, New Database Query, then in the window that pops up you should see your DSN connection. If you don't see it, navigate your harddrive to find where you stored it. It's just like any other file.

Good luck!!!

 

[DougStroud]

Chris,
Thanks for the reply. All of that, of which you suggested has been accomplished. Prior to linking the database, I need to get the MySQL data file inserted into the database, structure and data.
I am looking for the command line syntax to perform this function. I posted this here, thinking someone might know how to achieve this.

If you are experienced here, that would be great. If not, no worries, I have a few posts on different MySQL forums.

Doug

 

[ChrisM]

there are a couple GUI programs that should let you do this fairly easily. Can't quite remember the names, MySQL Control Center perhaps...