Excel Migration Job problem

I’ve got an Excel Migration Job that’s giving me fits.  The solution may lie in Excel itself but so far nothing I’ve tried works.  Perhaps it’s not Excel and there is an issue with my migration job.

I’m migrating Fixed Assets depreciation distribution detail. Note that the migration job for the distribution header worked without problems. 

The source Excel file has a column that requires formatting.   Specifically, I’ve used Excel to format leading zeros to 3 positions:  ‘1’ becomes ‘001’, ‘50’ becomes ‘050’, etc.

However when I Validate a few lines as a test, IFS migration seems to “see” the values in that formatted column without the formatting: ‘001’ is seen as ‘1’, ‘050’ is seen as ‘50’.

 

I’ve tried every approach and finagle I can think of so I’m here asking for insight.

Lucy

 

I just tested setting the same job up and it works fine for me. Is your Code_B column formatted as ‘Text’ in Excel? 
 

If you set one of these up manually in the depreciation distribution and then use the ‘Search’ to populate the excel sheet and then try to validate/update, does it fail as well? 

I ended up looking at this problem with our IFS Technical Consultant and we came up with a workable solution.  The problem is that I receive numeric data in Excel to be migrated,  but I need to present the number with one or more leading zeros to match the base data set up in IFS.  Formatting the column to have leading zeros causes the migration to fail because the column is still interpreted as numbers without the leading zeros by the IFS API.

What worked was taking advantage of Excel’s willingness to let me COPY a cell built with a formula and then PASTE the cell’s value into the target column.

Sample values are 1, 50, 12, and 5.

The Excel formula used was    =IF(T2<10, CONCAT("00",T2),CONCAT("0",T2))   where T is an unused Excel column into which I copied the problem data.

1 becomes the desired 001, 50 becomes 050, 12 becomes 012, and 5 becomes 005.  Migration is happy and so am I.

This is far from an elegant solution but it’s quick, repeatable, and it works.  Good for my migration goals.  May this someday help someone else.