November 15, 2011 at 12:07 pm
Dear All,
I am a very new to MS Sql Server. I have worked with MySql database using PhpMyAdmin. I have written a simple query and now I want to export this data into an excel sheet. In PhpMyAdmin it is very easy we have to just check all the rows I want to export and click on the export to excel button.
I am not sure how this can be done in Ms Sql Server 2005.
Thanks,
Marisha
November 15, 2011 at 12:17 pm
I searched for some time and I got an answer.
In Sql Server 2005 Express the import export wizard does not show in the MS Sql Server Management Studio but can be used from.
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe.
I was able to export data in excel.
Hope this helps
Marisha
December 6, 2011 at 4:50 pm
SQL query data (or table) can be exported to Excel by using this handy tool.
Another way might be you bcp out the data into .csv and convert it to .xls file.
December 13, 2011 at 3:13 pm
You can always copy and paste. Just click the corner in the results grid to select all, then copy (ctrl-c), and paste into excel. By default when the content of the result in Grid view is copied, the column headers are not copied. There is an option in SSMS "Include column headers when copying or saving the results" under Tools –> Options --> Query Reults
December 14, 2011 at 5:39 am
Burninator (12/13/2011)
You can always copy and paste. Just click the corner in the results grid to select all, then copy (ctrl-c), and paste into excel. By default when the content of the result in Grid view is copied, the column headers are not copied. There is an option in SSMS "Include column headers when copying or saving the results" under Tools –> Options --> Query Reults
Or just right click in the query results from Management Studio and select "Copy with Headres".
December 16, 2011 at 3:32 am
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_namevarchar(100),
@table_namevarchar(100),
@file_namevarchar(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'
I hope this solves your problems.
December 19, 2011 at 1:57 pm
In SQL Server 2005, right click on a database name, select "Tasks..." and then select "Export Data..."
This runs the DTS Wizard from within the Management Studio.
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
March 23, 2014 at 7:02 am
Hi,
I've found an article which shows you how you can achieve this. You have two methods, one is quick and easy and the other is safer, but both have the same outcome.
You can find the article here: http://sqlbak.com/blog/export-data-from-table-to-xls/[/url] and it details everything you need to do in order to export your data. If you need to do this just once, I recommend using the quick and easy method.
Otherwise, just use option number two.
Good luck!
~ Just some guy trying to tune queries ~
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply