Validate a CSV file

  • Thread starter Thread starter J-T
  • Start date Start date
J

J-T

I have a CSV file which contains comma seperated data that I need to
validate against our database.I was thinking about loading that CSV file
into a Typed-Dataset which has our database schema in it and then try to
load the CSV file into that Typed-Dataset and automatically get all the
errors and constarint violations.

1) Is it doable?

2) Because I need to check the foriegn keys as well,Can I loaded the data of
those refferecing tables into my dataset as well?

3)If number 2 is doable ,how should I update my dataset if a key has been
added to the existing refferncing tables?


Thanks a lot for taking your time answering this post in advance.

J-T
 
I suggest using BCP to upload it to a work table and doing the INSERTs from
a SP into a clone of the target table (just schema). When the INSERT
completes, the data is validated and can safely be inserted into the working
table. Then all you have to worry about is the RI issues.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I'm exactly doing this using a DTS package ,but I'd like to give my user the
ability to validate his file using our web application.Now they upload their
files to our system through web application and then using DTS packages we
process them and report them back all the errors in their file ,ofcourse in
DTS we have buiness rules and stuff like that but here I'd like to give my
user the ability to make sure that his file is not malformed,has the
appropriate number of columns,and just validate the CSV file rather than
validation and applying business rules.

Thanks
 
This validation step is where I normally use a csv parser like the one
I happen to sell, http://www.csvreader.com . This way on a row by row
basis, I can run all kinds of validation code, even possibly save the
good results out to another csv file to upload using the DTS package.
That's what I would recommend. I've also in this process mapped
multiple input column names with the same meaning, ie. Qty, Count,
Quantity, etc, to one resulting output column that matched my database
table's column name. You could load the data into a typed dataset if
that will work, but if you're only dealing with one fk constraint, I'd
be more likely to select out just the list of pk's, and then on a row
by row basis, do like a hash lookup to see if it's defined as one of
the pk's. This should give you better performance than loading all the
csv data up into the dataset, and then letting the dataset constraints
give you the errors, which from my experience isn't actually that nice
performancewise.
 
Thanks for your reply,No In my CSV file there are 33 columns which 20 of it would be foriegn key
lookups.

I cannot afford buyinh that product ,is there something free around?

Thanks again
 
Back
Top