I'm working an data import routine that takes an Excel spreadsheet and is supposed to validate the spreadsheet to make sure that it contains valid data before importing the records into the database.
The problem I'm trying to solve efficiently is how to validate data in a column that is a list of values. So let's say I have a column called "Roles" that might look something like: Row Fruits --- -------------------- 1 apple, orange, grape 2 orange 3 grape, orange 4 orange, apple 5 banana 6 strawberry, banana, apple In a nutshell, I've what I need to do is examine the data in the Fruits column to make sure that none of the rows contain an invalid "fruit". If they do, then I need to know the row that contains invalid information. Preferable I'd like to know the exact fruit that's invalid, but that's not a necessity. I know I can do this on a row-by-row basis, but considering the fact that I could be examining tens of thousands records, I'm hoping I can figure out a more creative approach to this problem. Also, all the data involved (what I need to valid and valid against) are in memory queries--so I'm going to be using QoQ. Has anyone found a creative solution for doing this? Since the "Fruits" will be fairly limited, I'm thinking if I have to go row-by-row, I'll be better off looping through the "Fruits" and finding rows where the Fruit doesn't exist in a list of values, but that identify a value in a "list" via QoQ seems potentially an issue. Does that seem like the best solution, or I missing some more creative? - Dan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212123 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
