How do you transfer or export SQL Server 2005 data to Excel

Question

I have a simple SQL 'Select' query, and I'd like to dump the results into an Excel file. I'm only able to save as .csv and converting to .xls creates some super ugly output. In any case, as far as I can tell (using Google) this doesn't seem to be so straight forward. Any help would be greatly appreciated.

Export to text file ("Save Results As" option in SQL Server Management Studio) and import that file into Excel ("Get External Data" from text). I faced problems using the other approaches like SSIS. "External Data from connection" because it doesn't give an option to run a query. — mauryat, Jul 09 '13 at 18:46
Anyone looking for an in depth answer to this, see this http://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file/9086889#9086889 — JonH, Jul 25 '13 at 14:40

score 62 · Answer 1 · answered Sep 17 '08 at 21:17

62

SSIS is a no-brainer for doing stuff like this and is very straight forward (and this is just the kind of thing it is for).

Right-click the database in SQL Management Studio
Go to Tasks and then Export data, you'll then see an easy to use wizard.
Your database will be the source, you can enter your SQL query
Choose Excel as the target
Run it at end of wizard

If you wanted, you could save the SSIS package as well (there's an option at the end of the wizard) so that you can do it on a schedule or something (and even open and modify to add more functionality if needed).

answered Sep 17 '08 at 21:17

Ryan Farley

11,315
4
46
43

5

Excellent! This should be marked as an answer. Very good solution. – Shane Oct 26 '11 at 19:04
Guess SSIS stands for SQL Server Integration Services. How can I start this tool? There is no such thing in programms. – BetaRide May 15 '13 at 06:41
@BetaRide To start it do just as the steps outline: 1) Right-click the database in SQL Management Studio 2) Go to Tasks and then Export data, you'll then see an easy to use wizard. – Ryan Farley May 16 '13 at 15:53
@RyanFarley by using this i got my query result imported into excel file.. is the query result get updated automatically? on excel 'Refresh' button under data tab has no effect on the result – victor Oct 02 '20 at 01:25
@victor no, the excel file is not live data, it's a one time export, it will not update with new data. – Ryan Farley Oct 04 '20 at 16:56
@RyanFarley thanks, I was looking for solution that will refresh the data... I am working with SQL temp tables and it seems there isnt any easy way around – victor Oct 14 '20 at 00:00

Sunny Milenov · Accepted Answer · 2008-09-17T21:25:09.800

41

Use "External data" from Excel. It can use ODBC connection to fetch data from external source: Data/Get External Data/New Database Query

That way, even if the data in the database changes, you can easily refresh.

edited Sep 17 '08 at 21:25

answered Sep 17 '08 at 21:16

Sunny Milenov

21,990
6
80
106

This doesn't work if the computer running excel does not have access to the database. – Shane Oct 26 '11 at 19:05
6

@Shane, yeah, everything works much better after you turn it on ... ;-) – Sunny Milenov Nov 03 '11 at 22:06
3

Heheh, right. I meant if SQL Server was inaccessible from whatever computer was running Excel. In which case, you'd need to use the SSIS solution. – Shane Nov 14 '11 at 03:12
1

Not a good method if you want data from a production database server that won't have excel installed. SSIS solution is much better. – mikesl Jan 24 '13 at 17:01
@MSleman: you don't need Excel installed on the database server in order to fetch data on your desktop – Sunny Milenov Jan 24 '13 at 17:13
@SunnyMilenov Our db servers are behind a firewall so can't run excel locally and connect to them. – mikesl Jan 25 '13 at 08:03
@MSleman If there is a port open for Management studio to connect to the db, it can be used by Excel as well. – Sunny Milenov Jan 25 '13 at 18:06

score 26 · Answer 3 · answered Nov 20 '10 at 13:44

I've found an easy way to export query results from SQL Server Management Studio 2005 to Excel.

1) Select menu item Query -> Query Options.

2) Set check box in Results -> Grid -> Include column headers when copying or saving the results.

After that, when you Select All and Copy the query results, you can paste them to Excel, and the column headers will be present.

score 13 · Answer 4 · edited May 23 '17 at 10:31

See this

This is by far the best post for exporting to excel from SQL:

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

To quote from user madhivanan,

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use these queries

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask 
    @outputfile = 'd:\testing.xls', 
    @query = 'Select * from Database_name..SQLServerTable', 
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)

