Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

How to export sql server data into multiple excel sheets ? Expand / Collapse
Author
Message
Posted Thursday, May 01, 2008 9:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 29, 2011 5:53 PM
Points: 11, Visits: 85
This is great. I am having trouble actually getting the dynamic execution of the openrowset call. That way I can feed the path variable and sheet name from the loop I created to call a stored procedure with the excel file insert query inside.

Thanks for the reply, I really appreciate it.
Post #494024
Posted Monday, May 05, 2008 12:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:55 AM
Points: 449, Visits: 1,830
Hi there A1_Technology_geb,

show me what you have so far and I will try to give you some pointers.

regards

GermanDBA



Regards,

WilliamD
Post #494794
Posted Wednesday, May 07, 2008 1:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 07, 2008 1:35 AM
Points: 6, Visits: 18
I have create a SSIS package .Now i m trying to schedule this SSIS in sql agent jobs.

but i m geeting error. unable to run it.

pls. help me how can i run this?

steps of creating jobs in sql server 2005 ??????????????????

is it any credential required for this?

Post #496119
Posted Thursday, May 08, 2008 6:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:24 AM
Points: 203, Visits: 1,304
You say that you don't want to use DTS or SSIS.
Have you consider to use Excel and VBA instead?
If you have Excel installed at the "production machine" then think about it.
The nice thing is that you have much more control of Excel formulas and formats
and can combain the figures with filtering and charts. Your result is more "reportlike"
including analysing/filtering/pivoting options if you like.
I do a lot of jobs creating and distributing Excel files and started with DTS and
stored procedures but now I do it all in Excel and VBA.
If you already are familiar with Excel and VBA the step to add ADO with Database functions is not scaring.

Post #497063
Posted Thursday, May 29, 2008 3:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 18, 2013 7:32 AM
Points: 1, Visits: 49
Fantastic article ! Helped me a lot !
Post #508250
Posted Friday, May 30, 2008 5:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
I much prefer treating this requirement as a pull rather than a push. Make each query into a view, and give your user(s) select access to the view.
Then create a workbook, and for each tab, install a "Data/Import External Data/New Database Query" to present the results of each view. Set the query data range properties to "refresh data on file open" (and other settings as you like.)

The advantages:
1. It is easier to implement
2. The user always sees the most current query results
3. To the user, there is no visible difference between a pre-populated workbook and one that gets populated when it is opened.
(Just warn them that changes they try to make to the data by hand will be lost.)
Post #508971
Posted Thursday, June 12, 2008 8:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:40 AM
Points: 375, Visits: 924
I am Balaji new to this discussion. My Code is::

exec xp_cmdshell 'copy C:\templates\BALAJI1.xls C:\export\BALAJI1.xls'


insert into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\export\BALAJI1.xls;;HDR=YES','SELECT * FROM [Sheet1$]')
select * from balaji1


ERROR:: Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'BALAJI1'.
Post #515993
Posted Thursday, June 12, 2008 10:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
(Guesswork follows...)
Does "select * from balaji1" work without the insert statement? (Is balaji1 a table/view in the SQL Server database?) Might you need to further qualify the select statement source with db name, and schema?
Post #516082
Posted Thursday, June 12, 2008 11:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 5:09 PM
Points: 42, Visits: 194
I attach the word document that contains the definition of the stored procedures USP_DMOExportToExcel that allows you to export the result set of a query on Excel.

Call for example:

With SQL Server authentication:

USP_DMOExportToExcel
@SourceServer='ServerName',
@SourceUID= 'UserName',
@SourcePWD = 'Password',
@QueryText = 'use DBTEST Select Field1, Field2 From Table1',
@filename = 'C:\Table1.xls',
@WorksheetName='MyTable1',
@WorksheetIndex=1,
@RangeName ='MyRangeTable1'

With integrated security:

USP_DMOExportToExcel
@SourceServer='ServerName',
@QueryText = 'use DBTEST Select Field1, Field2 From Table1',
@filename = 'C:\Table1.xls',
@WorksheetName='MyTable1',
@WorksheetIndex=1,
@RangeName ='MyRangeTable1'


I hope it is useful, I usually use it for export data.

Sergio


  Post Attachments 
USP_DMOExportToExcel.doc (218 views, 95.00 KB)
Post #516108
Posted Friday, June 13, 2008 5:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:40 AM
Points: 375, Visits: 924
BALAJI1 IS A table which would contain the records present in the excel sheet.
Post #516560
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse