Exporting to Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Exporting to Excel

Author

Topic

rk4u
Starting Member

8 Posts

Posted - 2001-12-19 : 18:35:51

Could someone guide me in the right direction, I would like to retrieve data from multiple tables in SQL Server 6.5 and export to a excel spreadsheet so that I can analyze the data. This needs to be automated and the spreadsheet must be updated every 15 minutes. I appreciate any suggestions.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-19 : 18:43:45

rk4u,

I recently had to do something very similar....

First option was get a recordset to your middle tier, loop through it and write to Excel...

Second Option was to use DTS on a schedule (Job)

The DTS option was orders of magnitude faster.

Possibly problems..
You need to create different Excel files.. requires a bit of ActiveX scripting...
SQL Server 6.5 DTS engine (Does it have one?)

DavidM

"Why are you crying?"

"Because I love a Microsoft product"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-19 : 18:44:17

You can create a job that runs every 15 minutes to export the data using bcp into a tab or comma-delimited file, which you can then open in Excel, but it won't work with an existing Excel file. If you add formulas and such they will be wiped out the next time the file is generated.

If you have another SQL Server version 7.0 or higher, you could write a DTS job that transfers data from 6.5 to Excel. This might sound dumb but I found this to be very handy and reliable. The DTS job can also be scheduled to run every 15 minutes.

Another option is to create an Excel workbook and use Get External Data to "link" to the SQL Server tables or queries you need. You can refresh the data at any time, but there won't be an automatic update at some specified interval.

Keep in mind that if you have the Excel file open, DTS or bcp jobs will not run successfully because of file sharing violations. Excel is very particular about this. I think the only method that will really work for you is Get External Data.

SNIPED AGAIN! You're killing me David!

Edited by - robvolk on 12/19/2001 18:45:04

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-19 : 18:45:17

You could accomplish this with a scheduled job running a DTS task which does an export to Excel.

BUT, considering the refresh rate, you might want to look at "pulling the data" from Excell rather than "pushing" it from SQL. By that I mean that in Excell you can setup a link into your SQL Server and run a query. This will then be a live picture everytime you do a refresh or open the file. I've done this many times to create Excel Pivot Tables. Look in the Excel help for topics on "External Data Sources" or something like that.

DOUBLE SNIPED!!