5 To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns
(
    @db_name    varchar(100),
    @table_name varchar(100),   
    @file_name  varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
    @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
    information_schema.columns
where 
    table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path:

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Its a whomping 29 pages but that is because others show various other ways as well as people asking questions just like this one on how to do it.

Follow that thread entirely and look at the various questions people have asked and how they are solved. I picked up quite a bit of knowledge just skimming it and have used portions of it to get expected results.

To update single cells

A member also there Peter Larson posts the following: I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?

This is the principle of how you do manage that

update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99

You can also add formulas to Excel using this:

update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'

Exporting with column names using T-SQL

Member Mladen Prajdic also has a blog entry on how to do this here

References: www.sqlteam.com (btw this is an excellent blog / forum for anyone looking to get more out of SQL Server).

See this http://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file/9086889#9086889 — JonH, Jul 25 '13 at 14:40

score 5 · Answer 5 · answered Sep 17 '08 at 21:21

5

If you are looking for ad-hoc items rather than something that you would put into SSIS. From within SSMS simply highlight the results grid, copy, then paste into excel, it isn't elegant, but works. Then you can save as native .xls rather than .csv

answered Sep 17 '08 at 21:21

Mitchel Sellers

62,228
14
110
173

I was scrolling to see if anyone had offered this option, as it is clearly the easiest – David Andrei Ned Nov 16 '16 at 16:53

score 5 · Answer 6 · answered Sep 17 '08 at 22:07

5

Here's a video that will show you, step-by-step, how to export data to Excel. It's a great solution for 'one-off' problems where you need to export to Excel:
Ad-Hoc Reporting

answered Sep 17 '08 at 22:07

Michael K. Campbell

1,510
10
13

Excellent video.Thanks a lot :) – Sampath Jul 16 '16 at 05:37

score 4 · Answer 7 · answered Dec 15 '10 at 21:51

It's a LOT easier just to do it from within Excel.!! Open Excel Data>Import/Export Data>Import Data Next to file name click "New Source" Button On Welcome to the Data Connection Wizard, choose Microsoft SQL Server. Click Next. Enter Server Name and Credentials. From the drop down, choose whichever database holds the table you need. Select your table then Next..... Enter a Description if you'd like and click Finish. When your done and back in Excel, just click "OK" Easy.

score 2 · Answer 8 · answered Jul 15 '11 at 09:05

Create the excel data source and insert the values,

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\testing.xls;', 
'SELECT * FROM [SheetName$]') select * from SQLServerTable

More informations are available here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

score 1 · Answer 9 · answered Jul 18 '11 at 17:19

A handy tool Convert SQL to Excel converts SQL table or SQL query result to Excel file without programming.

Main Features - Convert/export a SQL Table to Excel file - Convert/export multiple tables (multiple query results) to multiple Excel worksheets. - Allow flexible TSQL query which can have multiple SELECT statements or other complex query statements.

B. Regards, Alex

score 1 · Answer 10 · answered Oct 08 '11 at 14:44

There exists several tools to export/import from SQL Server to Excel.

Google is your friend :-)

We use DbTransfer (which is one of those which can export a complete Database to an Excel file also) here: http://www.dbtransfer.de/Products/DbTransfer.

We have used the openrowset feature of sql server before, but i was never happy with it, becuase it's not very easy to use and lacks of features and speed...

score 1 · Answer 11 · answered Sep 18 '08 at 12:24

1

You could always use ADO to write the results out to the worksheet cells from a recordset object

answered Sep 18 '08 at 12:24

Russ Cam

124,184
33
204
266

score 0 · Answer 12 · answered Mar 19 '13 at 11:00

Try the 'Import and Export Data (32-bit)' tool. Available after installing MS SQL Management Studio Express 2012.

With this tool it's very easy to select a database, a table or to insert your own SQL query and choose a destination (A MS Excel file for example).

score -1 · Answer 13 · answered Mar 14 '13 at 10:54

you can right click on a grid of results in SQL server, and choose save as CSV. you can then you can import this into Excel.

Excel gives you a import wizard, ensure you select comma delimited. it works fine for me when i needed to import 50k+ records into excel.

score -1 · Answer 14 · edited May 07 '15 at 16:23

-1

Check this.

Query -> Query Options.

Results -> Grid -> Include column headers when copying or saving the results

edited May 07 '15 at 16:23

Ajay2707

5,690
6
40
58

answered May 07 '15 at 13:46

oğuzhan cengiz

1

How do you transfer or export SQL Server 2005 data to Excel

14 Answers14

Linked