Load Excel to SQL Server using .NET

[Rich Ulichny]

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

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,

Rich

 

[Steve C. Orr [MVP, MCSD]]

The first step is to allow the users to upload the file using the File HTML
control.
Here's more info:
http://SteveOrr.net/articles/EasyUploads.aspx

Once you have the file on your server, you can open it as an ADO.NET
datasource to extract the data.
Here's an example:
http://www.dotnetspider.com/technology/kbpages/1080.aspx

Then (of course) just use standard ADO.NET code to insert it into SQL
Server.

 

[DotNet Coder]

http://www.15seconds.com/issue/030909.htm

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.

HTH,
~d

 

[RJ]

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:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q211378

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?

Thanks,

rj