none
Reading from an Excel source in a custom manner - SSIS 2014-2016 RRS feed

  • Question

  • Hi,

    I need to read data from an Excel file automatically produced by a system.

    The data begin to the 10th row and I cannot how many rows I need to read starting to the 10th row.

    Now, any suggests to me in order to solve this issue, please? Thanks

    Wednesday, November 13, 2019 3:42 PM

All replies

  • Hi pscorca,

    There is a notion of range while dealing with Excel files.

    You can issue a select statement which includes a range statement.

    Along the following: 'SELECT * FROM [Sheet1$A1:C1]'

    You can test it directly in SSMS:

    -- 1. entire sheet
    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml; HDR=NO;
        Database=e:\Temp\Formula.xlsx',
        [Sheet1$]);
    
    -- 2. as rectangular range
    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml;HDR=NO;
        Database=e:\Temp\Formula.xlsx',
        'SELECT * FROM [Sheet1$A1:C1]');


    Wednesday, November 13, 2019 3:52 PM
  • Hi Yitzhak,

    thanks for your reply.

    I need to read a range with an unknown row number to read.

    Is it possible to use a WHERE clause to stop the data read to the first empty row? Thanks

    Wednesday, November 13, 2019 4:07 PM
  • Hi Yitzhak,

    thanks for your reply.

    I need to read a range with an unknown row number to read.

    Is it possible to use a WHERE clause to stop the data read to the first empty row? Thanks

    You can put artificially large number to cover unknown line number to read. So it would be $A10:C1000000


    Wednesday, November 13, 2019 4:12 PM
  • Hi pscorca,

    Method 1: Using the OpenRowset Function

    Method 2: Query Excel Sheet

    Please refer to the following link:

    How to read data from an Excel file starting from the nth row with SQL Server Integration Services

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 14, 2019 2:01 AM
  • Hi Mona,

    thanks for your reply. I've already read the suggested article on mssqltips.com.

    Using an Excel source seems only allowing to read a fixed range. A complete solution contemplates to add a next conditional split to detect the first blank row in order to stop the read operation from the Excel source.

    Friday, November 15, 2019 10:29 AM
  • Hi pscorca,

    Please remember to click "Mark as Answer" the responses that resolved your issue. 

    This can be beneficial to other community members reading this thread.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, November 18, 2019 7:09 AM