The way that I did this once is I imported the entire file into a staging table in the database. Then I used queries within the database to verify where data was good/bad so I could post up alerts to the user. For the delimited list of data in one column I used a pipe lined function(in Oracle) to treat it as a column of values when comparing.
On 7/18/05, Dan G. Switzer, II <[EMAIL PROTECTED]> wrote: > > 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 > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212125 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
