Incorrect Data from File Loads in Data Management

I encountered an issue with a data load and in hindsight I should have known right away where to look.  Actuals are loaded during close through an automated process utilizing EPM Automate and Data Management.  The process has been working without issues since it was implemented.  But recently we needed to manually reload some prior periods due to adjustments made in the GL.  We’ve done these manual loads in the past and did not have issues.  The manual load has two CSV files exported from the GL that are combined into one file.  We found variances in some accounts when validating the load.  

The variances were small, less than one dollar.  I knew it wasn’t a rounding issue, the GL only goes to two decimal places.  I compared the load file with the Workbench in Data Management for a specific account to determine what was going on.  Data Management showed the same values for source and target, and these values did not match the load file.  How could that be possible?  Was the load file wrong?  

Workbench original load
  1. 1109435931
  2. -384832244.8

At first glance it appeared to be accurate and match the GL, until I noticed something odd when reviewing the individual lines.

I had the CSV file open in Excel.  Notice the values that appear.  They match what is in Workbench.

The first line shows 1109435931 and the second line shows -384832244.8. 

But these are not the values when we look at each cell.

It was puzzling, the CSV file had different values than Workbench.  Then something occurred to me: I had opened the CSV in Excel.  Could something be happening in Excel with the data?  Remember, the load file was the combination of two CSV files and Excel was used to merge them into one.

To prove out my theory, I opened each of the original export files from the GL in Notepad++ and did the merge to one file there.  The file was saved as CSV.  Notice the values.

This new file was then loaded.  The Workbench values now match what is in the file.

Workbench for file edited in Notepad++
  1. 1109435931.2
  2. -384832244.84

Editing the files in Excel did affect the data.  The CSV file was altered such that values were truncated for some lines and inaccurate data loaded to EPM.  The lesson learned is that Excel should not be used for editing files to load into EPM.  A text editor like Notepad++ is the safer tool for editing files for loading.

One thought on “Incorrect Data from File Loads in Data Management

  1. Very interesting. I’ve had the same thing happen with numerical values in text format. If you have 008 and you open the csv it will get converted to 8. I’ve found that the FillL import expression works wonders to correct this type of issue. Also it breaks different formats of dates. if you have the date Jul-23 it will format it differently when you open the csv.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s