asked
How can I automatically run a query and save to Excel (not using BCP/SSIS)
I have a complex query which I wish to run at a set time every day and save the results to Excel but I cannot find an appropriate solution from a vast amount of Googling. I'm using SQL Server 2012 Express remotely and do not have SSIS. I know I can use BCP but it saves as CSV and doesn't display the data correctly at all. I have not attached the query as it is large but I will if needed. I tried running the query from Excel but it didn't like temp tables. Sorry, if I've missed any info please let me know. Any help would be greatly appreciated.
You say you don't have SSIS. Does this mean you also don't have SSRS?
For all helpful answers, click on the thumbs up next to those answers. If any one answer solved the problem, click on the check box next to that answer.
More to the point, how are you getting on? Have you managed to get this working? If so, which approach did you use?
Can you create a stored procedure that returns your results and then write some VBA in Excel to process that output into your Excel sheet?
I don't see any issue with putting that into a stored proc - just finish it off with a select statement that returns the complete final data set. Not sure I understand the logic of dropping and re-creating the function every time though. The VBA side might be a hurdle for you, but perfectly doable. There's an overview here:
http://support.microsoft.com/kb/185125 Then just cycle through the recordset and write it into your spreadsheet Good Luck!
I think what I've got is a bit too complicated to put inside a stored procedure, I'm not sure, I'm not an SQL expert and I didn't write the code, it was passed on to me. I'm also not too good with VBA, but trying to learn when I have a chance. I have attached it if you want to look, doesn't matter if not. [Example][1] [1]: /storage/temp/892-example.txt
I suspect it won't work any better for you can BCP, but I'd suggest taking a look at [SQLCMD.EXE][1]. It has quite a few more functions than BCP and it's the new, better, tool for command line interface with SQL Server. [Here's an article][2] that shows how you can clean up the data a little using SQLCMD. Another option is to use PowerShell. There's going to be a higher learning curve there, but I guarantee you can get the data out of SQL SErver (even Express) and into exactly the format you need. [Here's an article][3] that might help. [1]:
http://msdn.microsoft.com/en-us/library/ms162773.aspx [2]:
http://www.excel-sql-server.com/sql-server-export-to-excel-using-bcp-sqlcmd-csv.htm [3]:
http://www.sqlserver-dba.com/2013/01/sql-server-export-excel-data-to-sql-server-with-powershell.html
A little bit of googling turned up this Simple-Talk page:
https://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/ Hope this helps.
17 People are following this question.
