Stored Procedure to Excel???

Question

Post reply

Stored Procedure to Excel???

Wayne Hans

Mr or Mrs. 500

Points: 518
More actions
December 6, 2005 at 11:51 am

#90086

Does anyone know how to get the result set of a stored procedure into an output file with tab delimited text?
Overall, I want to take the SP and dump results into an Excel file. I can get the results into a text file, but it is not tab delimited. Therefore, when I try to use my DTS package to export to Excel, it doesn't work since there are no delimiters. Since I've been trying to do this now for a few days, any help would be appreciated. Thanks in advance.
--Wayne

Viewing 14 posts - 1 through 13 (of 13 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

In Query Analyzer

Ctrl+Shift+F = Results to File

Menu Tools > Options > ResultsTab and Select Tab Delimited

Execute Your Query (SP)

File window will pop. Choose allfiles and name your file as FileName.XLS (Formats will be general)

OR

Ctrl+T = Results in text

Menu Tools > Options > ResultsTab and Select Tab Delimited

Execute Your Query (SP)

Copy the results and paste in XLS template

You can very well use DTS Package to get a resultset as TAB delimited text.

Regards,
gova

Wayne Hans Mr or Mrs. 500 Points: 518 More actions · Answer 2

Sorry for the confusion, but I need to find some way to do this within an ActiveX Script, since the code will be within an ASP page. I should have mentioned that before.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

This will take a lot of time. I would create an Excel file in the webserver and send it to the browser.

Dim ii , con, rs, stroutput

con = server.createobject("adodb.connection")

rs = server.createobject("adodb.recordset")

stroutput = ""

rs.open(blah, blah)

while not rs.EOF

For ii = 0 to rs.columns.count

stroutput = stroutput & rs.fileds(ii).value & chr(9)

rs.MoveNext

Wend

Response.Clear();

Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");

Response.Charset = "";

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd.xls";

Response.Write(stroutput);

Response.End();

Regards,
gova

Wayne Hans Mr or Mrs. 500 Points: 518 More actions · Answer 4

Well, here's the thing with sending it through the browser. It's a huge query that runs for a few minutes and we are trying to accomplish the task of e-mailing the user the results in an Excel file. I run the SP by adding an activescripting step to the job. The step also sends output to a text file but this file is not delimited and therefore when I try to send it to Excel it doesn't work.

I need to find another solution, but thanks again for the help.

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Try adding a ',' between each column in your select and give your text file a .csv extension.

Example:

Select ColumnA, ',', ColumnB, ',', ColumnC from tableA

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

create a dts package.

select XLS object and connection object from left window.

select a datatransfer task source is connection and destination is xls query is your sp. complete the rest of the steps.

Execute the package from your asp page. EMail the file.

OR

use johnrowan's method to create a csv file

OR

use select columnA + CHAR(9) + ColumnB + char(9) + columnC FROM TableName to get tabdelimited resultset.

Regards,
gova

john-132685 SSCertifiable Points: 6134 More actions · Answer 7

If it's ok with you to have your proc access xp_cmdshell, then do the simple thing:

BCP queryout with the -c option makes it tab/CRLF delimited

(and you may need to stage your results into a ##temp table so BCP can get to it)

master..xp_cmdshell 'BCP "select * from ##tempresult" QUERYOUT path\filename -c -T'

Wayne Hans Mr or Mrs. 500 Points: 518 More actions · Answer 8

We thought BCP was the perfect solution two days ago, but we realized that doesn't work well when your stored procedures use multiple SELECT statements as most of our procedures do. Any solution to this???

john-132685 SSCertifiable Points: 6134 More actions · Answer 9

Have the stored procs put the data into a temp table like I said. It doesn't matter how many steps it takes. Then after you're done, the last thing you do (still in the proc) is BCP the data out from the temp table (again ## instead of #).

Wayne Hans Mr or Mrs. 500 Points: 518 More actions · Answer 10

Thanks for your help, but I'm trying to BCP from outside of the SP. If I added the BCP command to the SP itself, it would solve the problem for this SP only. I have tons of SPs that I would like to use BCP which is why I'm using it from the outside. BCP results in error - 'Invalid object name "XXX"'. I have tried it on SPs with a simple SELECT TOP 100 * FROM TABLEA which works fine, but when I use a SP with multiple select statements... it errors out.

rezaislam SSC Rookie Points: 30 More actions · Answer 11

Im confused. Surely it would be easier to write the results of your SP (Stored Procedure) into a table, thats overwritten each time the DTS is run. Once its in a table you can very easily select that table via Microsoft Query within Microsoft Excel? I've used this several instances, and it seems to work fine! If your SP is essentially just a complex SELECT then MS Query can handle that too, and so you may not even need a SP !

Balamurali-460363 Newbie Points: 5 More actions · Answer 12

I want to export a result from SQL Server 2k5 to Excel, the report is generated in XL, but i want to do by Using SP only...

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 13

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

N 56°04'39.16"
E 12°55'05.25"