c# - import csv file/excel into sql database asp.net

Question

I am starting a project with asp.net visual studio 2008 / SQL 2000 (2005 in future) using c#.

The tricky part for me is that the existing DB schema changes often and the import files columns will all have to be matched up with the existing db schema since they may not be one to one match on column names. (There is a lookup table that provides the tables schema with column names I will use)

I am exploring different ways to approach this, and need some expert advice. Is there any existing controls or frameworks that I can leverage to do any of this?

So far I explored FileUpload .NET control, as well as some 3rd party upload controls to accomplish the upload such as SlickUpload but the files uploaded should be < 500mb

Next part is reading of my csv /excel and parsing it for display to the user so they can match it with our db schema. I saw CSVReader and others but for excel its more difficult since I will need to support different versions.

Essentially The user performing this import will insert and/or update several tables from this import file. There are other more advance requirements like record matching but and preview of the import records, but I wish to get through understanding how to do this first.

Update: I ended up using csvReader with LumenWorks.Framework for uploading the csv files.

Answer 1

Check out the excellent FileHelpers library - there an article on CodeProject about it, and it's hosted here.

It's pure C#, and it can import just about any flat-file, CSV, and it deals with Excel, too. The import is totally configurable - you can define things like delimiters, rows and/or columns to skip, and so on - lots of options.

I've successfully used it in various projects and it just works flawlessly - highly recommended.