In this article we will look into an automated way to generate HDL (pipe delimited file) from excel data.
Most of the clients provide the input data for conversions in excel sheets and we will have to use the excel functions or copy data to notepad++ and replace the new space character with pipe (|) symbol.
However after replacing the character if there is a need to modify a single column of data, we do not have the option and we will have to convert to excel again and copy it back to notepad and replace with pipe.
Now I am going to provide a excel macro tool which will convert the excel data into HDL file using a macro at the click of a button.
This excel sheet consists of two sheets “Excel Input” and “Open – Save HDL DAT Files“.
In the “Excel Input” sheet we need to input the data in rows and columns and then go to next tab
The next tab “Open – Save HDL DAT Files” will look like
There are two buttons in this sheet:
Open HDL DAT File by Clicking Here: If you click on it, you can browse for a DAT file and when select any DAT file, it will get opened up in the “Excel Input” tab in excel format. And then you can modify the data in excel and come back to this sheet and click on “Save DAT File by Clicking Here” to save it as DAT file.
Save HDL DAT File by Clicking Here: If you click this, it will ask for a filename and you can browse for the folder and then save the data in the “Excel Input” worksheet as a .dat file.
With this, we can save it as DAT file.
Once you save the dat file, you can zip it and then upload the file to Import and Load Data Screen to load the data into the application.
Download Link – Excel to DAT File Converter
Steps to edit a HDL file using this tool:
- Open the second tab “Open – Save HDL DAT Files” and import the HDL file using “Open HDL DAT File by Clicking Here” button and browsing for the file.
- The dat file contents will be open on the “Excel Input” tab. Make the modifications as needed.
- Then go to second tab “Open – Save HDL DAT Files” and click “Save HDL DAT File by Clicking Here” to save the HDL DAT file.
Note: For this Excel tool to work, Macros needs to be enabled on your Microsoft Excel. You will find a ribbon to enable the macros, once you open this sheet if at all Macros aren’t enabled already.
if you face issues with macros, check out this article on how to enable macros in MS Excel
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