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.

• Renuka__

  Hall of Fame

  Points: 3656

  More actions

  December 13, 2007 at 7:05 am

  #759811

  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: 3483

  More actions

  December 14, 2007 at 2:57 am

  #760122

  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).