How do I output query results to excel file from stored procedure

[glenn_barber]

I know how to use the output feature of interactive SQL to export the results of a query to an excel formated file - how could I do the same thing within a stored procedure?

[Baron]

whould this help?

create or replace procedure myprocedure (mytext long varchar, mypath long varchar)
begin
unload select mytext from dummy to mypath;
end
-------------
call myprocedure  ('blabla', 'c:\\temp\\myfile.txt')

[VolkerBarth]

This is an old discussion on that topic, and there I finally chose a different approach to use Excel VBA to select from SQL Anywhere instead of exporting to Excel files. I'm not sure whether v17 offers better opportunities.

If CSV is acceptable, that is easily doable via UNLOAD statements in stored procedures...

You might also use DBISQL in a cmd line, see here, by using xp_cmdshell within a stored procedure.

[glenn_barber]

Thanks Volker - while csv's are great for us developers, they create difficulties for users - and probably the most valuable utility would be a way to to the excel export similarly to how it could be done in interactive SQL. Apparently such a utility exists for Microsoft SQLServer.

We are quite familiar VBA - but also not all account have a Excel license on the server and as you might know VBA syntax changes from release to release....

That said - I will look into your thread re using DBISQL.

[VolkerBarth]

Yes, I surely agree that user prefer "real Excel files" over CSVs.

Apparently such a utility exists for Microsoft SQLServer.

If you are refering to the MS SQL Server Export/Import Wizard (from the SQL Server Data Tools), I'm not sure that tool is anyhow more user-frinedly... and I'm saying that as a developer...

[timcheshire]

I use a VBA Add-In.

I use a generic routine to call a standard fixed parameter stored procedure. The actual SP called is driven by a report table.

I use QueryTables to display the data and even embed XL column and header formatting into the column names. You can get pretty funky results.