Solved

Import to SQL 2008 from Excel fails

Posted on 2009-05-08
1
1,288 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
[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
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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