Lost of precision when reading from a spreadsheet file

[Tijarmin]

I really don't know how this happen, but it does: I have to read a simple spreadsheet file wich contain high precision number (14 significant digits): the numbers read by labview (6.0.2) are slightly different than those read by Notepad or Excel (with field precision increased). Could you try it on your side and give me feedback ?

[DavidT]

I think you can go about this 2 ways (there prolly are heaps more though.)

The first is to open the "Read from spreadsheet array.vi" which you are using and convert all the array types to EXT. (LV reads and passes the data as SGL precision to save memory.)

The second is to use the higher level vi's (open, read file, close) and use these to get your data.

Either of these ways should give you the result you are looking for.

Hope this helps.

[altenbach]

The single-precision that is used inside "read from spreadsheet" gives you only about 6 decimal digits of precision. This is a limitation of the data type (23 bit mantissa), but plenty for most "real-world" data.

You apparently want 14 digits of precision and DBL should be sufficient for this (52 bit mantissa = ~15 decimal digit precision). I would highly recommend not to use EXT, many of the advanced analysis VIs don't support it.

Of course you should also ask yourself how many of these digits are really significant. I am not aware of any device that can provide meaningful reults with that kind of resolution. (It's like measuring the distance to the moon with a precision of a few micrometers. Even the roughness of the moon rocks is more than that
!)

In any case, you could just read the file as string then convert to array externally(see attached sample code image), or change the representation of the "array type" inside "read from spreadsheet file", then save it locally under a new name (e.g. "read DBL from spreadsheet file.vi").

[shoneill]

Modifying the "Read from spreadsheet file" to a DBL version is, IMHO, the best version.

I've done this ages ago, and it just saves a lot of hassle.

Is is possible to create a polymorphic VI which upon wiring of a DBL or SGL, loads the correct version?

Shane.

[KileenC]

Hi,

In regards to the loss of precision when using the Read/Write Spreadsheet File.vi, I would also recommend editing the actual VI to input/output a DBL precision value. There is a useful KB discussing this issue:
KB 1RBDAG5P: Precision Errors Writing and Reading with Write to Spreadsheet File and Read from Spre....

Now, to build polymorphic VIs, see Contents and Index in the LabVIEW Help menu:

Complete the following steps to build a polymorphic VI.


1. Create two or more VIs with the same connector pane pattern. Make sure the
connector panes have corresponding input and output terminals
�if a terminal in the connector pane of one VI is an input, the corresponding
terminal on the connector pane of the other VI(s) also must be an input or it
must be unused. The same applies for output terminals. The VIs do not have to
have similar data types or similar subVIs and functions.
2. Select File�New to display the New dialog box.


3. Select the Create New radio button.
4. Select Polymorphic VI in the listbox and click the OK button to display the
Polymorphic VI dialog box.
5. Click the Add VI button to display the Choose the VI to open dialog box.
6. Select a VI to include in the polymorphic VI and click the Open button. The
VI appears at the top of the SubVIs list. This subVI determines the default
connector pane pattern for the polymorphic VI.


7. Repeat steps 5 and 6 to include other VIs. Any VIs that do not conform to the
default connector pane pattern appear with a broken icon. You can still save the
polymorphic VI. To change the order of the subVIs, select a subVI in the list
and click the up arrow or down arrow button.


Note Double-clicking a subVI in the SubVIs list opens the subVI.


8. Click the Edit Icon button to create an icon for the polymorphic VI.
9. Select File�Save to save the VI.


You also can remove a subVI from a polymorphic VI, edit the shortcut menu of a
polymorphic VI, and select the default instance of a polymorphic VI.


Hope this helps!

Kileen C.
Applications Engineer
National Instruments

[altenbach]

It is somewhat unfortunate that many of the old legacy code in LabVIEW contains compromises because they were written at a time when the OS was 16bit, memory was very tight and disk cost was thousands of dollars for a few MBytes.

Today it makes very little sense to bother with SGL, except in very specific situations.

Another example: Why is the default data type for "n" in "scale by power of two" set as I16? I32 would be more reasonable.

[KileenC]

Hi altenbach,

You're absolutely correct. I have submitted a Product Suggestion to have the input "n" in "Scale By Power of 2" changed to I32.

Thanks!

Kileen C.

[buldog]

I face the same problem like Martin Pelettier. I have tried your suggested solutions changing represention of arrays inside read from spreadsheet.vi and changing format&precision digits by right-click menu. I have also tried to use read characters form file.vi + Spreadsheet String To Array function. It still doesn't work I'm receiving only zeros on right side no matter what is set on number of precion in right-click menu of a indicator.
What do I do wrong? it makes me mad. please help thank you

PS I don't know if it's important I'm working on LV 6i

[JeanPierre]

Does the decimal separator used in the file match the one you use in LabVIEW? If not you have to prefix the format with the decimal separator specifier (like "%.;%.3f" or "%,;%.3f") used in the file.

[buldog]

Great!!!
Thank you
I've prefixed a format: "%.;%.3f" and it works now on Win XP.
Why it's not even mentioned in any manual about such a thing? 😕