Apart from Aaron advice about database, I would recommend you to take a look at "AWK". It has pretty nice features as far text parsing is concerned.
Let me know if you need any additional help Qasim On 7/18/05, Aaron Rouse <[EMAIL PROTECTED]> wrote: > 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:212136 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
