SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/02/2006 :  19:55:16  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by sanjayanthan

quote:
Originally posted by madhivanan

I dont know where the problem is. Post this as new topic so that other members may help you

Madhivanan

Failing to plan is Planning to fail




i got work around solutions refer
http://www.dnzone.com/ShowDetail.asp?NewsId=234

sanjayanthan


Thanks for sharing that link

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/02/2006 :  19:59:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Philkos, check whether the sheet name is sheet1 or sheet 1 (note that there is space)
Also the file should exist in the server where SQL Server is installed




Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

heze
Posting Yak Master

USA
192 Posts

Posted - 11/03/2006 :  19:10:07  Show Profile  Reply with Quote
hi I am trying to run

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\myServer\myPart\myDira\myDirb\hterms.xls;',
'SELECT * FROM [Sheet1$]') select * from reports..terms


with the folloing results

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].


what can the problem be?

thanks

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/03/2006 :  19:39:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
When you run that query make sure that EXCEL file is closed or it is not used by other applications

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

philkos
Starting Member

Kuwait
5 Posts

Posted - 11/04/2006 :  00:28:27  Show Profile  Reply with Quote
Madhivanan

Sheet name is Sheet1 not Sheet 1 (that i used the correct name,but still it is not working)

Philkos



quote:
Originally posted by madhivanan

Philkos, check whether the sheet name is sheet1 or sheet 1 (note that there is space)
Also the file should exist in the server where SQL Server is installed




Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/04/2006 :  04:13:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
If you have spelled it correctly, it would work. Check that again

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 12/13/2006 :  14:54:31  Show Profile  Visit funketekun's Homepage  Reply with Quote
how do i do it with multiple excel files with different names and in different directories??
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/13/2006 :  15:03:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You use dynamic SQL.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 12/13/2006 :  16:00:53  Show Profile  Visit funketekun's Homepage  Reply with Quote
dynamic sql...?

declare @sql varchar (8000)
set @sql = 'select * into SQLServerTable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=C:\*.xls;HDR=YES'',
''SELECT * FROM [Sheet1$]'')'
print (@sql)
exec (@sql)

Edited by - funketekun on 12/13/2006 16:01:05
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/13/2006 :  16:04:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Exactly.
If you have a specific question, please feel free to post it in the forums.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 12/14/2006 :  05:49:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by funketekun

dynamic sql...?

declare @sql varchar (8000)
set @sql = 'select * into SQLServerTable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=C:\*.xls;HDR=YES'',
''SELECT * FROM [Sheet1$]'')'
print (@sql)
exec (@sql)



Although you executed it using Dynamic sql, it is actually static as path is hard-coded

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 12/14/2006 :  05:52:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
C:\*.xls
>>

Note that you need to supply the path not *. It seems as if you are trying Search in Windows system

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 12/23/2006 :  09:06:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by heze

hi I am trying to run

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\myServer\myPart\myDira\myDirb\hterms.xls;',
'SELECT * FROM [Sheet1$]') select * from reports..terms


with the folloing results

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].


what can the problem be?

thanks




Also refer this
http://support.microsoft.com/default.aspx?scid=kb;EN-US;296711

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dollsangell
Starting Member

1 Posts

Posted - 01/19/2007 :  09:42:05  Show Profile  Reply with Quote
Can any one tell me how to export reports/results after certain conditions are met ,into an excel sheet?????
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 01/19/2007 :  09:44:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This entire topic is about that! You have 10 pages to read...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 01/19/2007 :  10:10:14  Show Profile  Visit spirit1's Homepage  Reply with Quote
but peter... i don't want to read. it hurts too much!



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 01/19/2007 :  10:15:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sadly true...

I wonder what they think about this forum? All get their question answered for free, without hiring a consultant.
I think that calls for some kind of effort from orignal poster, right?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

edvardas
Starting Member

Lithuania
1 Posts

Posted - 03/01/2007 :  05:29:38  Show Profile  Reply with Quote
Hello I have tried to export data to excel, I do not get an errors, but the export not working.. what can the problem be?

Alter Procedure ExportToExcelSheet(@fileName varchar(255),@sheetName varchar(255), @ID int)
as

Declare @sql varchar(1000)
DECLARE @Name varchar(15)

Set @sql ='insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='+@fileName+';HDR=YES'',''SELECT * From ['+@sheetName+'$F1:I1]'')'
select TimeDateStart3, TimeDateEnd3, Time, Status from tblResults where Time is not null and VehicleID = @ID
Exec (@sql)

exec ExportToExcelSheet @fileName='C:\Reports\Template.xls', @sheetName=@Name, @ID=@VeID
Go to Top of Page

praveenkjohn
Starting Member

India
9 Posts

Posted - 03/15/2007 :  09:41:16  Show Profile  Send praveenkjohn an AOL message  Reply with Quote
but what to do if i need to export query dynamically to Excel in which i have to generate the column names automatically in the excel sheet , the thing is i dont have to use T-SQL , any other way in SSIS or anything else

Regards
Praveen John
+91-9895074288
"Frankly, my dear, I don’t give a damn"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 03/15/2007 :  10:21:26  Show Profile  Visit spirit1's Homepage  Reply with Quote
you can always use .net and create your excel sheet from there with excel interop.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000