Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export Data to SQL Table from Excel sheet

Posted on 2013-12-04
3
Medium Priority
?
2,735 Views
Last Modified: 2013-12-11
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
Comment
Question by:Tpaul_10
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 200 total points
ID: 39697251
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
 
LVL 11

Accepted Solution

by:
SThaya earned 1200 total points
ID: 39697448
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question