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
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:
- Insert the table in Microsoft Excel.
- 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.
