Solved

Import to SQL 2008 from Excel fails

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
RAISERROR WITH NOWAIT 2 18
install report service in sccm2012 3 19
SQL Availablity Groups Shared Path 2 16
Setting variables in a stored procedure 5 24
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

820 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