Creating a Data Set Using a Microsoft Excel File

These topics describe requirements, options, and procedures for using Microsoft Excel files as a data source.

Options for Uploading Excel Files to BI Publisher

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.

About Supported Excel Files

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.

Accessing Multiple Tables per Sheet

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.

  1. Insert the table in Excel.
  2. Do one of the following:
    • Using Excel 2003, select the table. On the Insert menu, click Name and then Define. Enter a name that is prefixed with BIP_.
    • Using Excel 2007, select the table. On the Formulas tab, in the Defined Names group, click Define Name, then enter the name in the Name field. The name you enter appears on the Formula bar

For example, you could use the Define Name command in Microsoft Excel 2007 to name a table BIP_Salaries.


Description of GUID-E9F87768-4A5B-4A8B-AF40-19455A81BAAF-default.gif follows
Description of the illustration GUID-E9F87768-4A5B-4A8B-AF40-19455A81BAAF-default.gif

Using a Microsoft Excel File Stored in a File Directory Data Source

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:

  1. Click the New Data Set toolbar button and select Microsoft Excel File. The New Data Set - Microsoft Excel File dialog launches.
  2. Enter a name for this data set.
  3. Click Shared to enable the Data Source list.
  4. Select the data source where the Microsoft Excel File resides.
  5. To the right of the File Name field, click the browse icon to browse for the Microsoft Excel file in the data source directories. Select the file.
  6. If the Excel file contains multiple sheets or tables, select the appropriate Sheet Name and Table Name for this data set, as shown below.
  7. If you added parameters for this data set, click Add Parameter. Enter the Name and select the Value. The Value list is populated by the parameter Name defined in the Parameters section. Only single value parameters are supported. See Adding Parameters and Lists of Values.
  8. Click OK.
  9. Link the data from this query to the data from other queries or modify the output structure. For more information on linking queries, see Structuring Data.

Uploading a Microsoft Excel File Stored Locally

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:

  1. Click the New Data Set toolbar button and select Microsoft Excel File. The Create Data Set - Excel dialog launches.
  2. Enter a name for this data set.
  3. Select Local to enable the upload button.
  4. Click the Upload icon to browse for and upload the Microsoft Excel file from a local directory. If the file has been uploaded to the data model, then it is available for selection in the File Name list.
  5. If the Excel file contains multiple sheets or tables, select the appropriate Sheet Name and Table Name for this data set, as shown below.
  6. If you added parameters for this data set, click Add Parameter. Enter the Name and select the Value. The Value list is populated by the parameter Name defined in the Parameters section. Only single value parameters are supported. See Adding Parameters and Lists of Values.
  7. Click OK.
  8. Link the data from this query to the data from other queries or modify the output structure. For more information on linking queries, see Structuring Data.

Refreshing and Deleting an Uploaded Excel File

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.

Description of GUID-86E2FF41-4132-46B9-9009-05D1A6739AB2-default.gif follows
Description of the illustration GUID-86E2FF41-4132-46B9-9009-05D1A6739AB2-default.gif

See Setting Data Model Properties for information about the Properties pane.

To refresh the local file in the data model:

  1. Click Data Model in the component pane to view the Properties page.

  2. In the Attachment region of the page, locate the file in the Data Files list.

  3. Click Refresh.

  4. 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.

  5. Save the data model.

To delete the local file:

  1. Click Data Model in the component pane to view the Properties page.
  2. In the Attachment region of the page, locate the file in the Data Files list.
  3. Click Delete.
  4. Click OK to confirm.
  5. Save the data model.