Solved

Import to SQL 2008 from Excel fails

Posted on 2009-05-08
1
1,261 Views
Last Modified: 2013-11-10
I'm attempting to do simple load of an Excel file to a table in SQL 2008 using the Import wizard and I get the following error -

Error 0xc002f210: Preparation SQL Task 1: Executing the query "" failed with the following error: "Retrieving the COM class factory for component with CLSID {19E353EF-DAF4-45D8-9A04-FB7F7798DCA7} failed due to the following error: 80040154.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
       (SQL Server Import and Export Wizard)

Any ideas?

      
0
Comment
Question by:beboelke
1 Comment
 
LVL 2

Accepted Solution

by:
shoppedude earned 500 total points
ID: 24339705
There are multiple issues that can cause this error message.  The simplest is to make sure the SQL Agent Service is running on the database server.  If it is, or if the service is not available (depending on your version of SQL Server) then there is still another, perhaps easier, solution.

You can use Transact-SQL in Query Analyzer to read the data from the Excel spreadsheet directly and use the SQL INSERT command to move the data into the database.

First, make sure you can access the database.  Execute the following command:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=c:\FILE_NAME.xls;hdr=yes',' select * from [TAB_NAME$]') AS SOURCE

Replace c:\FILE_NAME with the full path and file name of the Excel spreadsheet.  Replace TAB_NAME (keeping the [ and $] in place) with the name of the worksheet tab from which you want to import the data.

Next, execute the INSERT command:

INSERT INTO table
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=c:\FILE_NAME.xls;hdr=yes',' select * from [TAB_NAME$]') AS SOURCE

This should move the data into the table.  If the columns in the spreadsheet are not in the same order and of the same type as the destination columns in the destination table you can enhance this SQL as needed.

-Lee
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

831 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