Jump to content

Database Query From Excel To Mysql


woesap
 Share

Recommended Posts

Hiya all,

 

This is what I want to do. I want to make een Db query from within excel on my client at home.

 

I want to connect through MySQL odbc to a mysql database of mine on the TCH server.

 

With that connection I can pull the data from my Db on my site directly into excel on my home PC.

 

BUT is it possible to remotely login to a MySql Db on my account and if so what do I enter for IP, port etc?

 

I can imagine that it is not possible due to security reasons but never ask, never know!

 

Thnx in advance!

 

Richard

Link to comment
Share on other sites

While I'm definitely NOT an expert in this sort of thing, I'm thinking it should be possible by using the same parameters (username, password, database, etc.) that you would if you were to access it from a web page using PHP. Your idea has produced some thoughts running through my head how I can get some people access to a database I'm using, so I'd be interested to see if this works.

 

Hopefully we'll get some of the more MySql-wise people to chime in on this thread.

Link to comment
Share on other sites

Steve,

 

Thnx for the reply.

I don't think it's the same as connecting from a webpage. In that case you are ON the server already and can connect though localhost.

 

Remotely that's not possible :unsure:

 

This makes it questionnable if it's even possible. I will need the server adress or IP with the correct port to do so.

Speaking abt it makes me doubt if it is possible.

 

But, we'll see if there more experienced users in this perticular field.

It indeed would be interesting because it opens much more oppurtunities to use mysql data on your home client.

 

I wanted to use this in combination with the UFM Pro version (form processor which is truly great!). Pple can submit there data on the webpage and I can process the data locally...

 

Well, lets now together patiently wait :)

Link to comment
Share on other sites

Yes, it is possible.

 

I can imagine that it is not possible due to security reasons but never ask, never know!
By default, the MySQL server will not accept connections from another machine, but you can grant permission for your home PC to connect to the MySQL server.

 

In your CPanel, go to "MySQL Databases". Near the bottom of the page is a section titled "Access Hosts". Enter your home PC's IP address then click the "Add Host" button. (If you're not sure what your home IP address is, go to whatismyip.com in your browser.)

 

This is what I want to do. I want to make een Db query from within excel on my client at home.

 

I want to connect through MySQL odbc to a mysql database of mine on the TCH server.

You did not indicate whether you've installed the MySQL Connector/ODBC (MyODBC) driver - it is required for what you want to do. If you have not installed it, you can download the MyODBC 3.51 driver from their Downloads page. (Scroll down to "Windows downloads" - I'd recommend downloading and installing the MSI installer.)

 

Once you've installed the MyODBC driver, then you need to set up a connection for the driver. In your Windows Control Panel, go into "Administrative Tools", then "Data Sources (ODBC)".

 

Click on the "System DSN" tab. On this tab, click the "Add" button. Scroll through the list of drivers displayed, select "MySQL ODBC 3.51 Driver", then click the "Finish" button.

 

You should now see the "Connector/ODBC - Add Data Source Name" window. On the "Login" tab are all of the fields you need to connect to the MySQL server.

 

Data Source Name - This is the name that Excel will display as a data source.

 

Description - Text description of database connection.

 

Server - Enter just your domain name (for example, ****).

 

User - MySQL database username.

 

Password - MySQl database password.

 

After you have entered the above fields, you can click on the "Test" button to see if the information you've entered is correct and you can make a connection to the MySQL server.

 

In the "Database" dropdown box, select the MySQL database you wish to use. (You will not be able to select a database unless you have entered the correct connection info in the above fields.)

 

The MySQL driver uses a default port of 3306, so you should not need to enter a port number. If you did need to though, an alternate port number can be specified on the "Connect Options" tab.

 

Click the "OK" button to finish setting up the connection - this will return you to the ODBC Data Source Administrator window. The connection you just created should now be visible in the "System DSN" tab. Click the "OK" button to exit ODBC Data Source Administrator.

 

Now you can send a query to the MySQL server and have the results returned in Excel. Within Excel, on the menu, click "Data", "Get External Data", "New Database Query..." (this is on Excel 2000 - hopefully it will be the same or similar on your version of Excel).

 

In the "Chose Data Source" window, you should see the MySQL connection you just set up listed on the "Databases" tab. Click on that data source then click "OK".

 

At this point, you will tell MS Query what tables, fields, etc. you want in your query. Once you've specified everything, the query will be run and the data will be returned to your Excel worksheet.

 

Hopefully the above instructions will get you pointed in the right direction. :)

Link to comment
Share on other sites

David,

 

You're the best! :)

 

Access host was just the clue i was looking for!

The rest i was quite familiar with but I think you made an excellent tutorial.

 

It works just fine and I think more people can benefit from it.

I gives you much more possibilities to process the data entered thorugh the web!

 

Imagine recalculating the data, export them and reenter in the MySql database!

(I know, you can also process online with e.g. PHP)

 

Great stuff and I am much obliged!

 

Many many thanks,

 

Richard

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...