Migrationg to Excel Worksheets where header is on column 2

Question

Migrationg to Excel Worksheets where header is on column 2

Debbie Edwards

SSCrazy Eights

Points: 9217
More actions
May 10, 2016 at 9:16 am

#307374

Im having to export SQL Data to an xlsx worksheet
However one of the worksheets doesn't have the header on the top row.
A B
1 Input if there is a match Input if Yes
2 Ethnic Group Ref_ID
So the Headers are on Row two.First Row has column names cant be used for this instance because obviously its the second column.
How do you set this up? Ive done some research and I cant find anything that actually works for this scenario
Debbie

Viewing 6 posts - 1 through 5 (of 5 total)

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

Thom A SSC Guru Points: 99128 More actions · Answer 1

Will the columns always be the same amount, for example Columns A through to J? If so, you can set the sheet name to;

[Sheet1$A2:J] This will make SSIS select all data from Columns A to J, starting at row 2. If you have First Row Contains headers ticked, Row 2 (Row 1 from the dataset point of view) will be used as the headers.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246956 More actions · Answer 2

Thom A (5/11/2016)
Will the columns always be the same amount, for example Columns A through to J? If so, you can set the sheet name to;
[Sheet1$A2:J] This will make SSIS select all data from Columns A to J, starting at row 2. If you have First Row Contains headers ticked, Row 2 (Row 1 from the dataset point of view) will be used as the headers.

Note that it's an export to Excel, not from. Does your suggestion work for exports too?

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

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 3

It will always be the same. I'm not quite sure where you would set that information though.

Thom A SSC Guru Points: 99128 More actions · Answer 4

Phil Parkin (5/11/2016)
Thom A (5/11/2016)
Will the columns always be the same amount, for example Columns A through to J? If so, you can set the sheet name to;
[Sheet1$A2:J] This will make SSIS select all data from Columns A to J, starting at row 2. If you have First Row Contains headers ticked, Row 2 (Row 1 from the dataset point of view) will be used as the headers.
Note that it's an export to Excel, not from. Does your suggestion work for exports too?

Sorry, I had misread. Not quite, but not far off. Instead use the Data access mode SQL Command and enter

Select *

from [Sheet1$A2:J]

Where Sheet1 is the name of your sheet, A2 is the starting cell, and J is the end column. This is in your Excel Destination Node.

That'll skip the first row instead.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 5

I will certainly have a look at that. I didn't know you could use the Sql command when you are using an excel destination source. I thought you could only choose your excel file and then choose the worksheet.

Debbie