Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1348
  • Last Modified:

Import to SQL 2008 from Excel fails

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
beboelke
Asked:
beboelke
1 Solution
 
shoppedudeCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now