Post reply

Skip Header Rows in Excel and Concatenate Excel column names in SSIS

  • December 17, 2013 at 10:57 pm

    #301476

    Hi,

    I am trying to load data from an excel sheet to a sql server table. The first two rows in excel has the page header. I have to concatenate columns from two rows to define a new destination column. Attached screenshot shows the sample source and sample destination. I know how to skip rows of the page header by changing the OpenRowSet value in the Excel connection Manager properties, but I don't know how to concatenate two row values to get the new column name. Can anyone please help me how to do this?

    Thanks in advance.

  • December 17, 2013 at 11:51 pm

    #1674280

    I would read the head as it were data and concatenate the values in a script component.

    One question though: why do you need to fiddle around with the header when you are importing to a SQL Server table?

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

  • December 18, 2013 at 12:01 am

    #1674286

    Koen Verbeeck (12/17/2013)

    I would read the head as it were data and concatenate the values in a script component.

    One question though: why do you need to fiddle around with the header when you are importing to a SQL Server table?

    Koen,

    Thanks for your reply. We are not doing anything with header in the sql table, we just ignore the header part. All we are trying to do is deriving a column with values from different cells.

  • December 18, 2013 at 2:51 am

    #1674314

    Sounds like you need an asynchronous script component.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 18, 2013 at 2:53 am

    #1674315

    Phil Parkin (12/18/2013)

    Sounds like you need an asynchronous script component.

    But why do you care what the column name is?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 18, 2013 at 2:54 am

    #1674316

    Phil Parkin (12/18/2013)

    Phil Parkin (12/18/2013)

    Sounds like you need an asynchronous script component.

    But why do you care what the column name is?

    Are you talking to yourself now? 🙂

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

  • December 18, 2013 at 3:07 am

    #1674321

    Koen Verbeeck (12/18/2013)

    Phil Parkin (12/18/2013)

    Phil Parkin (12/18/2013)

    Sounds like you need an asynchronous script component.

    But why do you care what the column name is?

    Are you talking to yourself now? 🙂

    I get a better response rate if I do 😛

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 18, 2013 at 12:02 pm

    #1674563

    Phil Parkin (12/18/2013)

    Phil Parkin (12/18/2013)

    Sounds like you need an asynchronous script component.

    But why do you care what the column name is?

    Thanks for your reply Phil. Unfortunately, the destination table is designed such that the column names should be combination of the names from Row 3 and Row 4.

  • December 19, 2013 at 12:00 am

    #1674710

    chaseurpuli (12/18/2013)

    Phil Parkin (12/18/2013)

    Phil Parkin (12/18/2013)

    Sounds like you need an asynchronous script component.

    But why do you care what the column name is?

    Thanks for your reply Phil. Unfortunately, the destination table is designed such that the column names should be combination of the names from Row 3 and Row 4.

    You could map a source column called 'chicken' to a destination column called 'squirrel' with no issues at all: column names do not have to match.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 19, 2013 at 12:19 am

    #1674714

    Phil Parkin (12/19/2013)

    chaseurpuli (12/18/2013)

    Phil Parkin (12/18/2013)

    Phil Parkin (12/18/2013)

    Sounds like you need an asynchronous script component.

    But why do you care what the column name is?

    Thanks for your reply Phil. Unfortunately, the destination table is designed such that the column names should be combination of the names from Row 3 and Row 4.

    You could map a source column called 'chicken' to a destination column called 'squirrel' with no issues at all: column names do not have to match.

    +1

    It doesn't help for maintainability, but it is not necessary to map columns with unique names.

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

  • December 19, 2013 at 11:23 pm

    #1675050

    Phil Parkin (12/19/2013)

    chaseurpuli (12/18/2013)

    Phil Parkin (12/18/2013)

    Phil Parkin (12/18/2013)

    Sounds like you need an asynchronous script component.

    But why do you care what the column name is?

    Thanks for your reply Phil. Unfortunately, the destination table is designed such that the column names should be combination of the names from Row 3 and Row 4.

    You could map a source column called 'chicken' to a destination column called 'squirrel' with no issues at all: column names do not have to match.

    Yes, we can match the source column to whatever destination column, but how do I define the destination column name as an expression (concatenation of two row values)?

  • December 19, 2013 at 11:29 pm

    #1675052

    chaseurpuli (12/19/2013)

    Phil Parkin (12/19/2013)

    chaseurpuli (12/18/2013)

    Phil Parkin (12/18/2013)

    Phil Parkin (12/18/2013)

    Sounds like you need an asynchronous script component.

    But why do you care what the column name is?

    Thanks for your reply Phil. Unfortunately, the destination table is designed such that the column names should be combination of the names from Row 3 and Row 4.

    You could map a source column called 'chicken' to a destination column called 'squirrel' with no issues at all: column names do not have to match.

    Yes, we can match the source column to whatever destination column, but how do I define the destination column name as an expression (concatenation of two row values)?

    I don't understand.

    Why 'define' the destination column? Surely it already exists? Just map column x to it and forget about this concatenation malarkey.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 9, 2021 at 7:54 pm

    This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

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