Assumptions and Potential Limitations: In this post all of the setup was done using a Windows 7 OS and Microsoft Excel 2010.
Microsoft Excel is an amazing tool for manipulating and organizing data. Excel makes it easy to analyze data which helps in making intelligent and factual business decisions. Our personal, blog, business and e-commerce websites all use databases that contain goldmines of data and information. I’ve investigated and found a fairly quick and painless way to access and extract (in real time) our database tables and fields into Excel. Using the method outlined below you can easily connect to your CMS (i.e. Joomla), blogging (i.e. WordPress), E-commerce (i.e. Magento) or literally any other database that you have stored on your webserver.
(1) Determine your personal computer public IP address (note that this is usually dynamic which means it can change). Google search “What is my IP address” and you’ll get this information in seconds.
(2) Setup your webserver to allow remote SQL access from that IP address. Hostgator has a section dedicated to databases and a tool called remote MySQL. This step of the setup can be accessed through your hosting administration website.
- If you are using Hostgator, you can log into your administration site. Find the databases section and click on the remote MySQL link/icon.
- Once you click on this link, you’ll be brought to the following screen which will allow you to then input your public IP address and add it as a host. This will allow for the ODBC connection to happen from your personal computer. Note that if your dynamic public IP address changes, you will no longer be able to connect to the database and will need to add your new dynamic IP address.
(3) Download the MySQL ODBC Connector here. Be sure to select the correct operating system platform and also correct version.
(4) Setup the ODBC connection using data sources (ODBC) in the control panel (assuming you are using Windows). Be sure to have your webserver IP address handy. Again a simple search of your domain name on the web can easily find you this information or you can access this through your hosting administration account.
- Once you open up the ODBC Data Source Administrator screen, you’ll be able to add a new ODBC connection using the add option
- Once you’ve clicked the add button the Create New Data Source screen will come up at which point you will select the MySQL ODBC driver and select finish.
(5) This will bring up the configuration for the connection. Using your webserver IP address setup a new MySQL ODBC connection. Here you will use your admin webserver username and password. You will also select the default database that you want to connect to (through a drop down list). Below I’ve provided a generic example of how you would set this up.
(6) Once the ODBC connection has been added, you can then access on the data tab within Microsoft Excel the import procedure used (Microsoft Excel 2010 is being used below).
- Here you will use the import from other sources and select from Microsoft Query.
- Once you select this option, you will select the new ODBC connection that you just finished setting up through the control panel:
- Once you’ve selected your ODBC connection, the query wizard screen will be brought up and you will see a listing of all of the tables associated to the default database that was setup on your connection. You can hit the plus sign on the left hand tables and columns and this will bring up all of the fields associated to the table. Select the table and fields that you want to add and extract.
- The query wizard will walk you through the process of adding tables and fields that you would like to extract into Excel and will also allow you to choose which columns you’d like to sort by or whether or not you want to import the data into Microsoft Query or Microsoft Excel. The final step will be selecting return data to Microsoft Excel and then hitting finish. This will generate the data for the fields and filters selected during the wizard process.
Now that your ODBC connection is all setup, you can pull and import data from any tables and fields that you want from your database in the future. Congratulations!
Trackbacks/Pingbacks