Importing from excel "applications" using SSIS

Question

Hello,

I am looking for any tips or resources on importing from excel into a SQL database, but specifically when the information is NOT in column and row format.

I am currently doing some pre-development work for a project, and in most ways I would like to use SSIS for the project. The one area that my research and googling is leaving a big question mark over is the import of the excel sheet.

In short our users are using excel as a simple calculator / application. Values are entered into specific cells in the sheet and then in other cells formulas come up with the final answers. The sheet has been "beautified" to make it easier for users to use (i.e. white space, merged cells, pretty colours, etc). I need a mechanism to get both the raw values and the final answers and import them into a SQL database.

There is a ton of information on the trials and tribulations of importing column and row info into Excel, but have any fellow stack-overflowers had experience with this? Is SSIS appropriate for this (the other viable option would have to be integration into a .NET service).

Thanks!

Answer 1

What about adding a sheet to the workbook that pulls the answers from the calculation sheet and shows them in a tabular format, and then use the magic of SSIS to suck in the values from that new sheet?

If you can't modify the worksheet, you might want to look into Interop services or something where you can specify the exact cells you want to pull data from.

I'd try the first route if possible, though another option might be to simply recreate their Excel calculator as a .NET app...

Answer 2

A SSIS Excel data source uses worksheet or range -- think table with column names. You could also consider using VBA from within Excel to push data into your DB.

Answer 3

SpreadsheetGear for .NET will let you load an Excel workbook, put values in cells, calculate, get results as raw values or formatted text and more. This might be your best option if the data is not suitable for SSIS - but it will require that you use .NET (C#, VB or any .NET language) to automate your process. The advantage of SpreadsheetGear is that you then have a solution which does not depend on anything other than .NET (of course it depends on SpreadsheetGear, but SpreadsheetGear can be deployed royalty free with your application using xcopy deployment or any other deployment method).

You can see live samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC