Best practice for Uploading Excel data in SQL Server using ASP.NET

Question

Hi All,

I am looking for best practice for uploading excel data in Sql server 2000 database through asp.net web application. Excel data will be predefined Format with almost 42 columns and out of 42 10 fields are mandatory and rest are conditional mandatory. i.e. if data exists it should be in defined format. I also need to validate for special character, length, specified format and so on.

After validating, i need to store valid data into sql server table and provide export to excel functionality for invalid data for exporting in same excel format with indicator to identity the invalid cells.

Can any one suggest me to do the same in optimized way.

Thank you...

Answer 1

You can, but you are, AFAIK, not allowed to use Excel COM Interop on a web server. And it is definately not recommended and supported (source).

So you are left with 2 options:

1. Try to switch to a different format (XML, CSV) or use an Excel XML format, that you can read and write using System.XML or System.XML.Linq.
2. Find a component that can read and write Excel binary files. There are commercial and open source components available.

Answer 2

FileHelpers for .net is a decent library that will do alot of the processing for you if you are looking for something quick and efficient without having to build a ton of it yourself. They have an example of loading excel files into a sql database like you describe.

Answer 3

You can use ADO.NET to read the data in from the spreadsheet, as outlined here.

Read it in to memory and parse all the data as necessary. Store the parsed data into a DataTable, and then you can persist that data in bulk to the database using a couple of possible methods.

The quickest, most efficient way to bulkload data into SQL Server is using SqlBulkCopy. The alternative method is to use an SqlDataAdapter. I recently outlined both approaches, with examples and performance comparisons here.