Improve company productivity with a Business Account.Sign Up

x
?
Solved

Import to SQL 2008 from Excel fails

Posted on 2009-05-08
1
Medium Priority
?
1,369 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 1500 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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

606 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