Darren's Occasional Nugget 09/26/2022

[paul_cardinale]

---

@Ettepet wrote:

There are still flaws to this way of reading an Excel file.

Besides getting the internal data in USA-format there are also rounding errors to deal with.  Exact dotted numbers in a number-field are retreived in an imprecise way:  "10.7" becomes "10.700000000000001" if you define the number field with "Decimal places: 1".

Reading this through the regular Labview-mechanism gives "10.7", or in my local format: "10,7". (like it shows in my local format Excel file)

---

Note that 10.7 is not exactly representable in binary floating point.

[paul_cardinale]

---

@Ettepet wrote:

There are still flaws to this way of reading an Excel file.

Besides getting the internal data in USA-format there are also rounding errors to deal with.  Exact dotted numbers in a number-field are retreived in an imprecise way:  "10.7" becomes "10.700000000000001" if you define the number field with "Decimal places: 1".

Reading this through the regular Labview-mechanism gives "10.7", or in my local format: "10,7". (like it shows in my local format Excel file)

---

Is there something in the .xlsx file we can use to determine whether a value is a numeric or a string?

[gkrn]

Hi..

can someone convert to lv17

[Ettepet]

---

@Ettepet wrote:

There are still flaws to this way of reading an Excel file.

Besides getting the internal data in USA-format there are also rounding errors to deal with.  Exact dotted numbers in a number-field are retreived in an imprecise way:  "10.7" becomes "10.700000000000001" if you define the number field with "Decimal places: 1".

Reading this through the regular Labview-mechanism gives "10.7", or in my local format: "10,7". (like it shows in my local format Excel file)

---

I did some checking in the unpacked xml-files and discovered that the imprecision was introduced by a colleague, not by some inherent Excel-problem. I have asked him to import only rounded values in future.

NB: I would have edited this to my original post but editing older posts seems to be restricted.

[billko]

What do you mean "rounded values"?  Rounded values will produce the same issues as long as you are representing them as floating point numbers.

[Darren]

---

@gkrn wrote:

Hi..

can someone convert to lv17

---

Sure, here you go. A couple of notes:

1) I only tested this with one simple .xlsx file. It worked, but I didn't do the extensive testing on this one that I did on the original. That being said, I can't think of a reason why this 2017 version wouldn't work just like the original does.

2) LabVIEW 2017 doesn't have NXG Style Controls, which I used for the front panel of the original. So the controls look "weird" in 2017.

[Ettepet]

---

@ooth wrote:

@Frozen

My  test Excel file didn't have any empty cells before the data, like the one you provided does. So I made a fix for that a little different than how Darren checked. See if this works for you. Might be faster than Darren's.

---

There were several problems/bugs in this code (empty-field detection criterium bug, escape sequences not implemented, decimal point/komma, etc.).

Here a fully working version in Labview 2018.  It is in my own compact format, so a little harder to read.

(wondering if it remains as fast as yours)

[ooth]

Hi Ettepet,

I'm sure it depends on the Excel file that you use. I tried your code and it took 21.1sec and mine took 0.3sec. But I'm sure my code probably does have the problems you mentioned (empty-field detection criterium bug, escape sequences not implemented, decimal point/komma, etc.). I really just took Darren's original and found where it was taking longer to process.

[Ettepet]

Here is a much faster version.  I just inlined some code.  Thanks for the feedback!

[ooth]

That code is a winner, Ettepet! It took 0.3sec to open my Excel file.

Thanks.