Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Read Excel Cells from SQL Server Integration Services

By Dinesh Asanka,

We all know that it is possible to import a table of data to SQL Server tables using Integration Services (SSIS). It is as easy as A-B-C. But what if you want to read only a cell from an Excel sheet and import that. Let say you have some kind of Excel sheet from which we need to perform a non-traditional import.

In the above Excel sheet, you will see that format is not a row, column format. Instead, you need to read each cell to find the data. For example, if you want to read Customer Name you, need to read the B3 cell. Similarly for the Order Number you need to read cell E3 and for Address, read B4, etc.

So you will not be able to use native SSIS to import this data. Instead there are two options of doing this, which I will show you.

By Using Name Cells

In Excel there is a feature where you can create "name cells". You can create a named cell by selecting Insert->Name->Define as shown below

For each cell, you need to create a Name. After creating a name, you can access that name treating it is as a table. However, from the Excel data source, you cannot create a cross join. So you need to create five Excel sources (for five cells) and create the SSIS package. Not only will you have five data sources, you also need to have Sorts, Merge Joins, which will complicate your SSIS package layout. See the package below as an example.

This is the output

Though this is what we are expecting, to get to that point we need to work hard, which is not worth money. On top of all this, you need to create a name cell in a Excel file, which you have to either do programmatically or use a template of some sort. The template method will not work in the case when you are generating these files from another third party system.

Using the Script Option

As they say, when you need some non-standard operations in SSIS, it is the script component's turn to put his hand up. These are the steps you need to follow to import that data using the Script component.

1.Drag and drop a script component and select "source" as the script option type.

2.By default the script language is Microsoft Visual C# 2008 and I have done this sample with Microsoft Visual Basic 2008. (That is my preference). Change this if you need to.

3.Define your output columns with the correct data type as shown below.

4.Edit the script. In the IDE you should add two references. These are the last two shown below.

I am not sure whether you need both references. However, in my case I had to add both.

5.Finally it is time to write some code. You need the Imports Microsoft.Office.Interop.Excel at the top of the imports section. Then replace the procedure CreateNewOutputRows with following code.

Public Overrides Sub CreateNewOutputRows()
Dim oExcel As Object = CreateObject("Excel.Application")
Dim FileName As String
FileName = Variables.FileName

Dim oBook As Object = oExcel.Workbooks.Open(FileName)
Dim oSheet As Object = oBook.Worksheets(1)

Output0Buffer.AddRow()

Output0Buffer.Address = oSheet.Range("B4").Value
Output0Buffer.CustomerName = oSheet.Range("B3").Value
Output0Buffer.OrderDate = oSheet.Range("E4").Value
Output0Buffer.OrderNumber = oSheet.Range("E3").Value
Output0Buffer.RepName = oSheet.Range("B5").Value

End Sub

6.Now this is our data flow.

Yes, you saw that correctly, no complications whatsoever, thus the same results.

The only issue with the script method is you need to have the Excel runtime installed. Apart from that, this method looks much smarter. If you wish to download the samples for above, you can get them from sky drive

  • Order.xls - Excel where I have add some samples data and defined named cells
  • ReadingCellByDefineName - SSIS Package which reads from the define cell names. You need to change the Excel file path to suit your environment
  • ReadingCellByScriptTask - SSIS Package which reads from a script.You need to change the FilePath variable to suit your environment
Total article views: 13635 | Views in the last 30 days: 49
 
Related Articles
FORUM

how to import Excel's several sheets to DB?

To import an Excel file which has 3 sheets.

FORUM

Import users from excel

Import users create sql logins

FORUM

Script Task to import Excel 12.0 file

I am using SSIS Script Taskto import and excel 12.0 file

FORUM

Import Data from Multiple Excel Sheets using SSIS

Import Data from Multiple Excel Sheets

FORUM

excel is not importing into an application

excel is not importing into an application

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones