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

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

OR

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

Then import .csv file into Ms Excel

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

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

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

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

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

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

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