Darren's Occasional Nugget 09/26/2022

[Frozen]

---

@paul_cardinale wrote:

And now this.

---

You broke it Paul. It throws an error in "\XLSx File\XLSx File\Get Cells from Row.vi"

Spoiler

Error -2633 occurred at Property Node (arg 1) in NI_XML.lvlib:Get Node Text Content.vi->XLSx File.lvlib:Get Cells from Row.vi->XLSx File.lvlib:Get Rows from Sheet.vi->XLSx File.lvlib:Get Sheet Data from File.vi->XLSx File.lvlib:Read All Sheets from XLSx File.vi

Possible reason(s):

LabVIEW: (Hex 0xFFFFF5B7) Attempted to get Child Nodes on invalid node refnum.

It works in Darren original code.

[paul_cardinale]

---

@Frozen wrote:

---

@paul_cardinale wrote:

And now this.

---

You broke it Paul. It throws an error in "\XLSx File\XLSx File\Get Cells from Row.vi"

Spoiler

Error -2633 occurred at Property Node (arg 1) in NI_XML.lvlib:Get Node Text Content.vi->XLSx File.lvlib:Get Cells from Row.vi->XLSx File.lvlib:Get Rows from Sheet.vi->XLSx File.lvlib:Get Sheet Data from File.vi->XLSx File.lvlib:Read All Sheets from XLSx File.vi

Possible reason(s):

LabVIEW: (Hex 0xFFFFF5B7) Attempted to get Child Nodes on invalid node refnum.

It works in Darren original code.

---

It works for me.  Send me the spreadsheet that's causing problems.

[Frozen]

Attached is a simplified version of the Excel file that is causing problems for me.

[Frozen]

---

@Frozen wrote:

Attached is a simplified version of the Excel file that is causing problems for me.

---

So I tried coping one of the original sheets to a new file without any of the formats and it no longer throws an error.... So I assume it is one of the many, many applied formats in the original file that is messing things up.

Applied formats: Table, color, filters, first row frozen, word wrap, alignments... to name a few.

However, Darren's original code still works with the original Excel file.... and is noticeable faster.

[paul_cardinale]

.

[Frozen]

---

@ooth wrote:

Thank you Darren! I was looking for something that used the zipped files instead of opening Excel.

I did improve the speed significantly by removing the search for "/>" when it iterates through the cells. Then I replaced the case structure with insert an empty string when the cell was 0.

I commented my changes.

---

@ooth,  I did not time your version because your version introduces a bug where the data is not correct for most of my example spread sheet 😿

Again, Darren's version works with my spread sheet.

[ooth]

@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.

[paul_cardinale]

Here. Bug fixed.

[Frozen]

---

@Frozen wrote:

---

...

However, Darren's original code still works with the original Excel file.... and is noticeable faster.

---

Thank you for the bug fixes.

Now the timing results...

ooth = 0.193 sec
Darren = 0.203 sec
Paul = 73.808 sec

Ooth & Darren version used average of 100 loops. Paul's was a single run 😱

[Ettepet]

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)