Thursday, February 19, 2009

Selecting Excel source range in SSIS

Many times I have been asked by SSIS programmers that they face difficulties when they need to select some specified columns and rows or they need to skip first 2 /3 rows in the excel sheet.

Below I will demonstrate how we can select a specified range in an Excel Sheet.

A) Let me take below sheet as an example and select data after first 2 rows



B) Now in Data Flow task, select an Excel source
Configure Data Access Mode as SQL Command
and in SQL Command Text give query as

SELECT * FROM [Sheet1$ range]
SELECT * FROM [Sheet1$A2:B10]



C) Preview



This way user can select any range or any specific cell value in an Excel File

28 comments:

digvijay said...

nice but
it occur error:-
The Microsoft Jet database engine could not find the object ''9765288793 1 $'A2'. Make sure the object exists and that you spell its name and the path name correctly.

Rahul Kumar said...

@DigVijay
you sure you are giving query in correct syntax
SELECT * FROM [Sheet1$ range]
example: SELECT * FROM [Sheet1$A2:B10]

Laksh said...

Thanks for the post..

Rahul Kumar said...

@Laksh : Pleasure..

Ran Wei said...

Can you put specific fields in Excel file into variables in SSIS and load them into a single record in database? Is it possible?

Thanks

Anonymous said...

is there any way i can exclude some range?
e.g. I want to select everything from Sheet1 except range A1:C4

Devendra C.

Rahul Kumar said...

@Devendra C: You cant really specify a range to exclude like you can skip given starting rows in flat file source..
But if
1. Range to skip lies starting row, you can leave them and start you range from say SELECT * FROM [Sheet1$D1:G1000].

2. Range to skip lies in between of sheet, you can use union between two select of upper and lower half
ex SELECT * FROM [Sheet1$A1:G10]
UNION
SELECT * FROM [Sheet1$A15:G1000].
by this you will be skipping rows from 11 to 14.

Anonymous said...

I CAME TO KNOW HOW TO SKIP TOP ROWS BUT CAN ANY ONE TELL ME HOW TO SELECT ALL RECORDS IN A COLUMN,INSTEAD OF SPECIFYING X NO OF RECORDS.
EX: 'SHEET1$'A2:F*
I MEAN I AM SKIPPING 2 ROWS AND I WANT TO SELECT ALL RECORDS HOW CAN I ??????

THABKS IN ADVANCE...
CHANDU

Anonymous said...

Hello,
I want to know how to skip some blank rows when I don't know their position specifically.
Bests,
Valmira

Rahul Kumar said...

@Valimara: use IS NOT NULL for the column in where clause.
ex: suppose you are select from range A1 to B10 and ID is the column name than use
SELECT * FROM [Sheet1$A1:B10]
WHERE ID IS NOT NULL

Anonymous said...

Hi Rahul,

I Need to select the rows dynamically how many no.of rows the excel having other than first two rows.


what i mean is A3:G(last record)
it needs to get the last row number at g column..

Your post is really appreciated....

Regards,
Roshan

Rahul Kumar said...

Hi Roshan,
you got to find out number of rows in some way.. you can use any logic depending upon ur scenario for example if you are sure date ur first column wont have null ( as mostly is),, you can count(1) with NOT NULLs from the worksheet and that will be the last row number for G

Anonymous said...

HI Rahul,

Your post looks cool.But here, I have lot of excel files in the folder and every files has one sheet name as 'emp details'. So, now how can I specify the range here and also skip first five rows in every sheet.?Please help me with this.!!

Anonymous said...

Hi Anon (April 2nd Post). If you only want to skip some leading rows, then you can use the 'Openrowset' property of the Excel Data Flow Source instead (NB. This is a property of the Data Source and NOT the Connection Manager - you still point the Connection Manager at the Excel file as normal). There is an explanation of this here: http://weblogs.asp.net/mikebosch/archive/2007/10/26/ssis-skipping-rows-and-stripping-subtotals.aspx

Unknown said...

Thanks a lot!

Anonymous said...

CAN YOU USE SQL FUNCTION LIKE CAST OR CONVERT WITH THE COLUMNS????

Anonymous said...

""I CAME TO KNOW HOW TO SKIP TOP ROWS BUT CAN ANY ONE TELL ME HOW TO SELECT ALL RECORDS IN A COLUMN,INSTEAD OF SPECIFYING X NO OF RECORDS.
EX: 'SHEET1$'A2:F*
I MEAN I AM SKIPPING 2 ROWS AND I WANT TO SELECT ALL RECORDS HOW CAN I ??????

THABKS IN ADVANCE...
CHANDU""

Any answer to this?? I have my data with column names starting from row no 4 but it is not possible to determine where the rows will end. So
is it possible to do something like the guy is saying which is - "'SHEET1$'A2:F*"
Please reply, thanks
Rahil

Rahul Kumar said...

Hi Rahil,

Yeap.. just dont mention the row number and it will select up to row end
example

SELECT * FROM [Sheet1$A2:A]

thanks

Anonymous said...

Thank you Rahul! It works good!

Rahil

vajra said...

Very Informative..

Tnq for the post...

Anonymous said...

how can I trim each column during the select

Matt P said...

I have multiple cells on a spreadsheet that I have to post data to. One cell is in B1, another in D1, D3, F1, F3, etc. It's a name, address, sales price, etc. How can I take a SQL query and post it into each of the cells using a range?

Anonymous said...

Nice post! Does this work for 32-bit only or it works for 64-bit as well? Do you need to install any driver on 64-bit server?

Sree Sree said...

If i have 3 columns,then i used select * from [sheet1A2:C] Its working but i didnt get all 3 columns,wt can i do?

ppc experts said...

PPC Expert For Tech Support |inbound calls for tech support, pop up calls for tech support Call at: +91 981-142-0083 ppc management experts ppc call provide by osiel web
expertppc

Domain KING said...

Thank you for such a wonderful Information !!
Here is a list of Top LINUX INTERVIEW QUESTIONS

Veritas Cluster Interview Questions

SAMBA Server Interview Questions

Linux FTP vsftpd Interview Questions

SSH Interview Questions

Apache Interview Questions

Nagios Interview questions

IPTABLES Interview Questions

Ldap Server Interview Questions

LVM Interview questions

Sendmail Server Interview Questions

YUM Interview Questions

NFS Interview Questions

Read More at :- Linux Troubleshooting

Pushkaraj Garge said...

http://www.techbrothersit.com/2013/11/ssis-read-excel-cell-value-in-ssis.html

can we get values of two different cells to different variables in single sql task? 

Thirumala kondaiah bojja said...

thanq bro

Post a Comment