Is there any way to generate excel files by using T-SQL?

Question

Is there any way to generate excel files by using T-SQL?

kpao

SSC-Addicted

Points: 406
More actions
April 21, 2006 at 3:34 am

#167854

Is there a function that generate excel files such as foxpro's?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

No there isn't.

afaik there is no immediate way to generate 'real' excelfiles from T-SQL. There is a sort of workaround where you (using xp_cmdshell) when naming the output file use a .xls suffix. If I remember correctly, it does need some 'tweaking' when opened in Excel for the first time though.. (such as 'save as' in order to make it a 'true' Excelfile, and possibly something else, can't remember for sure.

/Kenneth

Riyaz Ghouse SSC-Addicted Points: 464 More actions · Answer 2

yeah.....you can very well create a csv format using...

bulk copy....."bcp exec spname parameters queryout filename -t -r\n -c -slocalhost -U(username) -P(password)"

the username and password shd b provided without braces..

and the above command runs from command prompt....

thatz it..

hope that helps...

Cheers

Riyaz

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 3

If the users of the file will always be "in-house", consider creating a spreadsheet that get's it's data from a stored procedure using a pass through query in External Data.

--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)

Joe Contreras Ten Centuries Points: 1341 More actions · Answer 4

The quickest way is in query analyzer set the output to text and then hit CTRL - A (to select all) - CTRL C. Next open Excel and paste into Excel.