867 total views

In this article we will look into an automated way to generate HDL (pipe delimited file) from excel data.

Want to learn Fusion Technical tools? Checkout this article

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“.

Excel to HDL Convertor for HCM Data Loader in Fusion HCM HDL loader
Excel to HDL Converter

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

Open HDL Files and Save HDL Files from HDL Convertor in Fusion HCM HDL tool
Open HDL Files and Save HDL Files

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.

Excel to HDL Convertor Save File as DAT file in HCM Data Loader fusion hcm
Excel to HDL Converter Save File

With this, we can save it as DAT file.

Opening the DAT file the macro has generated in hcm data loader fusion hcm
Opening the DAT file the macro has generated

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.

This is a very useful tool when we work on data conversions and whenever there is a need to modify the existing HDL or generate the new HDL file.

Download LinkExcel to DAT File Converter

Steps to edit a HDL file using this tool:

  1. 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.
  2. The dat file contents will be open on the “Excel Input” tab. Make the modifications as needed.
  3. 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

Stay tuned to latest updates by joining our Telegram group, Facebook Page and LinkedIn Group

Sricharan

Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 6 Comments

  1. Gopi

    Excellent stuff Bro… very useful.

  2. Sricharan

    Thanks buddy. Will try to share exciting articles like this. Please do join the Telegram and LinkedIn groups if you haven’t.

  3. Sahil

    Hi Srichan,

    From where can I download this Macro?

  4. Ahmed

    Very useful post, Sricharan

Leave a Reply