How to skip more than 2 rows in excel as source while importing

Question

How to skip more than 2 rows in excel as source while importing

PaVeRa22

SSCommitted

Points: 1965
More actions
October 25, 2010 at 11:40 am

#225442

Guys,
How to skip more than 2 rows in excel as source( case 1 first row has column names, but i dont need that header and i wanted to import data from row 3 onwards, case 2: i have data from 3rd row onwards, without header),
i dont see any properly like flat files, skip no of rows etc.
appreciate your help.
thnx!

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

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

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

You can still configure the Excel connection manager to skip the header, but you can't configure it to skip additional rows.

You can try to add a conditional split to remove the unnecessary rows manually. (or you can put everything in a staging table and remove the unwanted rows with a DELETE statement)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

If you inject a row number into you data flow, then by using the condition split transform you can control which rows go to your destination and which don't.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Here is a snippet of code (copied from a post long ago, by Jeff Moden)

It works for me

/*Import Excel OpenRowsetSelect data from excel

note Sheet1$a4:C start at row 4 using columns a thru c */

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Temp\Test2.xls;HDR=yes',

'SELECT * FROM [Sheet1$a4:c]')

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

dg227 SSCrazy Points: 2017 More actions · Answer 4

If you'r able to convert the original excel file into a flat file of some format, you can opt to skip any number of data rows and/or the header row, as needed, in the connection manager. The other option, as mentioned above, is to do a conditional split based on the first field to discard unnecessary records after the header.

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 5

Thank you All,

we did similar to Daz.. suggession

Cheers

RB