How to export sql server data into multiple excel sheets ?

Question

How to export sql server data into multiple excel sheets ?

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 1

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 SSCrazy Eights Points: 9653 More actions · Answer 2

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 SSCrazy Eights Points: 9333 More actions · Answer 3

(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 Ten Centuries Points: 1156 More actions · Answer 4

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:

[font="Courier New"]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'[/font]

With integrated security:

[font="Courier New"]USP_DMOExportToExcel

@SourceServer='ServerName',

@QueryText = 'use DBTEST Select Field1, Field2 From Table1',

@filename = 'C:\Table1.xls',

@WorksheetName='MyTable1',

@WorksheetIndex=1,

@RangeName ='MyRangeTable1'[/font]

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

Sergio

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 5

BALAJI1 IS A table which would contain the records present in the excel sheet.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

balasach82 (6/12/2008)
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'.

Hilighted code above must be the table name that contains the data that you want to insert into the spreadsheet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Sajid-811795 SSC Veteran Points: 272 More actions · Answer 7

Hi All,

I tried "Openrowset" but it is not working. The error is "Could not find sheet1$". I am using MS Excel 2003 and SQL Server 2005. Please any one have some ideas.

Thanks

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Left my mindreader hat home... please post your code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Sajid-811795 SSC Veteran Points: 272 More actions · Answer 9

Hi Jeff, This is my Code.

insert into OPENROWSET(

'Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Contact.xls;;HDR=YES','SELECT * FROM [Sheet1$]')

select * from testing

My SQL Server is on different server from Excel file.

Thanks

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

In that case, the path to the spreadsheet should probably be a UNC that the SQL server can see... the reference to C: is for the server and won't work because the path you used doesn't actually exist on the server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sapna2310 SSC Journeyman Points: 95 More actions · Answer 11

Is there a way we can mail query results in excel format in sql 2005 without using DTS or SSIS?

jilanibaji.p Valued Member Points: 57 More actions · Answer 12

Hi GermanDBA,

Thanks for you good solutions.

can you please provide the step by step details, how to do the export data to one excel file of multiple sheets using DTS packages?

It is will be a great help!!!!

Thanks in advance.

Thanks

PJB

luisalfonso70 Old Hand Points: 350 More actions · Answer 13

hi; I just tried tu use your code to a similar problem I have, but I recieved the next error:

Msg 102, Level 15, State 1, Line 19

Incorrect syntax near 'Microsoft'.

Msg 102, Level 15, State 1, Line 20

Incorrect syntax near ''

the code is this:

DECLARE @rc int

DECLARE @dt varchar(8)

DECLARE @cmd nvarchar(1000)

declare @newfile nvarchar(100)

SELECT @dt = Convert(varchar(8),getdate(),112)

SET @cmd = 'copy i:\toluca\toluca.xls i:\toluca\toluca_'+@dt+'.xls'

EXEC @rc = master.dbo.xp_cmdshell @cmd

Exec master..xp_cmdshell @cmd

set @newfile='i:\toluca\toluca_'+@dt+'.xls'

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

--set @newfile=''Excel 8.0;Database='+@newfile+';;HDR=YES''

insert into OPENROWSET(

''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='+@newfile+';;HDR=YES'',

''SELECT * FROM [ayunt$]'')

select * from accioral.dbo.mun125

hopr u can help me !! thnaks

M Murphy SSC Enthusiast Points: 176 More actions · Answer 14

sapna2310 (10/21/2008)
Is there a way we can mail query results in excel format in sql 2005 without using DTS or SSIS?

If you have DB_Mail setup, then yes.

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'EmailProfile',

@recipients = 'you@domain.com',

@copy_recipients = 'me@domain.com',

@body = 'Hi Here are the results',

@subject = 'Excel Results',

@file_attachments = 'c:\file.xls';

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 15

Hi Guys,

Its pretty simple...

I was decided to use 5 DFT tasks in side to create multiple sheets in single excel file. I started working on it, mean while I tired to use source and target of the excel properties differently than by default.

Here I sent dynamic sheet name, data, when I ran , all of sudden i got data populated in 10 sheets, 🙂

this logic can apply not only for larger record count, but also for small count for e.g 10 records should goes into 10 sheets in single excel file, pages depends on the total record count / no of rows in each page.

this can be achieved using temp tables and for loop container and one sql connection, and one excel connection.

I have this solution, but not in my lappy, i will try to create a sample and post here, I gave you almost the outline of the solution.

Cheers

RB