In this article we will look into another interesting topic of generating CSV output from HCM Extracts/BI Reports.
I see lot of clients have requirement to generate the comma separated file with file extension as .csv and oracle doesn’t provide a robust solution for this problem. We will look into all possible ways to solve this problem both for HCM Extracts and BI Reports.
Table of Contents
Generating CSV file using Seeded CSV functionality for BI Report
Oracle has provided a seeded “Data(CSV)” delivery option for BI Report, however it generates the header with the column names and then the data accordingly. BIP has a limitation that when CSV output is requested, all the data in the underlying XML output is included in the CSV file. It also includes the input parameters in the output as an individual column and we do not have any control over the template. We cannot modify the template to hide the input parameters or modify the column headings.
Let’s look at how it works with a sample Data Model:
select PRIMARY_EMAIL_ID ,PRIMARY_PHONE_ID ,MAILING_ADDRESS_ID ,PRIMARY_NID_ID ,EFFECTIVE_START_DATE ,EFFECTIVE_END_DATE from per_all_people_f where rownum <5
Sample Data Model Output:
Now, lets create a blank rtf template and select the output format as Data(CSV)
Now, let’s run the report and check the output:
It is showing all fields in the output csv file in the same order as present on the data model as shown below:
Now, let’s add an input parameter effective date and get data as of that input date and check how the output changes with it.
select PRIMARY_EMAIL_ID ,PRIMARY_PHONE_ID ,MAILING_ADDRESS_ID ,PRIMARY_NID_ID ,EFFECTIVE_START_DATE ,EFFECTIVE_END_DATE from per_all_people_f where rownum <5 and :p_date between effective_start_date and effective_end_date
Here is how the DM output looks like:
Now lets check the report output:
We can see that the input parameter got added to the CSV file and there isn’t a way to change this behavior.
Even though we create an RTF template selecting only the fields that are required, once we change the output format to “Data (CSV)” it will override the template with the seeded behavior and present the output with this default behavior and there isn’t any workaround for it.
We can create an etext template to get the columns that we need and modify the header descriptions as well. However with this approach we cannot get the file extension as .csv.. It will generate output file as <output file>.txt only.
Generating CSV File from BI Report using FTP option
Getting the file extension of .CSV is possible with custom etext template ONLY when the report is scheduled to place the output on FTP server. In the FTP output options, we can provide the remote file name and it will override the filename and file extension as well.
In rest of all cases for Email/Bursting/Content Server/Manual run, we will not be able to get the csv file extension with custom template.
Generate CSV File in HCM Extract on FTP server
Similar to BI Reports, HCM Extracts will generate the output XML only and in backend BI Report is the one which actually generates the output file. As BIP has a limitation that when CSV output is requested, all the data in the underlying XML output is included in the CSV file. This will result in all the parameters (Payroll Action ID, Delivery Option Id, Flow Instance Id), as well as the HCM Extract input parameters being included in the CSV output.
We can use the same FTP approach to override the generated file name if FTP delivery option is being used.
Below is the setting that can be used on the extract delivery options for FTP option to override filename.
Generate CSV File in HCM Extract using XSL Template
This approach has been developed by Oracle CoE (Center of Excellence) team. It utilizes an XSL template to transform the generated XML to create a modified XML which will then be used to generate the CSV output file.
This approach works for all delivery modes like email/ftp/web content server, etc.
The only caveat with this XSL file approach is that the output CSV file should contain at least one column with constant value, else we will need to add one additional column at the end of file with constant value.
Here is the complete document by Oracle CoE team which lists the steps to achieve this functionality.
Generating CSV file in HCM Extracts on Web Content Server
Oracle has come up with a seeded CSV option starting somewhere in 19B/19C and it can be used only with the Web Content Server delivery mode. For other delivery modes, this solution doesn’t work.
We need to configure the delivery option on HCM Extracts like below:
Even here, if the extract contains multiple data groups and records, you will get unnecessary rows on the output as we do not have control over the template. The seeded CSV option utilizes the XML to generate the csv file and we do not any control if we want to modify the layout or heading. if the extract is straight forward with all columns in a single record, then this option works.
Hope you now know all possibilities of generating CSV file from BI Reports and HCM Extracts.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM