import csv file/excel into sql database asp.net

Question

Hi everyone! 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 me 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

I have the same situation in a current project I have to do at the moment and I'm using SQL Server Integration Services.

Answer 2

You can easily create an IDataReader over an Excel or CSV file (see http://support.microsoft.com/kb/316934).

Are you using SQL Server as your database engine? If so, you can use the SqlBulkCopy class (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx) to efficiently take your IDataReader, map columns as appropriate, and store the results in your database.

Answer 3

I suspect there may exist some robust and flexible tools to help you with this potentially very complex application that hopefully someone will point you to.

In the mean time, here is a function I have found useful to covert an excel file to a DataTable. This version only looks for the first worksheet. It might serve as a starting point. To to something similar with csv files should only require modifying the connection string.

public static DataTable GetDataTableFromExcel(string SourceFilePath)
{
    string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
           "Data Source=" + SourceFilePath + ";" +
           "Extended Properties=Excel 8.0;";

    using (OleDbConnection cn = new OleDbConnection(ConnectionString))
    {
     cn.Open();

     DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
     if (dbSchema == null || dbSchema.Rows.Count < 1)
     {
      throw new Exception("Error: Could not determine the name of the first worksheet.");
     }

     string WorkSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

     OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + WorkSheetName + "]", cn);
     DataTable dt = new DataTable(WorkSheetName);

     da.Fill(dt);

     return dt;
    }
}

Answer 4

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.

Cheers,Marc

Answer 5

FileHelpers is your friend. I've used it happily for several projects and it has saved me much grief and labour

Answer 6

well, I ve one excel sheet having 9 sheets, each having 40,000 records. it s taking long time to upload. Can anyone suggest a way that is less time consuming.

Regards,

Answer 7

kiev - what solution did you employ in the end? I am in a similar situation and would like to know how did you go about solving the whole issue. Thanks.

Regards, Omer Javed

Answer 8

how did you have the user map the fileds in the ui?

Answer 9

I got similar issue...am really a newbie ...i need to design a UI that will enable instuctors browse and upload csv files and save the files to the different tables in sql server database. Was aable to use ssis to import to some tables but the table that forms a many to many relationship is nt updated can someone help pls

Answer 10

I am using csvReader from LumenWorks.Framework for uploading and importing the csv files into a sql table and a DataTable in memory which I create based on the columns imported.

I also have the user map the fields in the ui and proceed to validate and prepare the data for import by labeling each record as insert/update/error. I then create/populate strongly typed DataSet for each table that will be affected and build the insert/update queries for Enterprise Library UpdateDataset() method.

Then I submit the transaction to insert/update the database. –

The mapping is a grid with 4 columns. ( import field name, destination table, destination field name, ignore, match status), with destination table and field names being selects which refresh based on table selected. I dynamically populate the selects. Initially select combo is populated with 1 value if the match is found, or please select if it isn't. ignore allows user to ignore the field. match status is if a field was auto-mapped