• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2801
  • Last Modified:

Export Data to SQL Table from Excel sheet

Experts,

I have been trying following to Export Data to SQL Table from Excel sheet and both are giving me errors. Excel sheet exists and the path is correct as well.
Since the second option didn't work, have been trying the first option by saving the excel file as .xls.

I would like to get this done through the script instead of DTS or using the wizard, appreciate your help and the question is "the table name (MyTable ) I have specified in the Select should be created before I run this?

I am using excel 2007 and SQL 2005

1. SELECT * INTO MyTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Reports\Data.xls', 'SELECT * FROM [Items$]')
======================================================================
Error for above script
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Items$'.  Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
=======================================================================
2. SELECT * INTO MyTable FROM OPENROWSET('Microsoft.Jet.OLEDB.12.0', 'Excel 12.0;Database=C:\Reports\Data.xlsx', 'SELECT * FROM [Items$]')
=======================================================================
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.12.0" has not been registered.
=====================================================================

Thanks in advance.
0
Tpaul_10
Asked:
Tpaul_10
  • 2
2 Solutions
 
dbaSQLCommented:
If this is a one-time effort, the fastest way to do it is probably the import/export wizard in SSMS:

http://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/
0
 
SThayaCommented:
hi,

 the above issue is related to the ACE provider which is not installed in your machine or server.please check with the below link

You can download and install only the ACE provider  from the following link, this supports office 2007 files both xls and xlsx file.

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now