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.
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] |
|
|
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 10Incorrect 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 6Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CambridgePubDatabase".Msg 7314, Level 16, State 1, Line 2The 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. |
|
|
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 1The 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? |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
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 |
|
|
|
|
|
|
|