Whats the best method of importing csv files and updating db tables with file values when using a Console application to read emails and attachments

Question

I have a console application that reads emails and their attachments. I save the attachments to disc successfully and now I need to update the stock levels of products in my products table from the csv attachment.

I am looking for the best method of doing this as the CSV file has about 8000 and for this supplier we will have only 1000 products to update. So should i add all to a table delete unwanted or from console application read each line if product in csv in my db table insert into temp table or just do the update straight away?

I dont want to cause bottle necks, timouts etc as these files of 3mb will be collected upto every 10 minutes or so.

to read the csv i am looking at these methods:

• http://www.morgantechspace.com/2013/08/how-to-read-data-from-csv-file-in-c.html
• Inserting Records From Csv File To Database Table (SQL Server)
• Import Comma Delimited data into Data Table

Thanks

Answer 1

I've written a number of similar applications.

In my experience, it's best to treat the CSV files as untrusted - they may be corrupted, the exporting application may have bugs, the data may not be consistent. Many of the validation steps in my systems have required access to data that lives in the SQL database.

So, the steps I usually take:

• (.Net app): check CSV file for consistency (e.g. ask for a checksum from the exporting application, or a row count, or a file size).
• (.Net app): read in CSV file
• (.Net app): for each line in CSV file:
  • validate line (column types and nullability)
  • import line into database holding table.
• Once all records are imported:
• (SQL): run data validation against holding table (foreign key constraints, business/data rules)
• SQL: move data from holding table to production table
• SQL: update other production data as required (e.g. update stock levels).

By using a transaction around the SQL queries, you can roll back the whole import routing if you encounter an error.

This approach allows me to validate the data from an untrusted source against my reference data in SQL, and by doing it against all imported data rather than row by row, it's fast and efficient. Transaction management is relatively straightforward with a small number of SQL statements that affect lots of rows (as opposed to lots of SQL statements affecting one row). Again, this tends to be more efficient.