Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Michel Levy
  Where is Michel Levy?
 
 France
 Michel Levy
 To: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: RE: Discussion on Inconsistent Data
Thread ID: 395339 Message ID: 395356 # Views: 38 # Ratings: 1
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Monday, January 13, 2014 10:01:45 PM         
   


> >
> >
> > Hi,
> >
> > I got into something like this a few years back. I ended up
> > doing what Vilhelm mentions. Had to create like a data lookup
> > keywords dictionary.
> >
> > We also when back into the source program and had it modified so
> > things like
> > MEDICAL, BENEFIT,LIFE
> > MEDICAL BENEFIT, LIFE
> > MEDICAL BENEFIT LIFE
> > MEDICALBENEFITLIFE
> > MED BENEFIT,LIFE
> >
> > moving forward were input via pull down list boxes so the
> > spelling at least moving forward would be corrected.
> >
> > The example was a 40,000 record inventory database in Oracle
> > backend database.
> > The inventory one main lookup entry was suppose to be the following
> > main field values separated by a comma.
> > ItemID, Category, SerialNumber
> >
> > You can well image the variety involved here. Of course I had the
> > same issue you are facing.
> >
> > DATA SCRUBBING
> > What I ended up doing was pulling all the records down into an
> > easy to use excel spread sheet. The end 3 column of the spreadsheet
> > had one column for ItemID a dropdown box for the category and the
> > last column to hold the SN value.
> > Had 4 input people actually going through all 40,000 records
> > and doing the data scrubbing.
> >
> > At the end of a 6 week period I had the data to upload into the system
> > to correct and also had a data dictionary that I could also use.
> >
> > I think a starting off point would be to extract the field and then
> > make it unique so now you have each possibility in a table appearing
> > only once. At least you can now scope out the numbers you are dealing
> > with.
> >
> > If you do what I suggest and find out you have 96 combinations then
> > adding additional correct input values in secondary column may not
> > take as long as you think. Once this is down you now have a data
> > lookup intermediate table that will greatly assist you.
> >
> > To get the unique combinations you could got the route of SQL DISTINCT
> > as one possibility.
> >
> > In a VFP table you could suck the data in and create a Unique Index
> > key on the values and then browse these.
> >
> > Pete "the IceMan", from the Great White North of Canada.
> > www.marathongriffincomputers.com
>
> My heart goes out to you; that was quite an ordeal to fix that data. I spoke to a fellow developer here at my client's site who listened concernedly, but he has no direct input to fixing the issue and I am not sure who does. I am told to only do what I am told, not to talk to anyone who has nothing to do with my specific assignments, so I know that the issue will never be resolved. If I make any waves, I would probably hear something like this: "Hey, we've done it like this for 30 years, so why are you the only one complaining?"
>
> What I forgot to mention is that I'll be doing this all in either T-SQL, or C# with T-SQL. It would be so much easier to do the data fixing with FoxPro functions in my opinion, since that is what I know best.
>
> Cecil Champenois, Jr.

--
Hi Cecil,

that's definetly a case where T-SQL is far mor efficient tnan VFP, or C#! Look at full text search (http://technet.microsoft.com/en-us/library/ms142571.aspx)


Michel L

ENTIRE THREAD

Discussion on Inconsistent Data Posted by Cecil Champenois @ 1/13/2014 6:15:46 PM
RE: Discussion on Inconsistent Data Posted by David Mustakim @ 1/13/2014 6:40:05 PM
RE: Discussion on Inconsistent Data Posted by Cecil Champenois @ 1/13/2014 6:46:51 PM
RE: Discussion on Inconsistent Data Posted by Tony Vignone @ 1/13/2014 6:49:00 PM
RE: Discussion on Inconsistent Data Posted by Jun Tangunan @ 1/14/2014 6:36:47 AM
RE: Discussion on Inconsistent Data Posted by Cecil Champenois @ 1/14/2014 3:12:39 PM
RE: Discussion on Inconsistent Data Posted by Tony Vignone @ 1/14/2014 6:58:46 PM
RE: Discussion on Inconsistent Data Posted by Cecil Champenois @ 1/14/2014 7:44:48 PM
RE: Discussion on Inconsistent Data Posted by Tony Vignone @ 1/14/2014 11:12:28 PM
RE: Discussion on Inconsistent Data Posted by Vilhelm-Ion Praisach @ 1/13/2014 7:00:25 PM
RE: Discussion on Inconsistent Data Posted by Tony Vignone @ 1/14/2014 7:00:35 PM
RE: Discussion on Inconsistent Data Posted by Vilhelm-Ion Praisach @ 1/14/2014 7:53:25 PM
RE: Discussion on Inconsistent Data Posted by Tony Vignone @ 1/14/2014 11:08:17 PM
RE: Discussion on Inconsistent Data Posted by Pete Sass @ 1/13/2014 7:55:46 PM
RE: Discussion on Inconsistent Data Posted by Cecil Champenois @ 1/13/2014 9:09:42 PM
RE: Discussion on Inconsistent Data Posted by David Mustakim @ 1/13/2014 9:24:52 PM
RE: Discussion on Inconsistent Data Posted by Cecil Champenois @ 1/13/2014 9:49:26 PM
RE: Discussion on Inconsistent Data Posted by Pete Sass @ 1/13/2014 9:41:48 PM
RE: Discussion on Inconsistent Data Posted by Cecil Champenois @ 1/13/2014 9:51:13 PM
RE: Discussion on Inconsistent Data Posted by Michel Levy @ 1/13/2014 10:01:45 PM
RE: Discussion on Inconsistent Data Posted by Vilhelm-Ion Praisach @ 1/14/2014 6:45:45 AM
RE: Discussion on Inconsistent Data Posted by Jun Tangunan @ 1/14/2014 1:15:20 AM
RE: Discussion on Inconsistent Data Posted by Tore Bleken @ 1/14/2014 8:53:53 AM