Load Excel to SQL Server using .NET


Rich Ulichny

Not sure if this is correct newsgroup but I figured I had to start

We need to build a Web based application to allow users to choose an Excel
file from their hard drive (or other mapped drive). At that point the
following needs to happen:

Upload the Excel File to the Webserver(or other server) and extract certain
data elements from the many tabs in the sheet. The extracted data needs to
be inserted into various SQL Server tables for reporting/query capabilities.
The good news is the worbook is protected so the data can always be found in
the same cells. However, the data is
scattered across various tabs in the workbook.

Some Assumptions based on existing infrastructure are:

SQL Server will be database
..NET will be used for Web Application

Any recommendations for how this can best be accomplished?

One sample application we have uses an ActiveX control that is called via
ASP/.NET page. This app basically opens the workbook and grabs data from
various cells. My thinking was this
could be used as a starting point to grab data, pass it to SQL Server stored
procs whichwould perform inserts into necessary tables.

I am new to .NET/Web programming so I'm looking for any possible pitfalls.
My research into the sample application above brought up some security
issues with installing and using ActiveX applications but I don't have
enough experience to know if that's a show stopper.

Any advice would be appreciated. Thanks,


DotNet Coder


This is a great article about how to utilize DTS packages from C#.

First off, stay away from ActiveX controls. You are asking for a
disaster and a support nightmare.

Secondly, you have two options; Use the options in the article mentioned
above (creating a DTS package in Sql Server to read and import the Excel
file and then calling that DTS package from .Net). Or, use the Microsoft
Office Interop libraries to open the file from the webserver and read
the values, then pass those values to the stored procs.

IMHO, I would go the DTS approach. Much cleaner and the processing of
the data is done where it should be, on the DB server.



Tht was good advice about upload and then use ADO.NET to read the data.
After some experimenting things were looking good until I hit a snag.

The Excel workbook is password protected and I get the following error:

"Could not decrypt file".

I found this link which seems to explain the problem:


It works fine when the password is removed but I am not in a position to
remove the password from the production spreadsheet. Any workarounds or
suggestions for next steps?



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question