How can I check to see that a spreadsheet (comma delimited) contains only numbers?

[ijustlovemath]

I'm writing a VI that checks the validity of a user-specified database for merging with a known good database. It basically checks that the path they specified exists, and that the spreadsheet within that files has two columns. It's passed every test (bad delimiters, too many columns, too few columns, etc) except where I put in two values, comma delimited, where one value is 115 (good) and one value is dog (bad). The format specifier on the spreadsheet opener is %f. I've attached the VI to this post. Any help is appreciated.

[ijustlovemath]

Here's the VI. sorry for double post!

[crossrulz]

I would tell the Read From Spreadsheet File to read strings.  This will still allow your other checks to work.  But then you can take your 2D Array into a couple of FOR loops, autoindexing, and check each value with a Scan From String with the format set to %f.  If any return an error, it is invalid.

[ijustlovemath]

So then, did you mean to leave the %f in the read from spreadsheet VI? should it be %s? Also, is my design sound? I worked it up on paper, how it should behave, but does it look like it was done "The Right Way"?

[johnsold]

I do not see where you are testing the values in the spreadsheet. 

The Read from Spreadsheet File.vi with a %f format specifier will return a number for every cell in the spreadsheet file. If the cell contains something ("bad") which cannot be interpretted as a number, it will return the default value (0).

One way to check might be to read the file as an array of strings and use the string functions to parse the values. This may not be a trivial exercise, depending on the allowable range of values and formats for the data.

Lynn

[crossrulz]

---

@ijustlovemath wrote:
So then, did you mean to leave the %f in the read from spreadsheet VI? should it be %s?

---

Yeah, that should be a %s.  That's what happens when you quickly make edits and don't test anything.

It looks like you are testing the right things.  The only thing I would change from what I gave you (other than the %s thing) is only do the formatting check (the FOR loops with the Scan From String) if all of the other checks passed.  This would speed things up a little bit for large files that fail the other items.

[ijustlovemath]

One last thing is that I don't know if my event structure is set to be potentially unsafe. The way it works is that if the the checks fail, but the user wants to enter a new database, the event structure frame does not complete, it opens a new instance because the "where is the database" value change is triggered.

[ijustlovemath]

Lynn, My values should never be 0. Would a simple test just be checking for zeroes? Or is that too computationally expensive for fairly large (20,000 x 2) arrays?

[crossrulz]

---

@ijustlovemath wrote:
Lynn, My values should never be 0. Would a simple test just be checking for zeroes? Or is that too computationally expensive for fairly large (20,000 x 2) arrays?

---

I would just be worried about actually having values of 0 in there legitamately.  Only if you can guarantee without any doubt that you will never have a 0 value could that truely work.

[crossrulz]

---

@ijustlovemath wrote:
One last thing is that I don't know if my event structure is set to be potentially unsafe. The way it works is that if the the checks fail, but the user wants to enter a new database, the event structure frame does not complete, it opens a new instance because the "where is the database" value change is triggered.

---

I think your terminology is messed up here.  You are not opening a new instance.  The event frame is completing.  You are looping back to wait for an event again.  This is a very common practice.  Though, you might want to add a Cancel button and handle it appropriately (a different event case).