Comparing input to information in a .csv file using Read from Spreadsheet

[steezeburger]

I need to compare each row of a spreadsheet to input from the user. I can read from the spreadsheet fine, but I am having trouble comparing one row at a time. I know I need to somehow compare each row using a loop, then OR all the results, but I can't seem to figure it out.

Attached is what I have so far.

The cluster with John Smith was used to test the compare.

[mikeporter]

I'm at home right now so I can't look at your code, but unless it is absolutely unavoidable never process an array one element at a time.

Consequently, comparing one row at a time is the absolute worst way of doing what you describe. There are a number of good ways to accomplish what you want. For example, there's a built-in function that searches 1D arrays for a given input and returns -1 if its not found.

You could also do a simple equality comparison between an array of values and a scalar input. The result would be an array of booleans, if any of the bits are true, you've found your answer. The first technique is generally more efficient, but the second one has its place too. Say for example you want to know how many elements equal the input, or you want to find the values exceeding some value.

Can you post a screen shot of your code?

Mike...

[nyc_(is_out_of_here)]

You only need a WHILE loop for the comparison.

Once the user's input and the entry from the row from the spreadsheet do not agree, you don't need to compare anymore.

[steezeburger]

Thank you for the timely reply.

Here is a screen shot.

It's really basic at the moment. I haven't gotten very far with reading from the spreadsheet, ha.

Could you elaborate on the first method you mentioned?

[steezeburger]

Okay, I got something working, but like you said, it doesn't seem like the best way.

[mikeporter]

 The code in this screen shot is one approach but there is some optimization that you can do. I'm assuming that the data in the file is written such that you have a column of names, a column of user ids and a column of pins. Using the spreadsheet read VI like you are using you get a 2D array of strings where each row is one user.

The first thing is to decide what column you want to do your initial search on. I would recommend User ID since you can enforce a requirement that it be unique. So using an array index node, index column 1 from the data you got from the file. To get an entire column, wire a constant to the column selector and leave the row unwired. The results is a 1D array of all the user ids.

Now use a search 1d array node to search the array for the user ID the user entered. If the result of that search is -1, the search was unsuccessful so you don't need to look any further. If the result is anything else its the row where the user ID was found.

Now all you have to do is use another array index function but this time wire a contant with the value 2 to the column selector and the value you got from the search function to the row selector. The result is the pin associated with the user ID. All you have to do now is compare it to the pin that the user entered. If they match the login was good.

Mike...

[crossrulz · Accepted solution]

What exactly is your goal here?  Do you just want to know if you got a match?  Or do you want to know which row matches?

This last solution you have will cause an endless loop if you can't find a match.  Use a FOR loop instead.  Here's the code I would use.

[steezeburger]

@Mike:

Would this be much more efficient than what I was originally thinking? Why is evaluating one element at a time a bad idea? I'm trying to learn the best practice for everything. I really appreciate all your help by the way.

[mikeporter]

this would work, but there is one very large problem: scalability.

If you have 50 users this might work well, but what if you have 5000 or 50,000. Now you have to go through 50000 records to determine that the record you're looking for doesn't exist.

By contrast, the solution I described scales very well.

Mike...

[steezeburger]

I figured this was the issue. Thank you very much!