Import Entire Excel Worksheet to Table

Question

Import Entire Excel Worksheet to Table

sho-467341

Ten Centuries

Points: 1062
More actions
December 13, 2007 at 6:52 am

#122758

Hi,
I'm trying to import an entire Excel workseet to a table.
I'm using the following query:
SELECT * FROM
OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;
DATABASE=c:\TEMP\Data.xls;HDR=NO',
'SELECT * FROM A1:IV65536'
)
It runs, but will only return as many rows as there is data, so if there are only 5 rows it will only return columns A, B, C, D, E and not all the rows in the worksheet. I've even tried specifying the columns so i've also tried:
SELECT * FROM
OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;
DATABASE=c:\TEMP\Data.xls;HDR=NO',
'SELECT [F1],
[F2],
--ETC...
[F6]
FROM A1:IV65536'
)
But if there's no data in [F6] it throws an error.
Does anyone know how to return everything regardless of whether data exists or not, please?
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Renuka__ Hall of Fame Points: 3656 More actions · Answer 1

Hi,

I have not used OPENROWSET ever, so have no idea. But for the same requirement, I think you can try using DTS. A data transformation task with source as ".xls".

Renuka__

[font="Verdana"]Renuka__[/font]

Powichrowski Marek Hall of Fame Points: 3423 More actions · Answer 2

You are asking on SSIS forum so use Excel Source component from Data Flow task ad redirect data from it to any available destination (table, flat file, excel and so on).