Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 export to NEW excel file

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-09-27 : 00:02:36
Okay, so i found a helpful thread: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926[/url]

but the thing is, i didnt see anything that talks about exporting to a new excel file. from what i've found, you have to already have an existing file to export to, with matching headers. here's my code:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\testing.xls;HDR=no',
'SELECT * FROM [Sheet1$]') select * from process_temp


so basically, i'm curious if any of you guys know how to export to a new excel file, via text (not bcp or dts)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-27 : 01:06:27
see http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/#first


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-09 : 01:14:09
Okay, so i've been crazy busy, but i just had a little time to re-visit this, and i ran the following code:

/* Now we have it, it is easy */ 

spExecute_ADODB_SQL @DDL='Create table CambridgePubs
(Pubname Text, Address Text, Postcode Text)',
@DataSource ='C:\CambridgePubs.xls'
--the excel file will have been created on the Database server of the
-- database you currently have a connection to

--We could now insert data into the spreadsheet, if we wanted
spExecute_ADODB_SQL @DDL='insert into CambridgePubs
(Pubname,Address,Postcode)
values (''The Bird in Hand'',
''23, Marshall Road, Cambridge CB4 2DQ'',
''CB4 2DQ'')',
@DataSource ='C:\CambridgePubs.xls'

--you could drop it again!
spExecute_ADODB_SQL @DDL='drop table CambridgePubs',
@DataSource ='c:\CambridgePubs.xls'

/* Manipulating Excel data via a linked server
----------------------------------------------

We can now link to the created excel file as follows */

EXEC sp_addlinkedserver 'CambridgePubDatabase',
@srvproduct = '',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\CambridgePubs.xls',
@provstr = 'Excel 8.0;'
GO

EXEC sp_addlinkedsrvlogin 'CambridgePubDatabase', 'false'
GO

--to drop the link, we do this!
--EXEC sp_dropserver 'CambridgePubDatabase', 'droplogins'

-- Get the spreadsheet data via OpenQuery
SELECT * FROM OPENQUERY
(CambridgePubDatabase, 'select * from [CambridgePubs]')
GO
--or more simply, do this
SELECT * FROM CambridgePubDatabase...CambridgePubs

--so now we can insert our data into the Excel Spreadsheet
INSERT INTO CambridgePubDatabase...CambridgePubs
(Pubname, Address, postcode)
SELECT Pubname, Address, postcode FROM ##CambridgePubs


that got me the following errors:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'spExecute_ADODB_SQL'.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CambridgePubDatabase" returned message
"The Microsoft Jet database engine could not find the object 'CambridgePubs'. Make sure the object
exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 6
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"CambridgePubDatabase".
Msg 7314, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CambridgePubDatabase" does not
contain the table "CambridgePubs". The table either does not exist or the current user does not
have permissions on that table.


NE idea? also, i've noticed that after the code is ran, the excel files are locked. if i re-boot
the server, they unlock, but then are curropt.

any help would be much appreciated.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-09 : 02:00:01
Okay, so i played with it a little, and figured out this much:



EXEC sp_addlinkedserver 'testexport',
@srvproduct = '',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\Inetpub\wwwroot\RUAccountable\proclosers\exporttest.xls',
@provstr = 'Excel 8.0;'
GO

delete
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Inetpub\wwwroot\RUAccountable\proclosers\exporttest.xls";Extended properties=Excel 8.0')...sheet1$ where f2='norris'

SELECT * FROM testexport...sheet1$
delete FROM testexport...sheet1$ where f2='norris'




everything but the delete command works, i get the following error:


OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "testexport" returned message
"Deleting data in a linked table is not supported by this ISAM.".
Msg 7345, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "testexport" could not delete
from table "sheet1$". There was a recoverable, provider-specific error, such as an RPC failure.



perhaps i'm going about this the wrong way, my basic goal, is to have a client upload a file
though a web interface. that file basically gets dumped to:
'C:\Inetpub\wwwroot\RUAccountable\proclosers\exporttest.xls'

at that point, vb.net will execute a stored procedure that i want to import the excel file
into a table, then run some code i've already got working, and spit it back out in a .xls format.
from there i'll have VB.Net e-mail the file back to my client.

now, i would like to have the output be dynamic, in other words, i dont want the client to be
totally limited with the number of fields, or where they are placed. i CAN however limit them
if i would like. just not perfered. am i going about this the wrong way? any suggestions/help?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-09 : 02:08:01
Moving this out of the Script library, since this isn't a working script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-09 : 02:11:38
my apologizes i didn’t know it was for working scripts only :o( sorry
Go to Top of Page
   

- Advertisement -