SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

navbingo20
Starting Member

12 Posts

Posted - 04/29/2011 :  06:51:55  Show Profile  Reply with Quote
its working now.. thnx alot for the code..
i made a few corrections in the replace statement..

(1)
all the columns are displayed in the first column of the excel sheet.. i need to display the result in diff columns in excel sheet.. how to do that..

(2)
if i use inner join with the main table, how can i get the column names of the other table


alter procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@schm_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='bcp " select * from (select '+@columns+') as t" queryout c:\test.xls -c -t, -T -S ' + @@servername
exec master..xp_cmdshell @sql

--Generate data in the dummy file
set @sql='bcp "select * from Ven_Test..VEN_FULLREPORTMASTER where entry_date = convert(varchar, getdate()-3, 105) " queryout c:\data_file.xls -c -t, -T -S' + @@servername
-- --select @sql = 'bcp st..ven_descriptionmaster out c:\test.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @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)

-- its working
--declare @sql varchar(8000)
--select @sql = 'bcp "exec est..usp_daily_fullmachinereport" queryout c:\test.xls -c -t, -T -S' + @@servername
--exec master..xp_cmdshell @sql
--set @sql= 'exec master..xp_cmdshell ''del c:\data_file.xls'''
--exec(@sql)


--EXEC proc_generate_excel_with_columns ,'[dbo]', 'VEN_FULLREPORTMASTER','c:\test.xls'


Edited by - navbingo20 on 04/29/2011 06:55:32
Go to Top of Page

surya.saladi
Starting Member

1 Posts

Posted - 05/30/2011 :  03:13:43  Show Profile  Reply with Quote
is there any way to select columns using column number not with column name like:

SELECT col0,col1,..colN FROM [Sheet1$]

Regards,
Surya
Go to Top of Page

cbjones
Starting Member

USA
4 Posts

Posted - 08/06/2011 :  23:25:24  Show Profile  Reply with Quote
Hello Madhivanan,

I am responding to your post dated 5/18/2005, updated in 2009, regarding exporting data, including column headings, to Excel from MS SQL (2000 in my case). I am far from an expert in these matters so forgive me if my question seems elementary.

I have written a MS SQL query that may be crude, but it works. I use 7 joined tables to form the output and the columns have names that I have assigned in the query. My challenge is to have the query execute and export the data, with my column headers, to Excel.

Number 5 of your post seems to give me that script but, being new with much to learn, I don't know how to execute the stored procedure from within my query and I don't know how to show multiple tables within your script. Can you help me?

cbjones
Planning but Need Help
---------------------------
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'
Go to Top of Page

dency
Starting Member

India
2 Posts

Posted - 08/28/2011 :  04:13:01  Show Profile  Reply with Quote
quote:
Originally posted by 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'

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

dency
Starting Member

India
2 Posts

Posted - 08/28/2011 :  04:17:39  Show Profile  Reply with Quote
Hi,

Am new to this export data from excel to sql server. When am trying to exec this query,
Insert into employee Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\mydata.xls;HDR=YES',
'SELECT * FROM [mydata$]')
getting an error like SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Could you help to resolve this?

Thanks,
Dency.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 10/27/2011 :  09:21:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Under Surface Area configuration Choose True for the value AdHocDistributedQueriesEnabled

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

snow12
Yak Posting Veteran

74 Posts

Posted - 11/01/2011 :  17:35:08  Show Profile  Reply with Quote
I have several hundreds id number, query each id to get name and then add id and name to this template excel based on each id and name, so there will be hundreds excel files.

time    term    test   id   name
  1        1       0
  2        2       0
  3        3       0
  4        4       0
  5        5       0
  6        6       0
  7        7       0
  8        8       0
  9        8       0
  10       11      0
  11       11      0
  12       12      0




Do you have detail example or code to do it more fast and effciently to make hundreds of excel files?
Go to Top of Page

praneethyadav1234
Starting Member

3 Posts

Posted - 11/08/2011 :  03:05:57  Show Profile  Reply with Quote
Hi Madhivanan
iam tried Export to Excel iam not getting data in Excel file the file is showing Empty
iam using SqlServer 2005.

With Regards
Praneeth
Go to Top of Page

Namarath
Starting Member

5 Posts

Posted - 11/08/2011 :  04:55:05  Show Profile  Reply with Quote
Hi,
Am new to export data from sqlserver to export to excel.Stored procedure executed successfully but iam getting when exec the query EXEC Myproc 'master',dbo.emp',D:\testing.xls iam getting these errors when iam givin print stmt for every query
1.Password:
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Invalid Instance()).
NULL
2.The system cannot find the file specified.
NULL
3.Could Not Find D:\Projects\data_file.xls
NULL

testing.xls file empty file is showing the file size is showing 7KB
Can u give me a better solution.

Thanks
Namaratha
Go to Top of Page

Namarath
Starting Member

5 Posts

Posted - 11/08/2011 :  04:55:05  Show Profile  Reply with Quote
Hi,
Am new to export data from sqlserver to export to excel.Stored procedure executed successfully but iam getting when exec the query EXEC Myproc 'master',dbo.emp',D:\testing.xls iam getting these errors when iam givin print stmt for every query
1.Password:
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Invalid Instance()).
NULL
2.The system cannot find the file specified.
NULL
3.Could Not Find D:\Projects\data_file.xls
NULL

