question

sqlrd10 avatar image
sqlrd10 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.
excelexportsql-server-2012-express
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You say you don't have SSIS. Does this mean you also don't have SSRS?
0 Likes 0 ·
No I do not.
0 Likes 0 ·
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.
0 Likes 0 ·
More to the point, how are you getting on? Have you managed to get this working? If so, which approach did you use?
0 Likes 0 ·
Sorry for the late reply, I ended up connecting via Excel and using a pivot table. Then re-created the output of the query using a few Excel functions. My SQL knowledge is just not that good.
0 Likes 0 ·
database_developer avatar image
database_developer answered
You can use Sql server Import Export Wizard.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes I can do this manually with the wizard but I was looking for a way that would automatically do it for me, even if I had to manually run the query I want the results to export to Excel when I do this.
0 Likes 0 ·
tomgough79 avatar image
tomgough79 answered
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?
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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!
1 Like 1 ·
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
0 Likes 0 ·
example.txt (7.3 KiB)
Thanks for your explanation, I should rephrase that I'm not skilled enough to turn this into a stored proc, not sure if I mentioned but I didn't write the code and I'm far from an expert. I will look at the link to see if I can gleam any help from it, thank you.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.