Question

Excel Data Migration - Dates

  • 16 April 2020
  • 4 replies
  • 477 views
C
Rank
Userlevel 2
Badge +6

When using the Excel Data Migration Add-in if any fields contain dates IFS brings them into Excel in the form DD/MM/YYYY HH:MI:SS.

If you do nothing and try to Validate the row, the result column seems to insist that the field doesn’t contain anything (if mandatory).  If you click in the field and then out again Excel re-presents the field as YY/MM/YYYY HH:MI and right aligns it.  It then validates ok.

Obviously if you have a lot of records you can’t click in every field to ensure the formatting corrects itself.

Does anyone know how to solve this ?

Regards chriselliston

4 replies

eqbstal
Rank
Userlevel 7
Badge +21

That’s weird, when I try this I pull in all rows using search.

Dates are shown as YYYY-MM-DD-H:mm:ss

If I enter a value in DD-MM-YYYY or d/M/YY it is automatically set to shown format.

My regional settings are:

However if I select the column D and press Ctrl+1 and set format to date: DD-MMM-YY the following happens:

 

I’ve used view PHRASE_ON_DOCUMENT.

Which view have you been using?

Regards,

Steve

Please like this response if it helped you
C
Rank
Userlevel 2
Badge +6

Hi Steve

Does yours validate ok when you first bring it in (without intervention)?  Mine doesn’t until I click the field.  After that excel changes the presentation of the cell and it will validate.  Prior to manually clicking the cell my results column states that the field is mandatory and must be populated (it already is).

I’m using CUSTOMER_ORDER_LINE.

Regards

Chris

Regards chriselliston
eqbstal
Rank
Userlevel 7
Badge +21

I prepared a new mig job for the customer_order_lines.

No changes done on one of the tabs in the mig job.

Opened Excel. Connected, selected the new mig job and used the search button.

Used an existing order number and the results are as follows:

Date shown is in different format when selecting cell

Now I press the icon to validate all rows.

Both lines come back with: Field [PLANNED_SHIP_DATE] in Customer Order Line may not be specified for new objects.

I take that field out of the update (in the mig job). This results in:

So I prepared a mig jobs without many changes (only ship date out). Searched for one order. Validated the set of two rows that were retrieved from the search. No problems appeared.

Try to fiddle a bit with your local region settings (although that may not result anywhere near what you want).

A bit more technical is to use the debug option via Settings in Excel:

Either you check the results of the debug or maybe an IFS expert that you regular speak can help you out.

Steve

Please like this response if it helped you
C
Rank
Userlevel 2
Badge +6

Thanks Steve.

I’ve found a workaround using text to columns in Excel that seems to work.

I’ll have a play with my regional settings and report back any findings for the community.

Ideally I’d like an intervention free solution if i can find one.

Regards

Chris

Regards chriselliston

Reply


Terms & ConditionsCookie settings