Introduction
Microsoft Excel for the Mac includes the ability to import live data from a database using an ODBC connection. This tutorial will describe how you can import sales data from an on-line sales database into your Excel spreadsheet. Once the data is in the spreadsheet, you can use Excel to perform further analysis of your sale data.
These instructions assume you have already downloaded and installed the appropriate ODBC driver for your database and you have configured a DSN (data source name). If you have not yet purchased a license key, you will only see the first 3 rows from any database query.
Please contact us at support@actualtech.com if you have any questions or suggestions regarding these instructions.
Connecting to your sales database
Let's suppose you want to see how many products you are selling, according to category and country. There are many different shopping cart databases you might be using (such as osCommerce), but they all have similar tables defined for storing customer, product, and sales information. We will use the NorthWind example database, whose defined tables should map easily to your own database tables. Please contact us if you have any problems using the tables in your database.
In this example, we will start with a new spreadsheet, although you could import your data into an existing spreadheet if you wanted.
Start Microsoft Excel and select File->New from the menu. Position your cursor in cell A1, and select the Data->Get Data->From Database (Microsoft Query)
When the "iODBC Data Source Chooser" dialog is displayed, select the DSN you configured previously and press OK:
After you enter your database's user ID and password (if needed), you will see the main window for Microsoft Query. MS Query is included with Microsoft Office for Mac, and is used specify the information you want imported into Excel. The Window will include a list of tables defined in your database. Clicking on one of the table names will cause a simple SELECT statement to be displayed:
We can change the SQL statement to be as simple or complex as we need. Here is an example of a multi-table JOIN statement that displays information from related tables. Click on the Run button to display the results of the query:
Once we're satisfied with our query, we press the "Return Data" button in the lower right corner of the window to import our results into Excel. Excel will then prompt us for the location to place the results:
We'll just accept the default and press OK. The results are then imported into the spreadsheet:
Once the data is in your spreadsheet, you can perform all the functions on the data supported by Excel, such as summation. You can also re-run the query by right-clicking on your results and selecting Refresh Data:
For more information
To learn more about the Excel database connectivity, please visit our MS Office Resource Center.
