|Something a bit more Advanced: Linking to an Excel file |
| ||On many occasions when conducting on-site mentoring with clients, they will have data stored in an Excel file (eg. Sales Totals or Customer Counts) that is used for reporting. This type of data changes frequently and is often used for reporting and visualisation. If the organisation's Analyst has generated a thematic map in MapInfo Professional based on the Excel file, data changing has typically will lead to them having to re import the revised data and regenerate the thematic map to ensure information shared is as up to date as possible. |
A more effective and efficient way to handle these types of data updates to Excel data, is to treat the Excel file as though it is a database management system (DBMS) file. By opening the Excel file through the open database connectivity (ODBC) capability of MapInfo Professional, the data contained in the file can be refreshed without having to be re-imported and outputs such as thematic maps are refreshed automatically.
Preparing the Excel file
In this example, the Excel file contains two fields, a Postcode (to be used as the relation between the data and the geographic boundary layer) and a column containing SalesTotal.
It is important to create a "Name" in the Excel file for the data to be used. To do this, select the content in Excel to be used and then either type a "Name" for the selected data in the Name Box just above Column Titles A&B or by going to Formulas->Define Name and entering a name into the Name field in the New Name dialog. In our example the name entered is Test123 (see Figure 1). Save the Excel file to retain the changes.
Figure 1: Select and name the data to be used in the DBMS table link.
Opening in MapInfo Professional
File->Open DBMS Connection. The "Open DBMS Connection" dialog box appears. Select ODBC and click on New. Click on Machine Data Source tab heading, select Excel files and click on OK. The "Select Workbook" dialog will appear. Navigate to the drive and directory as required and select the relevant file. Click OK. The DBMS connection should now be established.
You can open the data selected and named in the Excel file now that the DBMS connection has been created. This can be done by using File->Open and navigating to the Excel file which will appear under the "Files of type" drop-down. Alternatively, if you have the DBMS toolbar visible, clicking on the Open DBMS Table icon ( ) will deliver the same "Select One or More Tables to Open" dialog.
Choose the Database to use and the relevant Database Table. Make sure Linked is selected in the "Common Options" area. Specify the desired drive and directory location where you want the linked table to be saved (see Figure 2). Click OK.
Figure 2: The dialog in MapInfo Professional to access the named data from Excel.
A warning note will display advising that the opened table cannot be edited. Click OK and a browser displaying the data will appear. The data in the Excel table is ready to use.
Let's assume that a thematic map based on the SalesTotal field in our example is created against the Postcode boundary table. Once this has been created, any change made to the original Excel file is able to be reflected in the MapInfo Professional use of the data eg. the thematic map.
For our example, the Postcode 2127 will have the SalesTotal value changed from 281 to 2,281 in the original Excel file. Once this is done, click on the Refresh DBMS Table icon ( ) on the DBMS toolbar and the Postcode 2127 will change in display from being in the 100 to 500 range to the More than 2,000 range (see Figure 3).
Figure 3: By linking the data, changes made in Excel can be easily updated in the MapInfo Professional application of the data - in this case a thematic map.
Article by Tony Maber, Senior Pre-Sales Consultant, Pitney Bowes Software.
When not providing consulting services to PBS clients or writing articles for "The MapInfo Professional" journal, Tony enjoys spending time with his family and playing baseball with the Comets Baseball Club.