x

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.

more ▼

asked May 15, 2013 at 10:51 AM in Default

avatar image

sqlrd10
40 1 1 2

You say you don't have SSIS. Does this mean you also don't have SSRS?

May 16, 2013 at 07:27 AM ThomasRushton ♦♦

No I do not.

May 16, 2013 at 08:44 AM sqlrd10

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.

May 19, 2013 at 10:58 AM Grant Fritchey ♦♦

More to the point, how are you getting on? Have you managed to get this working? If so, which approach did you use?

May 19, 2013 at 11:06 AM ThomasRushton ♦♦

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.

May 22, 2013 at 01:41 PM sqlrd10
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

I suspect it won't work any better for you can BCP, but I'd suggest taking a look at SQLCMD.EXE. 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 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 that might help.

more ▼

answered May 15, 2013 at 02:02 PM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered May 15, 2013 at 11:32 AM

avatar image

tomgough79
1.7k 12 16 21

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

example.txt (7.5 kB)
May 15, 2013 at 11:43 AM sqlrd10

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!

May 15, 2013 at 09:48 PM tomgough79

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.

May 16, 2013 at 08:45 AM sqlrd10
(comments are locked)
10|1200 characters needed characters left

You can use Sql server Import Export Wizard.

more ▼

answered May 15, 2013 at 11:21 AM

avatar image

database_developer
36 3 3 6

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.

May 15, 2013 at 11:25 AM sqlrd10
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 16, 2013 at 07:29 AM

avatar image

ThomasRushton ♦♦
42k 20 51 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x148
x17
x9

asked: May 15, 2013 at 10:51 AM

Seen: 9135 times

Last Updated: May 22, 2013 at 01:41 PM

Copyright 2017 Redgate Software. Privacy Policy