Solved

SQL Server 2008 & Express Import from Excel .xls

Posted on 2009-04-14
8
3,838 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
  • 4
  • 4
8 Comments
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Mark, let me give it a shot and I will be back asap - thanks ! Rod
0
 

Author Comment

by:OCM123
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Many thanks Mark, I will give all this a go !
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now