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.
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 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
Excellent stuff Bro… very useful.
Thanks buddy. Will try to share exciting articles like this. Please do join the Telegram and LinkedIn groups if you haven’t.
Hi Srichan,
From where can I download this Macro?
Hi Sahil,
I have provided the link to download the excel file. https://fusionhcmknowledgebase.com/wp-content/uploads/2020/05/HDL-convertor.xls
Thanks,
Sricharan
Very useful post, Sricharan
Thanks Ahmed
Thanks much, I was badly looking for this. I used this in my previous organization and I have missed it.
More Useful page and excellent examples are provided for the specific topic.
Great to continue here. Welldone Team 🙂
Thank you for the compliments Kanimozhi
Hi Sricharan,
Your blog is very useful and helping to us.
Just a quick question regarding this post- Does this sheet work for all business objects or this works only for Worker.dat.
May be I am new so couldnt clarify this question by myself.
Thanks again for your good work.
Hi Anurag,
This excel works for all business objects..
Thanks,
Sricharan
Is this still relevant on the current versions since it is three years old?
Yes, Michael. It is a generic excel sheet that converts the input data into HDL format. You can download it and check it out.