Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to export sql server data into multiple excel sheets ?


How to export sql server data into multiple excel sheets ?

Author
Message
a1_technology_geb
a1_technology_geb
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
w.durkin@online.de
w.durkin@online.de
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 1879
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
deepti811
deepti811
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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?
Gosta Munktell
Gosta Munktell
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 1408
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.
peter.roethlisberger
peter.roethlisberger
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 75
Fantastic article ! Helped me a lot !
Jim Russell-390299
Jim Russell-390299
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 1403
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.)
balasach82
balasach82
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 1079
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'.
Jim Russell-390299
Jim Russell-390299
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 1403
(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?
sgovoni
sgovoni
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
Attachments
USP_DMOExportToExcel.doc (231 views, 95.00 KB)
balasach82
balasach82
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 1079
BALAJI1 IS A table which would contain the records present in the excel sheet.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search