| Author |
 Topic  |
|
|
albertkohl
Aged Yak Warrior
USA
723 Posts |
 Posted - 09/27/2007 : 00:02:36
|
Okay, so i found a helpful thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
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)? |
Edited by - tkizer on 10/09/2007 02:08:27
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
|
|
albertkohl
Aged Yak Warrior
USA
723 Posts |
Posted - 10/09/2007 : 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.
|
Edited by - albertkohl on 10/09/2007 02:01:17 |
 |
|
|
albertkohl
Aged Yak Warrior
USA
723 Posts |
Posted - 10/09/2007 : 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?
|
Edited by - albertkohl on 10/09/2007 02:04:18 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/09/2007 : 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/ |
|
|
|
albertkohl
Aged Yak Warrior
USA
723 Posts |
Posted - 10/09/2007 : 02:11:38
|
| my apologizes i didn’t know it was for working scripts only :o( sorry |
|
|
| |
Topic |
|
|
|
export to NEW excel file
