By Edgar Tinio
Have you ever created an Integration file only to realize that you have unexpected formatting or data values in the file? As an Integration Engineer/Solution Architect, I am regularly asked to help on projects where the results of using a flat file are not what the project team member expected.
Let me outline some specific strategies you can use to avoid issues with these integration files. Integration files are required by an HCM/WFM system, either inbound from another system or outbound going to another system. Here are the best tips for working with these HCM/WFM Integration Files.
Flat File Basics
Let’s start with the basics. What is a flat file? A flat file is primarily a text file with one record per line and is useful to represent tabular data. These have a delimiter to separate different fields or columns in the file, with a comma being the most common delimiter – such files we usually refer to as CSV (comma-separated values) files. Another flat file format would have a specific number of characters (like letters, numbers, or spaces) allowed per field or column – these files are usually called fixed-width files and tend to be used by legacy systems
Note: the screenshots and software used were taken from a computer running Windows 11 so steps or screens may not be applicable to macOS users.
CSV Integration File Common problems
In this blog post, we focus on some common CSV-related issues when creating your integration files. Newer systems can process files with a more complex structure, such as a hierarchy or key-value pairs – these formats tend to be XML (eXtensible Markup Language) files or JSON (JavaScript Object Notation) files.
Here’s an assumption: the most popular software tool used with CSV files is Microsoft Excel. The use of Microsoft Excel is both good and bad. It is good because most business users have Excel, and it is a quick way to generate a CSV file based on spreadsheet data. It is bad because Excel tends to cause issues if a user decides to “just open” a CSV file to edit/review.
- Missing leading zeroes – Excel tends to convert numerical data into generic text, and missing zeroes may cause system errors or confusion, e.g., Employee IDs or government IDs may begin with zeroes.
- Date formats are incorrect – Excel may change the formatting of date values to your system’s default format causing system errors or invalid data, e.g., the original date was 2023-10-31 (in ISO 8601 format), but Excel changed it to 10/31/2023.
- Corrupted data – Excel could misinterpret the delimiter for a record, causing fields to have the wrong data type or size expected.
Opening a CSV file in Microsoft Excel
For all three possible integration file issues above, never open a CSV file by double-clicking on it from File Explorer or as an email attachment. Use the following steps if you or a project team member needs to edit or review a CSV.
- Open Excel before opening the CSV and create a new blank workbook.
2. Click on the “Data” menu, and on the “Get & Transform Data” section of the ribbon, click on the “From Text/CSV” button.
3. An “Import Data” file explorer box will open and browse the CSV file, select it, and click the “Import” button or double-click the CSV filename.
4. The next dialog box will show a preview of the selected file and ensure the delimiter is correct.
5. Click the “Load” button to import the CSV data into the blank workbook.
6. Review/edit the data as needed.
7. When ready to save the file as a CSV, go to the “File” menu, click “Save As,” and change the format of the file from Excel Workbook (*.xlsx) to CSV UTF-8 (Comma delimited) (*.csv), and click the “Save” button.
If you encounter more complex issues or have large and complex CSV files to review/edit, instead of clicking on the “Load” button, the “Transform” button is useful. For further information, refer to the Microsoft 365 Support site on Power Query in Excel.
Other ways to edit a CSV file
Lastly, I highly recommend that to view a CSV’s contents quickly, you should use a text-editor program like Notepad++ or Sublime Text, but any simple text editor can be used. Use these editing tools to modify/edit the contents without corrupting the data.
We hope this information proves useful in the creation of your integration files. For additional support or to find out how HRchitect can help you during your HCM Implementation, contact us.
About the Author
Edgar Tinio brings over 18 years of technical experience and 14 years of Workforce Management (WFM) experience to the HRchitect team. Edgar provides technical advice and support to clients while delivering quality solutions from his extensive experience. He has a Bachelor’s degree in Mathematics, Computer Science, and Information Systems from the University of Waterloo.
Learn more about Edgar on Linkedin.