1 Reply Latest reply: May 15, 2014 10:14 AM by Olly Bond RSS

    Opening Excel file returns null for some values.

    sookie49 _

      If I open an Excel file where the value of a field is great than a million and the data has 9 decimal places, then Monarch gives and error that it could not read the data and returns nulls instead of the value.

        • Opening Excel file returns null for some values.
          Olly Bond

          Hello sookie,

           

          This is because Monarch gives numeric fields a data length of 18.

           

          Let's imagine I win the lottery, and the jackpot is:

           

          £123,456,789,876.54

           

          To handle that number in a computer, it is treated as a floating point integer.

           

          My jackpot would be 1.2345678987654E+12 - which takes 18 bytes of memory to store.

           

          This format allows for 14 significant figures, which is enough for most real life purposes, although that is reduced if the exponent (the E+nnn) grows larger.

           

          I've not read up on this, and Gareth Horton would be able to give you a much better understanding of how Excel is handling the data under the hood, but as far as I understand it, Monarch v11 and previous can't handle numbers that can't be shown as a floating point integer in 18 bytes.

           

          There's another data type that crops up occasionally - the bit, a field that can be only 1 or 0. Perhaps Monarch v12 will extend the range of data types to allow us to handle these oddities?

           

          In the meantime, I'd suggest you find some way to handle this "number" as a character field, then truncate it and use val() to get a close approximation of the real value.

           

          Best wishes,

           

          Olly