Excel Buffer Import

Excel Buffer import Performance is great in NAV 2016.   One issue I have run into when loading Excel file is fields that have long numeric values.


Excel by default changes the formatting Scientific Notation.  When loading this in excel buffer, NAV cannot handle the data type and errors out.

 

The error stops on the following line.  in Table 370  Excel Buffer in function ParseCellValue

 

EVALUATE(Decimal,Value);

 

The workaround is the change excel formatting to a number with no decimal places.   This is a manual process that wouldn't work for my senario.

 

The fix that I added was the following.

 

//M01 Start  Remove Scientific notation
IF STRPOS(Value,'E+') <> 0 THEN BEGIN
  TempValue := DELCHR(COPYSTR(Value,1,STRPOS(Value,'E')),'=','E.');
  EVALUATE(Exponent,COPYSTR(Value,STRPOS(Value,'E')+2));
  TempValue := PADSTR(TempValue,Exponent+1,'0');
  Value := TempValue;
  IF NOT EVALUATE(Decimal,Value) THEN BEGIN
   "Cell Type" := "Cell Type"::Text;
    "Cell Value as Text" := Value;
    EXIT;

  END;
END;
//M01 End

EVALUATE(Decimal,Value);

 

I tried to use dotnet  Decimal.Parse function the only issue is that any number greater 999,999,999,999,999.99 will give you runtime error.

And my numbers were e+15.  For example  7.15757005695256E+15

 

So the only option was to parse it manually. 

Anonymous
Related