Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Skip Header Rows in Excel and Concatenate Excel column names in SSIS Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 10:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:48 AM
Points: 26, Visits: 94
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.


  Post Attachments 
Data.jpg (17 views, 51.19 KB)
Post #1523946
Posted Tuesday, December 17, 2013 11:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 13,630, Visits: 11,501
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1523958
Posted Wednesday, December 18, 2013 12:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:48 AM
Points: 26, Visits: 94
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.
Post #1523967
Posted Wednesday, December 18, 2013 2:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 5,307, Visits: 12,334
Sounds like you need an asynchronous script component.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1523998
Posted Wednesday, December 18, 2013 2:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 5,307, Visits: 12,334
Phil Parkin (12/18/2013)
Sounds like you need an asynchronous script component.


But why do you care what the column name is?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1524000
Posted Wednesday, December 18, 2013 2:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 13,630, Visits: 11,501
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1524001
Posted Wednesday, December 18, 2013 3:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 5,307, Visits: 12,334
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1524006
Posted Wednesday, December 18, 2013 12:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:48 AM
Points: 26, Visits: 94
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.
Post #1524289
Posted Thursday, December 19, 2013 12:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 5,307, Visits: 12,334
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1524455
Posted Thursday, December 19, 2013 12:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 13,630, Visits: 11,501
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1524462
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse