Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exporting Data into Excel Sheets Expand / Collapse
Author
Message
Posted Tuesday, November 15, 2011 12:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 12:01 PM
Points: 5, Visits: 3
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
Post #1206241
Posted Tuesday, November 15, 2011 12:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 12:01 PM
Points: 5, Visits: 3
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
Post #1206248
Posted Tuesday, December 6, 2011 4:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 9, 2014 5:59 PM
Points: 22, Visits: 83
SQL query data (or table) can be exported to Excel by using this handy tool.
Export SQL data to Excel

Another way might be you bcp out the data into .csv and convert it to .xls file.

Post #1217492
Posted Tuesday, December 13, 2011 3:13 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 2, 2014 6:39 PM
Points: 496, Visits: 1,123
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



Looking for a Deadlock Victim Support Group..
Post #1221234
Posted Wednesday, December 14, 2011 5:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 11:16 AM
Points: 75, Visits: 622
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".
Post #1221573
Posted Friday, December 16, 2011 3:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 28, 2012 10:52 PM
Points: 27, Visits: 53
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'



I hope this solves your problems.


resume writing | examples of resumes
Post #1222924
Posted Monday, December 19, 2011 1:57 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 306, Visits: 853
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
Post #1224045
Posted Sunday, March 23, 2014 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 27, 2014 3:47 PM
Points: 4, Visits: 28
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/ 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!
Post #1553813
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse