CODEWITHSUNDEEP
sql-server-2008ssisspreadsheet
CraigA
CraigA

Reputation: 173

Select specific cells from multiple spreadsheets into SQL using SSIS

I need to loop through a series of spreadsheets (all in the same folder), pulling data from the same cells within the same named range in each, into an existing SQL database, using SSIS (SQL Server 2008 R2).

I started by using the information in How to loop through Excel files and load them into a database using SSIS package? as a point of reference.

However, because my files don't run in a strict columnar format (i.e. the whole of column C plus the whole of column E, etc.), I am struggling with it.

My sheet is as follows:

Spreadsheet

Basically, the area outlined in red (A6:E11) will be the named range (done this way to allow for additional rows as we move forward) and the yellow cells are those that I need to import.
Let's assume that the range will be named "My_Range"

I need to import a row into the database for each of the rows in the range (currently rows 6 through 11).

e.g.
DBase: Col1, Col2, Col3, Col4
Row 1 = B3....B4....C6....E6
Row 2 = B3....B4....C7....E7
Row 3 = B3....B4....C8....E8
etc..

Any help would be greatly appreciated as I need to find the most efficient way to do this for up to 100 files per night.

If you can help me to get the correct data in the correct format from just 1 file, I can work on the multiple-file problem next.

Thanks guys.

Upvotes: 1

Views: 3031

Answers (1)

Dayton Brown
Dayton Brown

Reputation: 1240

One of the nifty things you can do with the Excel source in SSIS is define the actual range you want. So instead of saying I want "Sheet1" Put into the Sheet1$A5:E.

Just ignore the columns you don't want.

Something llke this.

enter image description here

EDIT:

You might want to use an excel script source to grab the first 2 rows if they are always in the same spot.

Upvotes: 3

Related Questions