Create a Dataset Using a Microsoft Excel File

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

• About Supported Excel Files

• Access Multiple Tables per Sheet

About Supported Excel Files

Support of Microsoft Excel files as a dataset type in 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, 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 Publisher to recognize each one. See Access 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 dataset is created, 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.

• Single value parameters are supported, but multiple value parameters are not supported.

Access Multiple Tables per Sheet

If the Microsoft Excel worksheet contains multiple tables that you want to include as data sources, then you must define a name for each table in Microsoft Excel.

The name that you define must begin with the prefix: BIP_, for example, BIP_SALARIES.

To access multiple tables per sheet:

1. Insert the table in Microsoft Excel.
2. Select the table and define a name that is prefixed with BIP_.

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

Description of the illustration xdo11g_dme_excel1.gif

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

Create datasets using Microsoft Excel files stored in file directories.

Note that to include parameters for your dataset, you define the parameters first, so that they're available for selection when defining the dataset. The Excel dataset type supports one value per parameter. It doesn't support multiple selection for parameters.

To create a dataset using a Microsoft Excel file from a file directory data source:

1. Click the New Dataset toolbar button and select Microsoft Excel File. The New Dataset - Microsoft Excel File dialog launches.
2. Enter a name for this dataset.
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 dataset, as shown below.
   
   

   
   Description of the illustration xdo11g_dme_excel2a.gif

   
   
7. If you added parameters for this dataset, 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.
8. Click OK.
9. Link the data from this query to the data from other queries or modify the output structure.

Upload a Microsoft Excel File Stored Locally

To use a local Microsoft Excel file as a data source, you first upload it.

Note that to include parameters for the dataset, you define the parameters first, so that they're available for selection when defining the dataset. The Excel dataset type supports one value per parameter. It doesn't support multiple selection for parameters.

To create a dataset using a Microsoft Excel file stored locally:

1. Click the New Dataset toolbar button and select Microsoft Excel File. The Create Dataset - Excel dialog launches.
2. Enter a name for this dataset.
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, you can select it from the File Name list.
5. If the Excel file contains multiple sheets or tables, select the appropriate Sheet Name and Table Name for this dataset, as shown below.
   
   Description of the illustration xdo11g_dme_excel3.gif
6. If you added parameters for this dataset, 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.
7. Click OK.
8. Link the data from this query to the data from other queries or modify the output structure.

Refresh and Delete an Uploaded Excel File

You can refresh and delete uploaded local Excel files.

After uploading the file, it's displayed on the Properties pane of the data model under the Attachments region, as shown below.

Description of the illustration xdo11g_dme_excel_files.gif

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 won't 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.