[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server 2008 & Express Import from Excel .xls

Posted on 2009-04-14
8
Medium Priority
?
3,887 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:OCM123
[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
  • 4
  • 4
8 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24147231
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;'
GO
 
--Set up login mappings (just ADMIN - jet wants something).
EXEC sp_addlinkedsrvlogin MyExcel, FALSE, NULL, Admin, NULL
GO
 
--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
GO
 
--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$
GO
 
-- 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$]')
GO
 
 
-- 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';
GO
 
 
-- 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

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24147256
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:\


order-worksheet.xls
0
 

Author Comment

by:OCM123
ID: 24148646
Hi Mark, let me give it a shot and I will be back asap - thanks ! Rod
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:OCM123
ID: 24149856
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24150448
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.

0
 

Author Comment

by:OCM123
ID: 24150960
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.

Rod

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 24151291
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.

Cheers,
Mark.
0
 

Author Closing Comment

by:OCM123
ID: 31570186
Many thanks Mark, I will give all this a go !
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

649 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