1. In the ' Data ' tab, select ' From Other Sources '> ' From Data Connection Wizard ' in the ' Get External Data ' box:
As you can see in the image above, Excel 2007 supports importing data from various sources (sql server files, sql server analysis services, xml, Microsoft query) into the worksheet. But in the context of this article, we will import the data of DB2 9, which is not defined in Excel 2007, so you need to select ' From Data Connection Wizard '.
2. The ' Data Connection Wizard ' window opens, select ' Other / Advanced ' and click ' Next '.
3. Open the ' Data Link Properties ' window, select ' IBM OLE DB Provider for DB2 ' in ' OLE DB provider (s) ' and click ' Next '.
When you install DB2 9 client on your machine, the corresponding 'OLE DB provider' will also be installed automatically. And Excel 2007 will use this component to retrieve data from the DB2 database.
The illustration in step 2 shows the method for you to choose the type of ' ODBC DSN '. When the data source is entered as a DB2 database, you can choose the type of database configuration that is the user (user), system (system) or a DSN file using the Windows utility available - the Data Source function ( ODBC). My SAMPLE sample database is configured as a system DSN file, so I can also use this connection to query data instead of just putting data into an Excel 2007 file.
4. Select the database you want to connect from the ' Specify the DB2 database ' box. Enter the correct login information for your database in the ' Log on Information ' box and click ' Test Connection '.
If the database you want to connect to is not in the type defined on the machine running Microsoft Excel 2007, you can search for existing Data Servers and their databases (as well as DB2 Connect entries if you want to Create additional DB2 connections to machine I or DB2 for Z machines using the ' DB2 Discovery ' component built into Excel. You can do another type by selecting the ' Direct server connection ' button and selecting a database from the ' Select the database on the server ' list (click the ' Refresh ' button to update the server list and list). Latest database before selection).
Somewhat out of the scope of this article a bit, but it is better to note that you can use the ' Advance ' function to set advanced properties for this connection. For example, set the timeout for connecting to 'time-out', or create a hard-coded password.
5. Click ' OK ' when connecting successfully.
The DB2 OLE DB Provider is usually fully integrated in Excel 2007. For example, if Excel 2007 cannot connect to the SAMPLE database because the database instance is down, an error message as shown below will be displayed:
If this database is being used (maybe because it is in a silent state or a backup process is being performed), Excel 2007 will display an error message:
6. The ' Data Connection Wizard ' opens. Select the table with the data you want to import into Excel 2007 and click ' Next '. For example, you can select the content of the STAFF table located in the SAMPLE database.
Note : To create the SAMPLE database in DB2 9, you need to enter the db2sampl statement from the operating system's Command Prompt screen.
7. You may optionally leave the information for this connection or not. But it will be better if you record it to be reused later. After that, click the ' Finish ' button.
If you do not want to record this connection in the future, simply click the ' Finish ' button in the previous step. In the image above you can see that I have recorded the current database connection (which is the only connection to the STAFF table) for future use. You can optionally add a description for this connection object (in the ' Description ' field) and search for the metadata (in the ' Search Keywords ' field) to make the following query times easier. Finally, note that you can set good controls for the authentication section by clicking ' Authentication Settings '.
8. The ' Import Data ' window opens. Use this window to describe where the data will be placed in Excel 2007 worksheet. You can select the default values and press OK .
As you can see in the image above, the DB2 data store can be either Table or Pivot Table Report or both. I will discuss them in the next article. (If you want to configure in more detail the properties of the connection string, click the Properties button.)
The Excel 2007 worksheet will display form data:
Of course, at this point you can adjust the display data to suit you through formatting functions strongly support in Excel 2007 and apply Table Style style for tables from the Design tab. Eg:
You can use the ' Table Style Options ' function in the Design tab like:
When the data is downloaded to Excel 2007, you can use the ' External Table Data ' function in the Data tab to manage the database connection. For example, select Unlink will separate data from the table and turn them into independent:
And you can quickly filter the returned data without writing any SQL command line:
One of the things I really like about Excel 2007 is the powerful charting feature. Using this method you can easily display your DB2 9 data as charts.
In the chart above you can see the salary and commission percentages for 5 employees in room 20. Of course, as long as you don't disconnect the log file, you can refresh the data, the chart will be Update immediately. For example, run the following DML command to change the percentage of Pernal commission achieved (Pernal is an employee in the chart):
UPDATE TABLE STAFF SET COMM = 55565.56 WHERE ID = 20
Now refresh the data:
And of course, the base data is also updated:
Let us rethink how to use the most common form of data with Excel: segmenting data into an unofficial Data Server. So, if your real data changes, when will your analysis for real data be? Let's wait for the answer in the next post.
Summary
In this article we have seen how easy and easy it is to import DB2 9 data into Excel 2007 files. Besides, we also know some useful components to change the way data is displayed. Marketing (using different functions of Table Tools) as well as how to create a chart in Excel and how to refresh real data.