Importing Excel data into SQL Server

• marilyn Beaudreau

  Valued Member

  Points: 60

  More actions

  July 17, 2005 at 2:18 pm

  #186689

  Hi,

  I need to import data from an excel sheet into sql server. I'm attempting to use a SQL script utilizing OLEDB and the JET engine. But I'm having a few issues...

  Because I have mixed data types I think I'm going to need to break the import into sections to be loaded into different sql tables. I need to perform these actions with no input from the user except for them to choose the excel file that contains the input data.

  here's what I've got! It creates a new table with field names retrieved from a specified cell range. It then loads data into the table. BUT only the first row of data is loaded instead of the entire file!?

  ------------------------------------------------------

  select *  

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

  'Excel 8.0;Database=c:\ExcelInputFile.xls;HDR=YES;IMEX=1',

  'SELECT * FROM [RB04244200-A5,B5$B34:F34]')

  Insert into cat_LoadExcel

  Select * FROM OPENROWSET

  ('Microsoft.Jet.OLEDB.4.0',

     'Excel 8.0;Database=c:\ExcelInputFile.xls;HDR=NO;IMEX=1',

     'SELECT * FROM [RB04244200-A5,B5$B35:F35]')

  ----------------------------------------------------------

  Is there a better way to do this?

  thx...

   

• dm

  Ten Centuries

  Points: 1135

  More actions

  July 17, 2005 at 11:39 pm

  #575138

  Using DTS would be MUCH simpler than writing SQL scripts using OPENROWSET...do yourself a favor and look into it. 

  Diane