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

Question

Post reply

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

Learning1

SSC Eights!

Points: 898
More actions
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.
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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

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

Learning1 SSC Eights! Points: 898 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

Sounds like you need an asynchronous script component.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 4

Phil Parkin (12/18/2013)
Sounds like you need an asynchronous script component.

But why do you care what the column name is?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 6

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 😛

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Learning1 SSC Eights! Points: 898 More actions · Answer 7

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.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 8

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 9

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

Learning1 SSC Eights! Points: 898 More actions · Answer 10

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)?

Phil Parkin SSC Guru Points: 246686 More actions · Answer 11

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.