Skip n rows while importing from Excel Sheet...

Question

Skip n rows while importing from Excel Sheet...

Ashesh

SSCommitted

Points: 1961
More actions
December 20, 2007 at 2:39 am

#201878

Hi!
I want to skip first few rows from spread sheet while importing into table, as the & column header is @row# 4 & actual data @row#5.
I've explored Openrowset & OpendataSorce. But couldn't find any option.
Thanks in adv.
Ashesh
Thanks,
Ashesh

Viewing 15 posts - 1 through 15 (of 15 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you use a DTS package? iirc, the Excel source allows skipping of rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ashesh SSCommitted Points: 1961 More actions · Answer 2

In this particular case DTS package cann't be used. Still I would like to the way.

Thanks,

Ashesh

Thanks,
Ashesh

dragos_sv Default port Points: 1478 More actions · Answer 3

never used this but maybe it helps:

"FIRSTROW = first_row

Specifies the number of the first row to load. The default is 1. This indicates the first row in the specified data file. The row numbers are determined by counting the row terminators.

" (openrowset, BooksOnLine)

Ashesh SSCommitted Points: 1961 More actions · Answer 4

Thanx for the response. The FIRSTROW option can be used along with the format file only.

Ashesh

Thanks,
Ashesh

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 5

Import into a staging table, use an Identity to capture the order, delete the items with ID < 5.

Reseed the table each time you do this. (DBCC CHECKIDENT)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

No, no... it's much easier than any of that. This is a common problem and DTS frequently messes up imports especially if there are some merged cells above the data. Using OpenRowSet, the answer is simple and fast...

I've attached a spreadsheet similar to what the OP has... save it in C:\Temp and then run the following code in Query Analyzer...

SELECT *

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

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

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

Notice the cell coordinates right next to "Sheet1$" ? The "a4" identifies the left corner of the header row... the "c" identifies the right most column where the data is... and it's intentional that there is no row for the "c" coordinate.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

p.s. Don't forget to close the spreadsheet after you save it or you WILL get an access error... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ashesh SSCommitted Points: 1961 More actions · Answer 8

Thanx, This has met my requirement.

Thanks Again,

Ashesh

Thanks,
Ashesh

archuy2k Valued Member Points: 59 More actions · Answer 9

hi..can you post the complete code and nt just the selct query alone

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

The rest of the code should be pretty simple to figure out... create a table and use the Select I posted as part of an INSERT/SELECT

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ss-710497 Old Hand Points: 338 More actions · Answer 11

I am trying to load the same sample file, it is showing the Error message:

Syntax Error From Class..

Regards,

suresh

J-440512 SSCertifiable Points: 6285 More actions · Answer 12

"

much simpler than any of that...

Notice the cell coordinates right next to "Sheet1$" ? The "a4" identifies the left corner of the header row... the "c" identifies the right most column where the data is... and it's intentional that there is no row for the "c" coordinate. "

wunderbar !

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

suresh (8/19/2008)
I am trying to load the same sample file, it is showing the Error message:
Syntax Error From Class..
Regards,
suresh

I've never seen such an error in SQL Server, so it must be some GUI code you're trying to write... since I've tested the code I provided before I posted it (like I normally do), and you've not posted your code for us to take a look at, all I can say is watch the quotes, commas, and other punctuation in your embedded code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chris.du-toit Old Hand Points: 310 More actions · Answer 14

I also encountered this problem and solved it using an inline view:

DECLARE @SKIPROWS INT

SET @SKIPROWS = 4 --SKIP THE FIRST 4 ROWS

SELECT * FROM(

SELECT

ROW_NUMBER () OVER (ORDER BY (SELECT 1 FROM sysobjects WHERE 11)) AS rowNum,*

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

'Excel 8.0;Database=c:file.xls;HDR=NO;IMEX=1'

,'select * from [sheet1$]')

) as a where rowNum > @SKIPROWS