Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exporting Data into Excel Sheets


Exporting Data into Excel Sheets

Author
Message
marishasingh82
marishasingh82
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
marishasingh82
marishasingh82
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
SQLALX
SQLALX
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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.
Possinator
Possinator
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

Group: General Forum Members
Points: 502 Visits: 1123
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..
tyson.price
tyson.price
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 642
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".
frankcastle509
frankcastle509
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
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
Sigerson
Sigerson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 1232
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
radu.gheorghiu
radu.gheorghiu
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 230
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search