What's the easiest way to export data to excel from SQL Server 2000.
I want to do this from commands I can type into query analyzer.
I want the column names to appear in row 1.
Add a comment
|
2 Answers
In Query Analyzer, go to the Tools -> Options menu. On the Results tab, choose to send your output to a CSV file and select the "Print column headers" option. The CSV will open in Excel and you can then save it as a .XLS/.XLSX
1 Comment
PBoillot
Thank you for this clear, easy, and correct answer.
Manual copy and paste is the only way to do exactly what you're asking. Query Analyzer can include the column names when you copy the results, but I think you may have to enable that somewhere in the options first (it's been a while since I used it).
Other alternatives are:
- Write your own script or program to convert a result set into a .CSV or .XLS file
- Use a DTS package to export to Excel
- Use bcp.exe (but it doesn't include column names, so you have to kludge it)
- Use a linked server to a blank Excel sheet and INSERT the data
Generally speaking, you cannot export data from MSSQL to a flat file using pure TSQL, because TSQL cannot manipulate anything outside the database (using a linked server is sort of cheating). So you usually need to use some sort of client application anyway, whether it's bcp.exe, dtswiz.exe or your own program.
And as a final comment, MSSQL 2000 is no longer supported (unless your company has an extended maintenance agreement) so you may want to look into upgrading at some point.
2 Comments
cindi
The linked server was the way to go, I didn't rule out cheating in my question :-)
PBoillot
This answer is incorrect. In particular, these assertions are false: "Manual copy and paste is the only way to do exactly what you're asking... Other alternatives are: Write your own script or program to convert a result set into a .CSV or .XLS file ... Generally speaking, you cannot export data from MSSQL to a flat file using pure TSQL, because..." As Joe Stefanelli's answer below shows, Query Analyzer options can easily be changed to output query results to a .csv file. You do not, in fact, need your own script or "kludge" program to accomplish this.