testing.xls file empty file is showing the file size is showing 7KB
Can u give me a better solution.

Thanks
Namaratha
Go to Top of Page

wqeniomaadiomas
Starting Member

USA
1 Posts

Posted - 11/12/2011 :  03:44:37  Show Profile  Reply with Quote
The fake leaps in an uncommon church. Americans in the UK's forum bottles the chapter. A listening symbol prevails with the sample. Does Americans in the UK's forum trifle with his undesirable?


http://www.addmy5.info

http://www.addmy5.info
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 11/14/2011 :  07:55:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by praneethyadav1234

Hi Madhivanan
iam tried Export to Excel iam not getting data in Excel file the file is showing Empty
iam using SqlServer 2005.

With Regards
Praneeth


Did you scroll down and see if there are data?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 11/14/2011 :  07:57:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Namarath

Hi,
Am new to export data from sqlserver to export to excel.Stored procedure executed successfully but iam getting when exec the query EXEC Myproc 'master',dbo.emp',D:\testing.xls iam getting these errors when iam givin print stmt for every query
1.Password:
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Invalid Instance()).
NULL
2.The system cannot find the file specified.
NULL
3.Could Not Find D:\Projects\data_file.xls
NULL

testing.xls file empty file is showing the file size is showing 7KB
Can u give me a better solution.

Thanks
Namaratha


Make sure that the file is available at Server's directory. If the file is at your local system, you need to use UNC path like \\system_name\projects\data_file.xls

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

praneethyadav1234
Starting Member

3 Posts

Posted - 11/14/2011 :  08:52:16  Show Profile  Reply with Quote
Hi Madivanan,
iam scroll down there is no data but it is showing do u want to replace the data.There is no data
Go to Top of Page

praneethyadav1234
Starting Member

3 Posts

Posted - 11/14/2011 :  08:54:04  Show Profile  Reply with Quote
Hi Madhivanan
iam tried Export to Excel iam not getting data in Excel file the file is showing Empty
iam using SqlServer 2005.

With Regards
Praneeth

Did you scroll down and see if there are data?

Madhivanan

Failing to plan is Planning to fail
---------------------------------------------------------------------------------------------------
Hi Madivanan,
iam scroll down there is no data but it is showing do u want to replace the data.There is no data
Go to Top of Page

Namarath
Starting Member

5 Posts

Posted - 11/14/2011 :  09:03:40  Show Profile  Reply with Quote
Hi,
Am new to export data from sqlserver to export to excel.Stored procedure executed successfully but iam getting when exec the query EXEC Myproc 'master',dbo.emp',D:\testing.xls iam getting these errors when iam givin print stmt for every query
1.Password:
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Invalid Instance()).
NULL
2.The system cannot find the file specified.
NULL
3.Could Not Find D:\Projects\data_file.xls
NULL

testing.xls file empty file is showing the file size is showing 7KB
Can u give me a better solution.

Thanks
Namaratha

Make sure that the file is available at Server's directory. If the file is at your local system, you need to use UNC path like \\system_name\projects\data_file.xls

Madhivanan

Failing to plan is Planning to fail
____________________________________________________________________________________________________
Hi
Thank u for Quick reply

File is available at Server's directory D:\Projects\testing.xls
D;\Projects\data_file.xls
Go to Top of Page

Namarath
Starting Member

5 Posts

Posted - 11/14/2011 :  09:04:49  Show Profile  Reply with Quote
Hi
Thank u for Quick reply

File is available at Server's directory
D:\Projects\testing.xls
D:\Projects\data_file.xls
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 11/15/2011 :  05:56:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by praneethyadav1234

Hi Madhivanan
iam tried Export to Excel iam not getting data in Excel file the file is showing Empty
iam using SqlServer 2005.

With Regards
Praneeth

Did you scroll down and see if there are data?

Madhivanan

Failing to plan is Planning to fail
---------------------------------------------------------------------------------------------------
Hi Madivanan,
iam scroll down there is no data but it is showing do u want to replace the data.There is no data


Can you create another sheet and use that sheet in the query and see what happens

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 11/15/2011 :  06:09:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Namarath

Hi
Thank u for Quick reply

File is available at Server's directory
D:\Projects\testing.xls
D:\Projects\data_file.xls



If the files are available at server's directory, you will not get any problem. Make sure that you have spelled the path correctly

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 11/15/2011 06:10:33
Go to Top of Page

Namarath
Starting Member

5 Posts

Posted - 11/15/2011 :  07:41:10  Show Profile  Reply with Quote
Hi
Thank u for Quick reply

File is available at Server's directory
D:\Projects\testing.xls
D:\Projects\data_file.xls


If the files are available at server's directory, you will not get any problem. Make sure that you have spelled the path correctly

Madhivanan

Failing to plan is Planning to fail
__________________________________________________________________________________________________
Hi,
Madhivanan iam spelled correct and the path also correct iam writing the query in database server directly but iam getting errors.why?
Errors:
1)
output
Password:
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Invalid Instance()).
NULL
2)
output
Password:
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Invalid Instance()).
NULL

3)
output
NULL
4)
output
NULL
____________________________________________________________________________________________________
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000