Exporting from SQL to Excel

[ConfusedChap]

Hi

I'm tryintg to export some data from a table into an excel sheet without using DTS. Is there a script or stored procedure that can do this for me?

Thanks

 

[mutley1]

1. Try googling the question.
2. check out

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

HTH

M.

 

[TysonLPrice]

You can also connect to SQL directly from Excel to populate a spreadsheet from a script.

 

[ConfusedChap]

Thanks for the replies.

Mutley, i tried that and came across the page you linked to but for some reason the script used there is not working and i just wondered if anyone else had any ideas.

Tyson, you wouldn't happen to know this script would you...

 

[mutley1]

Hi Confused,

Tyson was saying that if you open Excel you can import directly into the spreadsheet - not really a script, apart from your select statement. You can connect to the DB from Excel and script what data you want to get out.

Cheers,

M.

 

[TysonLPrice]

bring up a blank worksheet in Excel. From the menu click Data then Import External Data then Import Data. Double click +New SQL Server Connection. Enter in your server name and click next. Select your database and any table name and click next. Enter a descriptive name for your database (I use the actual database name) and click finish.

Now from the menu click Data then Import External Data then Import Data then New Database Query. Select your a table and a single column and add it the left side of the menu and click next. Click through the select and sorting criteria until you get to the screen asking you what to do next. Then click on View data or edit query. That will put you into Microsoft Query mode. From the click on the SQL command button on the menu.

From this point you have connected and opened a query. Paste in the query you really want to run. It will populate the spreadsheet using the column names from the table.

If you save the query you can connect to the external source again and the click execute existing queries to use it again.

There may be an easier way to set that up but it's the only one I know.

Have fun!



Have fun!

 

[TysonLPrice]

left side of the menu and click next" I meant right.

 

[ConfusedChap]

wow, i never knew you could do something like that in Excel! Thanks very much Tyson.

This will come in very handy for reports!

But...is there a way you can do this through SQL so, for example i can create a script that other users can use to export a selection of data to excel through that script?

 

[mutley1]

If you aren't going to use the scripts in the article, then I would suggest setting up a job to run a DTS to export to a file (or SSIS if on 2005) and just give the users access to that job.

Cheers,

M.