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
at
Labels:

37 comments:

  1. digvijayOctober 22, 2010 at 11:52 AM

    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.

    ReplyDelete
  2. Rahul KumarOctober 23, 2010 at 10:43 PM

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

    ReplyDelete
  3. LakshJanuary 28, 2011 at 1:44 AM

    Thanks for the post..

    ReplyDelete
  4. Rahul KumarJanuary 28, 2011 at 4:39 PM

    @Laksh : Pleasure..

    ReplyDelete
  5. Ran WeiApril 28, 2011 at 5:22 AM

    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

    ReplyDelete
  6. AnonymousMay 24, 2011 at 11:32 AM

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

    Devendra C.

    ReplyDelete
  7. Rahul KumarMay 26, 2011 at 10:37 AM

    @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.

    ReplyDelete
  8. AnonymousAugust 26, 2011 at 4:39 PM

    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

    ReplyDelete
  9. AnonymousOctober 26, 2011 at 4:01 PM

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

    ReplyDelete
  10. Rahul KumarOctober 27, 2011 at 10:57 AM

    @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

    ReplyDelete
  11. AnonymousJanuary 17, 2012 at 9:00 PM

    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

    ReplyDelete
  12. Rahul KumarJanuary 18, 2012 at 11:47 AM

    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

    ReplyDelete
  13. AnonymousApril 2, 2012 at 8:02 AM

    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.!!

    ReplyDelete
  14. AnonymousApril 12, 2012 at 4:26 PM

    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

    ReplyDelete
  15. UnknownMay 12, 2012 at 1:25 AM

    Thanks a lot!

    ReplyDelete
  16. AnonymousAugust 24, 2012 at 8:57 PM

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

    ReplyDelete
  17. AnonymousOctober 12, 2012 at 2:06 AM

    ""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

    ReplyDelete
  18. Rahul KumarOctober 12, 2012 at 12:32 PM

    Hi Rahil,

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

    SELECT * FROM [Sheet1$A2:A]

    thanks

    ReplyDelete
  19. AnonymousOctober 15, 2012 at 9:25 PM

    Thank you Rahul! It works good!

    Rahil

    ReplyDelete
  20. VajraMarch 12, 2013 at 6:20 AM

    Very Informative..

    Tnq for the post...

    ReplyDelete
  21. AnonymousMarch 29, 2013 at 1:44 AM

    how can I trim each column during the select

    ReplyDelete
  22. Matt PMay 7, 2013 at 12:20 AM

    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?

    ReplyDelete
  23. AnonymousAugust 8, 2014 at 10:13 AM

    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?

    ReplyDelete
  24. UnknownFebruary 6, 2015 at 8:32 PM

    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?

    ReplyDelete
  25. UnknownJuly 12, 2015 at 3:14 AM

    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

    ReplyDelete
  26. UnknownFebruary 9, 2016 at 12:01 PM

    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? 

    ReplyDelete
  27. UnknownFebruary 13, 2016 at 12:34 AM

    thanq bro

    ReplyDelete
  28. sathyaSeptember 27, 2017 at 6:53 PM

    This is a great post. I like this topic.This site has lots of advantage.I found many interesting things from this site. It helps me in many ways.Thanks for posting this again.

    MSBI Training in Chennai

    Informatica Training in Chennai

    Dataware Housing Training in Chennai

    ReplyDelete
  29. UnknownJanuary 20, 2018 at 12:51 AM

    Thanks for the Awesome post.

    I have the sheet name as "ESC PRISM Document Approval Rou" on the excel, i need to skip first 4 rows, so my select is like "select * from [ESC PRISM Document Approval Rou$A4:B] " . But it gets me an error . Any help is much appreciated.

    ReplyDelete
  30. UnknownMay 21, 2018 at 5:05 PM

    Hi There,

    Brilliant article, glad I slogged through the Data Solutions : MSBI, Data Science it seems that a whole lot of the details really come back to from my past project.

    I have recently tried a number of versions of LINUX and UBUNTU. So far I am not impressed with any of them. My major issue is that when it comes to downloading either what is offered in the SOFTWARE MANAGER or using one of the downloaded browsers, nothing I download,
    like OPEN OFFICE for instance, leaves a desktop icon. It typically is left in the MENU and the program must be opened from there.

    Please keep providing such valuable information.

    Thank you,
    Preethi.

    ReplyDelete
  31. GeishadowSeptember 28, 2023 at 4:21 AM

    Cunda
    Mordoğan
    Alibeyli
    Tahtakale
    Akköprü
    D45

    ReplyDelete
  32. ShawnaaOctober 2, 2023 at 8:10 AM

    Diyarbakır
    Adana
    Bursa
    izmir
    Sakarya
    3V6QP3

    ReplyDelete
  33. AytekiinOctober 8, 2023 at 9:05 PM

    görüntülü show
    ücretlishow
    UMS

    ReplyDelete
  34. StardustGoddessXYOLG129October 22, 2023 at 1:21 AM

    Maraş Lojistik
    Hatay Lojistik
    Tokat Lojistik
    Elazığ Lojistik
    Aksaray Lojistik
    EWAR

    ReplyDelete
  35. TimeTravelerOctober 22, 2023 at 11:28 PM

    istanbul evden eve nakliyat
    konya evden eve nakliyat
    düzce evden eve nakliyat
    bursa evden eve nakliyat
    diyarbakır evden eve nakliyat
    7CVSUG

    ReplyDelete
  36. 9AA48Tyrell1EE01November 29, 2023 at 10:47 PM

    E82BC
    %20 komisyon indirimi

    ReplyDelete
  37. BEA67Taylor5F68BFebruary 6, 2024 at 11:08 AM

    D97FB
    poocoin
    ellipal
    raydium
    safepal
    eigenlayer
    metamask
    ledger desktop
    satoshi
    galagames

    ReplyDelete

Subscribe to: Post Comments (Atom)

Google Dataset Search

Google's Vision statement  is “ to provide access to the world's information in one click. ” Google’s mission Statement is “ ...