Post reply

Read excell cell value - ssis

  • November 28, 2011 at 3:22 am

    #247401

    Hi,

    I know how to read a range of data from excel in SSIS.

    How do you read only say cell A6 from excel sheet?

    Note that there is no heading for the cell. It only has a value which I want to read.

    How is this done please?

    For a range I use select * from [sheet name$A3:N5000]

    Thanks

  • November 28, 2011 at 3:34 am

    #1413315

    Does select * from [sheet name$A6:A6] not work? If not, could you select * from [sheet name$A6:B6] and only use the value in the first column?

    John

  • November 28, 2011 at 3:46 am

    #1413325

    They do not work because using $A6:A6 then shows say 21/3/2011

    Then on mapping this to the oledb destination, it is NOT using the column name because there is none but it uses the value to map to the destination table field and this value differs on each file.

  • November 28, 2011 at 7:17 am

    #1413422

    arkiboys (11/28/2011)

    They do not work because using $A6:A6 then shows say 21/3/2011

    Then on mapping this to the oledb destination, it is NOT using the column name because there is none but it uses the value to map to the destination table field and this value differs on each file.

    Does the cell A6 contain the value 21/03/2011?

    Try building the query using the query builder in the Excel Source, and then give the column an alias. You can use that alias in the OLE DB Destination.

    Also make sure you put HDR=no in the connection string.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 28, 2011 at 7:43 am

    #1413449

    How can I alias something like this:

    select * from [sheet name$A6:A6]

  • November 28, 2011 at 11:30 pm

    #1413791

    Don't use *, but actual column names.

    If you do not have a header, the Excel source will take F1, F2, ..., Fn as column names.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 29, 2011 at 3:31 pm

    #1414377

    Can you give an example of an alias please?

  • December 4, 2011 at 11:35 pm

    #1416475

    SELECT F1 AS MyColumn FROM [Sheet1$]

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 5, 2011 at 1:07 am

    #1416491

    Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply