• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3929
  • Last Modified:

SQL Server 2008 & Express Import from Excel .xls

Hello Experts. I am using SQL Server 2008 Express (ie the free one) and I have the Import/Export Wizard installed as part of the package. Using the Wizard, I am attempting to import a simple excel table of 3 rows by 20 columns. The Wizard sets up the source, and I can see my excel-table data in the Wizard. All good so far. Then for the Chose a Destination page, I can locate my SQL database without problems. I am using Windows Authentication. Next, i use "copy data...one or more tables". My source lists the sheets, and the named_area of cells. I click next to get to Run Package. The box is checked. At the bottom of the Run Package screen is says

 "In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation."

So, do I have to pay $5999 for an SQL license to import data from an excel spreadsheet ?

When I run the operation, I get a green-checked traffic light until I hit Prepare to Execute. Followed by this error message :

TITLE: SQL Server Import and Export Wizard

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.

I have looked online for a solution, but none of them make sense. Please assist if you can. Many thanks, RW

Windows XP, latest SPs, SQL Server 2008, MS Excel 2003 with SPs.
  • 4
  • 4
1 Solution
Mark WillsTopic AdvisorCommented:
Yep, it is a problem. There is no SQL Agent with the express version, and a few other "marketing" decisions as to what is and isn't included.

You can do it via code. That code can be run interactively, created as a stored procedure, created as an "external" script and so on. You do have a few other tools available, like OSQL and SQLCMD with can runs scripts from the command line, and coincidentally, can use Windows Task Scheduler to run those scripts on a frequent basis.

So there are work arounds and you can achieve without having to spend big bucks.

And at those dollars, you would probably get a developer edition $60.00 license (single license strictly for development purposes only) or an MSDN or Technet subscription.

But is not neccessary...

Have a look at the code snippets below, try running them and see how you go. We can then take the next step of using SQLCMD to run the scripts on a scheduled (windows task) basis.

Now some of this will depend on what versions you are running, we will start with Jet given excel 2003 (2007 requires ACE - the upgrade to JET)

-- A few basic approaches... Dynamically using openrowset through to more formally with Linked Server. Could even go to Office Automation procedures - but we won't go that far if not needed.
-- the common way is to use "dynamic" linking to the datasource using a data provider when used in an ad-hoc kind of way
-- and by using providers directly as a "dynamic" linked server - opens Excel directly, then there are a couple of choices...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=c:\order_worksheet.xls;hdr=yes',' select * from [order_worksheet$]') as a
-- or
SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\order_worksheet.xls"; Extended properties=Excel 8.0')...order_worksheet$
-- The more formal method - use a linked server directly to the spreadsheet - by far the preferred method... and in office 2007 might need the ACE drivers not JET.
EXEC sp_addlinkedserver MyExcel, 'Jet 4.0','Microsoft.Jet.OLEDB.4.0','c:\order_worksheet.xls', NULL, 'Excel 5.0;'
--Set up login mappings (just ADMIN - jet wants something).
EXEC sp_addlinkedsrvlogin MyExcel, FALSE, NULL, Admin, NULL
--List the tables in the linked server (these are the worksheet names), don't have to do this, but good to see how excel presents the names
EXEC sp_tables_ex MyExcel
--and now look at the "table" - pretty much the same as any table... just use a four part identifier of <server>.<database>.<schema>.<table> 
-- in this case database and schem do not apply
select * from myexcel...order_Worksheet$
-- or use openquery (can be handy to reduce selections on server side - not really that useful here)
select * from openquery(MyExcel,'select * from [order_Worksheet$]')
-- now do whatever you want with the data using the linked server just like any table
-- oh, and by the way, you can update or insert into excel this way as well...
-- remove server, or leave it for next time - if so, should check to see if server is still available next time using sp_linkedservers
sp_dropserver 'MyExcel', 'droplogins';
-- and just to show you the ACE version of openrowset
Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\order_worksheet.xlsx;HDR=Yes', 'SELECT * FROM [order_worksheet$]') as a         -- just to show office 2007 syntax

Open in new window

