Store Locator - Mass Import

I have a client that wants me to build a *.csv file upload mechanism that will import store location information.  They haven't been very good about providing well organixed data and I'm concerned that no matter how well a scrit I write to parse through the data to ensure data integrity, I would recommend importing large amounts of data through Enterprise Manager.  I know this is a top level description, but what do all of you think?  What If some non technical person tried to upload a *.csv file with bad data? Thoughts?

coldfusion.developer@att.net wrote: > What If some non technical person > tried to upload a *.csv file with bad data? > > Thoughts? For the State of California, we create a extensive validation routine making extensive use of file inspection and stored procedures to validate the data as best as possible.  When errors are found, a report is generated and returned to the submitting person|organization detailing the problems.  The validation routines does things like count the length of the lines in the data files, ours are column delimited.   The stored procedures are run against the data that is stored in a temporary table created with a bulk upload of the data file.  A history of all errors and changes is kept as well as the original data submitted.

I've built something like this myself and as Ian says its all about your validation routine.  You run it, you test each field with everything you can throw at it (in my case regexes are my friend) and you tell the uploader of problems with the upload operation.  If something gets past the regexes and a sql write fails the reason is logged separately.  Also you may or may not want to skip over error records and continue with the good ones, or fail the whole upload depending on your business rules.  I use the intermediate temp table concept as well to keep the upload the heck away from a live db until its been fully vetted. Its all possible with CF.  You just have to code it. -- --m@Robertson-- Janitor, The Robertson Team mysecretbase.com

coldfusion.developer@att.net wrote: > I have a client that wants me to build a *.csv file upload mechanism that will > import store location information.  They haven't been very good about providing > well organixed data and I'm concerned that no matter how well a scrit I write to > parse through the data to ensure data integrity If you put your validation rules in the database they will be enforced the same through all front-ends (i.e. CF and Enterprise Manager). Just make sure you show a helpful error message to the user. Jochem