Excel truncating zeros when I read a worksheet

[Nova2]

I have a problem where when I read cells from an Excel worksheet any trailing zeros are being truncated.  The values in Excel are numeric and I am reading them as strings because of the data manipulation I wish to perform.  The program is generic and is designed to operate on any number of different formats so I can't just use format specifiers.  If the value in the Excel field is .9210 the property node returns .921

I have included a picture of my code that reads the range of data.  I would appreciate any help or suggestions.

[Jordan_M]

Can you post your program and a sample of your data?

[crossrulz]

Are you sure the Excel cells actually have those 0s in the strings?  Excel does have a tendency of applying a format to the data for display, but the actual values, even strings, are not exactly as they appear.

[Nova2]

I have simplified my program for the purposes of you running a test.  I have also included an example Excel worksheet.

[natasftw]

If you import them as numbers, do they show up successfully or are the 0s gone there as well?

The string import might be seeing the 0 as the termination character.  In that case, it'd end the string.  If they import as you'd expect when read as a number, I expect this to be the case.  If they import without the 0s, I'd look into what Crossrulz is suggesting.

[Bob_Schor]

I do not understand your problem.  I wrote a little routine that read the I (letter Capital-i) column from your Workbook.  Note that your formula creates cells that have floating point values with four digits to the right of the decimal point.  The code below exactly retrieves these floating point values:

I will note that the first value in the Column array is 0.91, not 0.9100.  However, this is just a "representation" issue -- if we set the LabVIEW Display properties to show 4 digits and clear "Hide Trailing Zeros", the zeros appear.

Bob Schor

[Nova2]

That is my problem.  When I read the value from the spreadsheet it is .91 and not 0.9100  It is more than a representation issue because I need the code to be dynamic.  I can't just force it to 4 decimal places because the next spreadsheet I open could have 2 digits or 1 after the decimal place.  I need to be able to read what is in the spreadsheet as it is displayed. 

[James.M]

You can grab the cell formatting and use that to format the values that you read if you really need those trailing zeros.

[James.M]

---

@Nova2 wrote:

That is my problem.  When I read the value from the spreadsheet it is .91 and not 0.9100  It is more than a representation issue because I need the code to be dynamic.  I can't just force it to 4 decimal places because the next spreadsheet I open could have 2 digits or 1 after the decimal place.  I need to be able to read what is in the spreadsheet as it is displayed. 

---

Also, you're not "forcing" the values to two decimal places. The ones with 4 places will get pulled as 4 decimal places. The ones with 2 places will get pulled as 2 decimal places. The Excel VIs we're using here will pull whatever each cell has available and won't cut any decimal places off if there is value there.

The 0.9100 is displayed in the spreadsheet as two decimal places unless it's formatted to a specific number of decimals. If you use my code above, you're going to see a problem if the cells aren't formatted to match a specific decimal place like they currently are. For example, if they're set to "General", that formatting string will just output "General". The cells in the range also all need to have the same formatting, or else it will output an empty string. You can increment through each cell if you want to work around this.

[Bob_Schor]

In fact, the number in the cell of your Excel Workbook is 0.91000000000000000310000000...  Most Floating Point numbers (especially those that are not integers) have a value different from most of the "usual" representations.  Depending on the origin of the data, they may be "very close" to a less-precise value (as your number is "very close" to 0.91), but it is still only a representation, and not the value itself.  Another example -- the third number, which "looks like" 0.9157, is really 0.925699999999999995800000... 

If you want to treat the value, keep it as a Float and display it in any fashion you choose.  You should be able to "deduce" the "representation precision" from the values in the Spreadsheet (and thereby figure out that these data were "rounded" to 4 digits of precision) and, if it really matters to you, adjust the precision of the LabVIEW representation accordingly.

Since I don't "get it", I don't see why this matters, I'll leave this to someone else who might be able to figure out what is really bothering you and how to fix it.  To me, Data are Data, and as long as you don't compromise its integrity, how you choose to display it "is in the Eye of the Beholder".

Bob Schor