Dynamics Community Forum Thread Details

NAV reading wrong/Junk value for Time column in Excel.

Hey,

I am reading a fetching data from excel and dumping in NAV table using Automation variable.

The issue is NAV is reading incorrect time value from Excel.

Below is example of my code:

-------------------------------------------------Code----------------------------------------------------
FileName := 'D:\GRN_Today.xlsx';
CLEAR(ExcelApp);
CLEAR(ExcelWorkSheet);
CLEAR(ExcelWrkbook);
i := 0;
IF NOT CREATE(ExcelApp,TRUE,TRUE) THEN
ERROR('Error');
ExcelWrkbook := ExcelApp.Workbooks.Open(FileName);
ExcelWorkSheet := ExcelWrkbook.ActiveSheet;

i := 2;
REPEAT
PODate := FORMAT(ExcelWorkSheet.Range('Q'+FORMAT(i)).Value);                              // PO Date
DeliveredTime := FORMAT(ExcelWorkSheet.Range('R'+FORMAT(i)).Value);                       // Delivered Time
CreatedTime := FORMAT(ExcelWorkSheet.Range('S'+FORMAT(i)).Value);                         // Created Time

//MESSAGE('PO Date: '+PODate);
MESSAGE('Del Time: '+DeliveredTime);
MESSAGE('CreatedTime: '+CreatedTime);
i+=1;
UNTIL FORMAT(ExcelWorkSheet.Range('A'+FORMAT(i)).Value) = '';

ExcelWrkbook.Close;
ExcelApp.Quit;
CLEAR(ExcelWorkSheet);
CLEAR(ExcelWrkbook);
CLEAR(ExcelApp);

---------------------------------------------------Code--------------------------------------------------

The variables 'ExcelWorkSheet', 'ExcelWrkbook' and 'ExcelApp' are office automation variables

The variables 'DeliveredTime', 'CreatedTime' and 'PODate' are text variables.

I've even tried changing the format in excel, but all in vain.

The output for time value is

---------------------------
Microsoft Dynamics NAV Classic
---------------------------
Del Time: 42,816.705555555556
---------------------------
OK
---------------------------

---------------------------
Microsoft Dynamics NAV Classic
---------------------------
CreatedTime: 0.41175925925925921
---------------------------
OK
---------------------------

even though the value in excel is time : 09:52:56

I've found one link but that seems only to be a workaround but doesn't looks like a solution.

https://dynamicsuser.net/nav/f/developers/8625/importing-a-time-field-from-excel

Please let me know, if anybody have solved such issue.

FileName := 'D:\GRN_Today.xlsx'; CLEAR(ExcelApp); CLEAR(ExcelWorkSheet); CLEAR(ExcelWrkbook); i := 0; IF NOT CREATE(ExcelApp,TRUE,TRUE) THEN ERROR('Error'); ExcelWrkbook := ExcelApp.Workbooks.Open(FileName); ExcelWorkSheet := ExcelWrkbook.ActiveSheet; i := 2; REPEAT PODate := FORMAT(ExcelWorkSheet.Range('W'+FORMAT(i)).Value); DeliveredTime2 := ConvertTime(FORMAT(ExcelWorkSheet.Range('R'+FORMAT(i)).Value)); CreatedTime2 := ConvertTime(FORMAT(ExcelWorkSheet.Range('S'+FORMAT(i)).Value)); MESSAGE('Time Del :'+ FORMAT(DeliveredTime2)); MESSAGE('Time CreatedTime :'+FORMAT(CreatedTime2)); i+=1; UNTIL FORMAT(ExcelWorkSheet.Range('A'+FORMAT(i)).Value) = ''; ExcelWrkbook.Close; ExcelApp.Visible := FALSE; ExcelApp.Quit; CLEAR(ExcelWorkSheet); CLEAR(ExcelWrkbook); CLEAR(ExcelApp);

ConvertTime(DecimalTimeText : Text[50]) : Time //DeliveredTime := FORMAT(ExcelWorkSheet.Range('R'+FORMAT(i)).Value); // Delivered Time EVALUATE(DeliveredTime_Decimal,DecimalTimeText); IF DeliveredTime_Decimal > 0 THEN BEGIN DeliveredTime_Decimal := DeliveredTime_Decimal*24; HH:= ROUND(DeliveredTime_Decimal,1,'<'); DeliveredTime_Decimal := DeliveredTime_Decimal-HH; DeliveredTime_Decimal := DeliveredTime_Decimal*60; MM := ROUND(DeliveredTime_Decimal,1,'<'); DeliveredTime_Decimal := DeliveredTime_Decimal - MM; DeliveredTime_Decimal := DeliveredTime_Decimal*60; SS := ROUND(DeliveredTime_Decimal,1,'='); EVALUATE(DeliveredTime2,FORMAT(HH)+':'+FORMAT(MM)+':'+FORMAT(SS)); END ELSE DeliveredTime2 := 0T; EXIT(DeliveredTime2);

Quick Links