IBM InfoSphere Federation Server, Version 10.1

Excel wrapper

An Excel workbook is a file that is created using the Microsoft Excel application and has a file extension of xls. The Excel wrapper is used to perform searches on the Excel files.

You use Excel files to store information that is best displayed in a table, with corresponding rows and columns. Excel workbooks consist of one or more spreadsheet pages, or worksheets. Worksheets are often used to perform calculations.

The following figure shows how the Excel wrapper connects your worksheets to the federated system.

Figure 1. How the Excel wrapper works The federated database uses the Excel wrapper to access data from Excel worksheets. The data is returned to the client computer as structured data in a relational results table.

The federated database uses the Excel wrapper to access data from Excel worksheets. The data is returned to the client computer as structured data in a relational results table.

The Excel wrapper uses the CREATE NICKNAME statement to map the columns in your Excel worksheets to columns in your federated system. The following table shows a sample of worksheet data that is stored in a file called Compound_Master.xls.

Table 1. Sample worksheet for Compound_Master.xls
	A	B	C	D
1	COMPOUND_NAME	WEIGHT	MOL_COUNT	WAS_TESTED
2	compound_A	1.23	367	tested
3	compound_G		210
4	compound_F	0.000425536	174	tested
5	compound_Y	1.00256		tested
6	compound_Q		1024
7	compound_B	33.5362
8	compound_S	0.96723	67	tested
9	compound_O	1.2		tested

The information in an Excel worksheet is usually not available to you through standard SQL commands. When the Excel wrapper is installed and registered on your federated server, you can access this information as if it were a typical relational data source. For example, if you wanted to know all the compound data where the molecular count is greater than 100, you would run the following SQL query:

SELECT * FROM compound_master WHERE mol_count > 100

The results of the query are shown in the following table.

Table 2. Query results
COMPOUND_NAME	WEIGHT	MOL_COUNT	WAS_TESTED
compound_A	1.23	367	tested
compound_G		210
compound_F	0.000425536	174	tested
compound_Q		1024

Feedback

Last updated: 2012-05-18