Import Excel data to SQL

Question

Import Excel data to SQL

infodemers

SSCommitted

Points: 1513
More actions
April 8, 2010 at 8:04 am

#220292

Hi,
Is there a way using the following syntax, to query the data from the Excel file starting from row 3 only?
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
'SELECT * FROM [Customers$]')
Thanks!

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

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

If this is 2005 or above you can use the row_number function if you are fimilliar with it. you would have to chage you from to a select query that included the row_number and the exclude anything no gretter then three.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

infodemers SSCommitted Points: 1513 More actions · Answer 2

Hi Dan,

Thank you for your suggestion. It is a good one.

I have another problem now...It does not get the column A from my excel sheet...

Bizarre!

Dan.Humphries (4/8/2010)
If this is 2005 or above you can use the row_number function if you are fimilliar with it. you would have to chage you from to a select query that included the row_number and the exclude anything no gretter then three.

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

welcome to the bizzare problems of working with Excel in SQL. I have had many problems getting the two to play nicely. The most bizzare of these was a number column that refused to import as anything but text no matter what I did. Every number in the excel spreadsheet was read with a single quote at the start of the number even though there was not a single quote in the cell.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

infodemers SSCommitted Points: 1513 More actions · Answer 4

Hi Dan,

I found out that if I insert a blank column (A) then it gets correctly all other columns...

I can not figure out why! ! !

Dan.Humphries (4/8/2010)
welcome to the bizzare problems of working with Excel in SQL. I have had many problems getting the two to play nicely. The most bizzare of these was a number column that refused to import as anything but text no matter what I did. Every number in the excel spreadsheet was read with a single quote at the start of the number even though there was not a single quote in the cell.

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 5

somewhat unrelated however i'll mention it anyways. One thing that really bothers me is how much more difficult it is to work w/ excel files in sql 2005 and sql 2008 ssis packages. sql server 2000 dts packages seemed to handle excel much much easier w/ the translations. For some reason it has been my experiance that ssis in 2005 and 2008 have actually made it more work and much more difficult and complex to work w excel files. Why would you design a new solution that make something less user friendly? Sorry. Just venting.

Dan.Humphries SSChampion Points: 12125 More actions · Answer 6

not to turn this into a gripe thread but I know exactly what you are saying. I had to write an SSIS in 2005 that wrote totals from different queries to specific cells in a spreadsheet and that was a total pain. I basically wrote the entire thing in a script transform since it is so difficult to actually write to an excel file unless you want to write to a very generic set of columns like a-e.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.