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.
This question is related to
sql
sql-server
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).
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).
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.
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).
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
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
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
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
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.
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
You could always use ADO to write the results out to the worksheet cells from a recordset object
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
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...
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).
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.
Check this.
Query -> Query Options.
Results -> Grid -> Include column headers when copying or saving the results
Source: Stackoverflow.com