Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 23rd, 2005, 10:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default Output to Excel

Does anyone know an easy way to output the results of an SQL Server query in Query Analyzer to an Excel file?

Copy and Paste from a grid loses column headings and date formatting among other things.



Rand
__________________
Rand
Reply With Quote
  #2 (permalink)  
Old June 23rd, 2005, 12:01 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

why not export directly to Excel from SQL using the query that you divised in QA?

Reply With Quote
  #3 (permalink)  
Old June 23rd, 2005, 01:09 PM
Friend of Wrox
 
Join Date: Jan 2004
Location: , , USA.
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OR

in Query Analyzer right click on the results pane, Save As (save as type .csv file)

Then import .csv file into Ms Excel

Reply With Quote
  #4 (permalink)  
Old June 23rd, 2005, 02:16 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The output to a .csv file suffers from the same problem(s) as Copy and Paste from a grid. I can use CAST(DateField AS varchar(11)) to work around the date formatting problems, however the column headings are still missing.

Rand
Reply With Quote
  #5 (permalink)  
Old June 23rd, 2005, 05:18 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Location: Wellington, FL , USA.
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

DTS is your answer. That will easly and effortlessly export your data to excel. You first must get the results of your query into a table to be able to use DTS, but I'm sure you know how to do that.

Scott

Reply With Quote
  #6 (permalink)  
Old July 5th, 2005, 05:33 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

You can achieve that using BCP utility, that can be run from command prompt or from sql query analyser to store the output into a .csv, which can be opened using excel.

For more info on that check the following URLs

Exporting Data Programatically with bcp and xp_cmdshell

Command Prompt Utilities - bcp Utility

Hope that helps.

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #7 (permalink)  
Old July 6th, 2005, 04:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

I just use 'Results to Text' with 'Results Output Format'=Tab Delimited, then it pastes straight into Excel. dates can be sorted with Excel's Cell Format commands, no need to mess about with SQL convert.

hth
Phil
Reply With Quote
  #8 (permalink)  
Old July 6th, 2005, 11:10 AM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

pgtips,

I like your answer. Would you happen to know how to create a stored procedure that will output a csv file, comma delimited. I can get as far as creating an SP to output to csv but not with the delimiter.

Thanks,
Richard

Reply With Quote
  #9 (permalink)  
Old July 7th, 2005, 03:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Richard,

Do you mean the result format in QA? Or do you want to automate it without any manual copy and paste - if so DTS is easier isn't it? I suppose you could use the excel driver along with an insert query, but that seems a bit long-winded to me. Can you elaborate a bit?

rgds
Phil
Reply With Quote
  #10 (permalink)  
Old July 7th, 2005, 09:42 AM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hi Phil,

Yeah, I don't know what I was thinking. You're right, I can create a DTS package to output directly to Excel.

However, one problem that I need to address is to be able to direct query output to specific cells in an Excel template. We need to report to a government agency here in California that requires that we use their Excel template for the report. We need to generate approximately 400 reports per quarter in that format. So, I'm trying to find a way to automate that process through a web application.

Number one, I don't know how or if its possible to format an Excel file with DTS and direct output to specific cells. Number two, how could I start a DTS package through an application with input parameters.

Any thoughts on this or any ideas on how to go about this would be greatly appreciated.

Thanks,
Richard

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Inline Excel Output Filename Sir Guso ASP.NET 1.0 and 1.1 Professional 2 May 31st, 2007 08:26 PM
Output to Excel with selected field beagle Access VBA 1 November 2nd, 2006 09:43 AM
ASP to Excel - excel output formatting issue mat41 Classic ASP Professional 0 August 13th, 2006 06:41 AM
About output data to excel momowu0701 Beginning VB 6 0 March 1st, 2005 09:31 AM
Displaying output in Excel via ASP Sach Classic ASP Components 4 May 3rd, 2004 07:37 AM



All times are GMT -4. The time now is 09:38 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.