Excel import - data distorted in Excel Buffer function

Pinkycz · 2014-10-10

Hi,

I'm importing Excel values to NAV and noticed may of the values get distorted... Value in excel stores as say 35.1 is saved to Excel buffer table as 35.100000002 this happens almost for every value.
The value in Excel really is 35.01, no rounding issue there of decimal places hidden.

This seem to be known issue, searching forum I found similar problems reported
viewtopic.php?f=23&t=35899&hilit=excel+import+problem
or
viewtopic.php?f=23&t=42672&hilit=excel+import+problem

The problem seem to be in Excel Buffer table, function ReadSheet()

There is a line where the is formatted and it is on this line when the value changes, I suspect it is the formatting that does that
..
..
..
"Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');
..
..
..

I would like to only get the value AS IT IS in the excel, clearly it is the FORMAT function that is somehow changing this... And I ssupectet this is wrong by design for quite some time probably.

I tried to force format as FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value,0,'<Char>') and some other... but can't get it right. Having no experience with formatting before, what would be the proper FORMAT parameters to only pick the values for teh text avoiding these 0.000000000001s to be added or subtracted from the original values stored in file??

This is the error I see most often when trying various format parameters..

Microsoft Dynamics NAV Classic

There are errors in the text conversion because text no. 121-2000 does not exist in the .stx file.

Internal error: 47-1

OK

MBerger · 2014-10-13

I had the same problem last week, getting extra decimals when reading from Excel using the buffer. i found there was a hotfix available, and it does solve this problem : http://support.microsoft.com/kb/973898

Pinkycz · 2014-10-13

Thank you very much for pointing me to this one! We obviously got this code in as the IsDecimal function exist and is used... but I detecte dthe code in ReadSheet is slightly different and on the existing one some numeric fields were not detected as numeric, hence the rounding problem.

Pinkycz · 2014-10-13

The suggested fix in T370 / ReadSheet function has still been giving me the problems with our import... any numbers lower than 1 were distorted. As we are importing to a custom table where these values are often used it creates a big problem... I think this is the reason..."IF "Cell Value as Text"[1] <> '0' THEN.."

VALIDATE("Row No.",i);
  REPEAT
    VALIDATE("Column No.",j);
    "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');
    IF "Cell Value as Text" <> '' THEN BEGIN
      IF "Cell Value as Text"[1] <> '0' THEN     // Any value that starts with 0 is skipped from IsDecimal check????
        IF IsDecimal("Cell Value as Text") THEN
          IF EVALUATE(CellValueDecimal,"Cell Value as Text") THEN
            "Cell Value as Text" := FORMAT(ROUND(CellValueDecimal,0.000001),0,1);
      INSERT;
    END;
    j := j + 1;
  UNTIL j > Maxj;
  i := i + 1;
  Window.UPDATE(1,ROUND(i / Maxi * 10000,1));

and adjusted

  REPEAT
    VALIDATE("Column No.",j);
    "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');
    IF "Cell Value as Text" <> '' THEN BEGIN  
    MyDebug :=  "Cell Value as Text"[1];
      IF  "Cell Value as Text"[1] IN ['0','1','2','3','4','5','6','7','8','9'] THEN
        IF IsDecimal("Cell Value as Text") THEN
          IF EVALUATE(CellValueDecimal,"Cell Value as Text") THEN
            "Cell Value as Text" := FORMAT(ROUND(CellValueDecimal,0.000001),0,1);
      INSERT;
    END;
    j := j + 1;
  UNTIL j > Maxj;
  i := i + 1;
  Window.UPDATE(1,ROUND(i / Maxi * 10000,1));

\:D/

MBerger · 2014-10-14

btw, i have noticed another bug in the Excel buffer table : ReadSheet will break if there are any error cells ( like division by zero ) in the sheet. I fixed that by adding a boolean field "HasError" to the table ( not really needed, but i found it practical to know which cells had errors afterwards ) and adding this in the ReadSheet function :

    HasError := XlApp.WorksheetFunction.IsError(XlWrkSht.Range(xlColID + xlRowID)) ;

    IF NOT HasError THEN
      "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ')
    ELSE
      "Cell Value as Text" := XlWrkSht.Range(xlColID + xlRowID).Text ;

Pinkycz · 2014-10-14

This is a neat improvement MBerger, will definitely try to implement this into ours as I have also suffered with some N/A errors and similar!!

easy-navi · 2018-01-26

It is better to make such a condition to extract those distorted values:

         IF ("Cell Value as Text"[1] IN ['0','1','2','3','4','5','6','7','8','9'])
          AND ((STRPOS("Cell Value as Text",'.')>0) OR (STRPOS("Cell Value as Text",',')>0))
          AND ((STRPOS("Cell Value as Text",'0000000000')>0) OR (STRPOS("Cell Value as Text",'9999999999')>0))
         THEN .....

You are 99,99999% sure that this will work ONLY on those strange values.

Howdy, Stranger!

Quick Links

Categories

Excel import - data distorted in Excel Buffer function

Comments