Creating a Data Set Using a CSV File

BI Publisher supports data sets that use CSV file data sources to return valid XML data.

The following topics describe using requirements and procedures for using a CSV as a data source:

About Supported CSV Files

Support of CSV files as a data set type in BI Publisher follow certain guidelines.

  • You can use a CSV file that is located in a directory that your administrator has set up as a data source. For more information, see Setting Up a Connection to a File Data Source in Administrator's Guide for Oracle Business Intelligence Publisher.

    You can upload a file from a local directory.

  • The supported CSV file delimiters are Comma, Pipe, Semicolon, and Tab.

  • If your CSV file contains headers, the header names are used as the XML tag names. The following characters are not supported in XML tag names: ~, !, #, $, %, ^, &, *, +, `, |, :, \", \\, <, >, ?, ,, /. If your data source file contains any of these characters in a header name, use the data model editor Structure tab to edit the tag names.

  • CSV data sets support editing the data type assigned by the data model editor. See Editing the Data Type for more information. If you update the data type for an element in the data set, you must ensure that the data in the file is compliant with the data type that you selected.

  • The CSV files that you use as input to the Oracle BI Publisher data engine must be UTF-8 encoded and cannot contain empty column headers.

  • Group breaks, data links, expression and group-level functions are not supported.

  • Data fields in CSV files must use the canonical ISO date format for mapped date elements, for example, 2012-01-01T10:30:00-07:00, and ######.## for mapped number elements.

  • Data validation is not provided for CSV files.

Creating a Data Set from a Centrally Stored CSV File

You can use a CSV file from a file directory to create a data set.

  1. On the data model editor toolbar, click New Data Set and select CSV File. The New Data Set - CSV 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 CSV file resides.
    The list is populated from the configured File Data Source connections.
  5. Click Browse to connect to the data source, browse the available directories, and select the file.
  6. Select The first row a column header to specify if the first row in the file contains column names.
    If you do not select this option, the columns are assigned a generic name, for example, Column1, Column2. You can edit the XML tag names and display names in the data model editor Structure tab.
  7. Select the CSV delimiter used in the file.
    The default selection is Comma (,).
  8. Click OK.

Uploading a CSV File Stored Locally

Create data sets using CSV files stored in local file directories.

To create a data set using a CSV file stored locally:

  1. On the toolbar, click New Data Set and select CSV File. The New Data Set - CSV File dialog launches, as shown below.
  2. Enter a name for this data set.
  3. Select Local to enable the Upload button.
  4. Click Upload to browse for and upload the CSV 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. (Optional) Select The first row a column header to specify if the first row in the file contains column names. If you do not select this option, the columns are assigned a generic name, for example, Column1, Column2. The XML tag names and display names assigned can be edited in the data model editor Structure tab.
  6. Select the CSV Delimiter used in the file. The default selection is Comma (,).
  7. Click OK.

Editing the Data Type

After uploading a CSV file data type, you can edit it as needed.

To edit the data type for a CSV file element, click the data type icon or update it from the element Properties dialog.

The data for an element must be compliant with the data type that you assign. The user interface does not validate the data when you update the data type. If the data does not match, for example, a string value is present for an element you defined as Integer, errors may occur in the layout editing tools and or at runtime.

You can only update the data types for CSV file data sources.

Refreshing and Deleting an Uploaded CSV File

You can refresh and delete uploaded local CSV files.

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


Description of GUID-12A7C995-A0FD-4F69-87D0-4B1BE89A4029-default.gif follows
Description of the illustration GUID-12A7C995-A0FD-4F69-87D0-4B1BE89A4029-default.gif

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

To refresh the local file in the data model:

  1. In the component pane, click Data Model 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. In the component pane, click Data Model 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.