Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Data Checking Software
- Thread starter rudevincy
- Start date
MichaelRed
Programmer
I know of no such (GENERIC) software.
My approach nd generic advice is to always regard any and all import data with extreme suspicion.
always import into a temp table with all fields as text
check for import errors - if there are ANY, reject the import and inform the owner / publisher of the errors. Note that the import cannot and will not be done until the errors are corrected.
assuming there are no import errors, have another temp table(s) with the same structure(s) as the intended target(s).
Insert or append the records from the first temp into the temp(s) with the correct data types.
If there are required limits in the destination tables, run select queries with the limits. Report any errors to the owner / publisher ...
loop until all data is clean
insert / update the new records into the destination table(s).
The above seems like a LOT of effort, and certainly can be for arbitriary inputs. The results, however, are worth the results.
if the owners / publishers are generally the same, they will soon learn that their casual attitude toward the data they own / publish (to you at least) also cause them grief (extra) work and will be somewhat more careful, thus relieving both parties of some effort
you, however, really have the best of the situation as you can at least generally automate the process and thus just run the V&V code aginst the source(s) and either they will run to completion (actually import the data as intended) or reject it with appropiate messages generate (and possibly even sent) to the owner / publisher.
MichaelRed
My approach nd generic advice is to always regard any and all import data with extreme suspicion.
always import into a temp table with all fields as text
check for import errors - if there are ANY, reject the import and inform the owner / publisher of the errors. Note that the import cannot and will not be done until the errors are corrected.
assuming there are no import errors, have another temp table(s) with the same structure(s) as the intended target(s).
Insert or append the records from the first temp into the temp(s) with the correct data types.
If there are required limits in the destination tables, run select queries with the limits. Report any errors to the owner / publisher ...
loop until all data is clean
insert / update the new records into the destination table(s).
The above seems like a LOT of effort, and certainly can be for arbitriary inputs. The results, however, are worth the results.
if the owners / publishers are generally the same, they will soon learn that their casual attitude toward the data they own / publish (to you at least) also cause them grief (extra) work and will be somewhat more careful, thus relieving both parties of some effort
you, however, really have the best of the situation as you can at least generally automate the process and thus just run the V&V code aginst the source(s) and either they will run to completion (actually import the data as intended) or reject it with appropiate messages generate (and possibly even sent) to the owner / publisher.
MichaelRed
We're currently performing an exercise like this at work and we're using a package called Trillium. It's not cheap, but seems very good.
There is also some open source software that may do what you want (I've necer used them, so can't comment on the quality):
Ed Metcalfe.
Please do not feed the trolls.....
There is also some open source software that may do what you want (I've necer used them, so can't comment on the quality):
Ed Metcalfe.
Please do not feed the trolls.....
drbtodd1971
Programmer
- Mar 28, 2007
- 34
- 0
- 0
Could also try this solution, very good but not cheap.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question