Mark WillsTopic AdvisorCommented:
And here is the order_worksheet for you to play with the above. Just remember to rename it with underscores and so on and save in c:\

OCM123Author Commented:
Hi Mark, let me give it a shot and I will be back asap - thanks ! Rod
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

OCM123Author Commented:
Hi Mark, there's no doubt you've answered my question - in the results of my query I see the table. If I may, I'd like to follow up with 3 questions :

1. What if I want to use a named range in the excel worksheet ( let's say the named range covers A1:G20 and is called "data_area_1"

2. How can I amend your code to add the data in "data_area_1" to an existing table called "tbOrders"

3. I'm not familiar with the concept of sp_addlinkedserver : even though your code gives me a table in the results of the query, I cannot see it in Object Explorer.  (I will read about this myself if it's too much to explain here. 1 and 2 are more important and useful to me !)

This is really very helpful, I will of course award you the points but I believe it will close this link if I do it right now ?

I'm new to the exchange, and SQL. So far it seems like a great service

thanks rod
Mark WillsTopic AdvisorCommented:
Hi Rod,

Welcome aboard, and we will try to answer you questions for you...

1) well that is a little challenging, you need to explicitly refer to the columns and rows, pretty much the same as any table (more so than excel terms)
 in that order_worksheet example, say I wanted the range A1:F3 then what I can do is :

select top 3 item,description, [deliver to], [due date], price, qty from myexcel...order_Worksheet$

so for you, it would be the top 20 and whatever the column names are.

2) normally insert the data into a "staging table" which is a fairly "open" transient work table that you can temporarily store the data so you can manipulate a bit easier. So, using the order_worksheet example again, would be something like :

if object_id('tmp_orders_import','U') is not null drop table tmp_orders_import               -- if the tmp table already exists, then drop it

select identity(1,1) as id, * into tmp_orders_import from myexcel...order_Worksheet$       -- notice I import the whole lot and create an identity column as well.
 -- now tidy up the tmp_orders_import where needed

insert tblOrders (column1, column2, column3, column4, column5, column6)
select item,description, [deliver to], [due date], price, qty
from tmp_orders_import                                                                                                  -- if I was very sure, then could be directly from myexcel...order_Worksheet$
where id < 20
and item is not null
and qty is > 0
-- etc

3) the linked server will be under the group "Server Objects" then "Linked Servers" on the left hand side in Object Explorer in SSMS

Now, what about that batch job ?

1) Create a text file, put your SQL script in it, lets call it My_SQL_Sript.sql
2) create a batch job with the one-liner : osql -S %server% -d My_Database -E -i My_SQL_Sript.sql
3) now you can run the batch job from Windows...
4) Read up on the OSQL utility in Books OnLine - There is also the newer incarnation SQLCMD, but OSQL is also supported for now.

OCM123Author Commented:
Wow, thanks. I will give this a go. I was thinking about naming the range on the worksheet because I will be adding new data to my tbOrders table every day, and for each day, there can be somewhere between 900 and 1100 new rows of data. So I could manually change my stored procedure to reflect that day's rowcount, or I could use a macro in the excel sheet (before the import) to create a named range, so then the Stored Proc would not need to be updated.

It seems that this question also relates to the code

where id < 20

in the import into tbOptions table.

what happens if we just set this to "where id < 2000" ? (i.e. a  number much higher than the number of rows we ever expect to import)

I'm going to try to implement your solution as is, and see I can make it work !

Thanks Mark.


Mark WillsTopic AdvisorCommented:
You can set the where clause to what ever range you want. You just need to ensure that the where clause is going to give you exactly those rows that need inserting.

That insert statement assumes totally new rows, you could also put in a "safety" check of

Where id < 2000
and not exists (select null from tblorders o where o.key_fields = tmp_order_import.key_fields)

that way it will not import something that is already in there. And key_fields might be 1 or however many different columns are needed to prevent a duplicate row being posted...

And it has been a pleasure helping, hope you enjoy the rest of your experience with EE.

OCM123Author Commented:
Many thanks Mark, I will give all this a go !
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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