These topics describe requirements, options, and procedures for using Microsoft Excel files as a data source.
To use a Microsoft Excel file as a data source, you have the two options for providing the file to BI Publisher.
Place the file in a directory that your administrator has set up as a data source. See Setting Up a Connection to a File Data Source in Administrator's Guide for Oracle Business Intelligence Publisher.
Upload the file to the data model from a local directory.
Support of Microsoft Excel files as a data set type in Oracle BI Publisher follows certain guidelines.
Save Microsoft Excel files in the Excel 97-2003 Workbook (*.xls) format by Microsoft Excel. Files created by a third party application or library are not supported.
The source Excel file can contain a single sheet or multiple sheets.
Each worksheet can contain one or multiple tables. A table is a block of data that is located in the continuous rows and columns of a sheet.
In each table, Oracle BI Publisher always considers the first row to be the heading row for the table.
The first row under the heading row must not be empty and is used to determine the column type of the table. The data type of the data in the table may be number, text, or date/time.
If multiple tables exist in a single worksheet, the tables must be identified with a name for BI Publisher to recognize each one. See Accessing Multiple Tables per Sheet.
If all tables in the Excel file are not named, only the data in the first table is recognized and fetched.
When the data set is created, BI Publisher truncates all trailing zeros after the decimal point for numbers in all cases. To preserve the trailing zeros in your final report, you must apply a format mask in your template to display the zeroes. For more information about format masks, see Formatting Numbers, Dates, and Currencies in Report Designer's Guide for Oracle Business Intelligence Publisher.
Single value parameters are supported, but multiple value parameters are not supported.
If the Excel worksheet contains multiple tables that you want to include as data sources, then you must define a name for each table in Excel.
Note:
The name that you define must begin with the prefix: BIP_, for example, BIP_SALARIES.
For example, you could use the Define Name
command in Microsoft Excel 2007 to name a table BIP_Salaries.
Create data sets using Microsoft Excel files stored in file directories.
Note that to include parameters for your data set, you must define the parameters first, so that they are available for selection when defining the data set. See Adding Parameters and Lists of Values.
Note:
The Excel data set type supports one value per parameter. It does not support multiple selection for parameters.
To create a data set using a Microsoft Excel file from a file directory data source:
To use a local Microsoft Excel file as a data source, you must first upload it.
Note that to include parameters for the data set, you must define the parameters first, so that they are available for selection when defining the data set. See Adding Parameters and Lists of Values.
Note:
The Excel data set type supports one value per parameter. It does not support multiple selection for parameters.
To create a data set using a Microsoft Excel file stored locally:
You can refresh and delete uploaded local Excel files.
After uploading the file, it displays on the Properties pane of the data model under the Attachments region, as shown below.
See Setting Data Model Properties for information about the Properties pane.
To refresh the local file in the data model:
Click Data Model in the component pane to view the Properties page.
In the Attachment region of the page, locate the file in the Data Files list.
Click Refresh.
In the Upload dialog, browse for and upload the latest version of the file. The file must have the same name or it will not replace the older version.
Save the data model.
To delete the local file: