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
 New to SQL Server Programming
 Excel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jgzabala@yahoo.com
Starting Member

Philippines
18 Posts

Posted - 10/02/2006 :  03:07:54  Show Profile  Reply with Quote
Dear All,

Can you please help me regarding exporting of record from sql using stored procedure but the cell (in excel) should be equal to text. Is there a way?

by the way here is the stored procedure

CREATE proc spExportData
(
@dbName varchar(100) = 'crewing',
@sql varchar(5000) = '',
@fullFileName varchar(150) = ''
)
as
if @sql = '' or @fullFileName = ''
begin
select 0 as ReturnValue -- failure
return
end
-- if DB isn't passed in set it to master
select @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
drop table ##TempExportData
if object_id('##TempExportData2') is not null
drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select @tempSQL = left(@sql, charindex(' from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex(' from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
print @tempSQL
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),' --'convert(text,'
+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
when data_type in ('datetime', 'smalldatetime') then ',120'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'
-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
print @sql
exec (@sql)
-- build full BCP query
select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'
-- execute BCP
Exec master..xp_cmdshell @sql
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
select 1 as ReturnValue -- success
GO


thanks


Edited by - jgzabala@yahoo.com on 10/02/2006 03:58:51

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/02/2006 :  09:31:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See if this helps
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

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

jgzabala@yahoo.com
Starting Member

Philippines
18 Posts

Posted - 10/03/2006 :  03:46:15  Show Profile  Reply with Quote
thanks for the answer but i'm encountering problem, and is there a way to empty the excel file that will be populated once stored procedure is run? cause I need to do this every day.

"Error : Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

I used this Stored procedure
CREATE PROCEDURE ExportToExcelSheet(@fileName varchar(255),@sheetName varchar(255))
as
Declare @sql varchar(8000)
Set @sql ='
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@fileName+';HDR=YES'',
''SELECT* FROM ['+@sheetName+'$]'')
SELECT * from TableName'
Exec (@sql)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/03/2006 :  03:49:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use UPDATE OPENROWSET and use Named range in the target location.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/03/2006 :  08:40:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Read that thread fully to know Named Ranges

Madhivanan

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

jgzabala@yahoo.com
Starting Member

Philippines
18 Posts

Posted - 10/04/2006 :  05:31:47  Show Profile  Reply with Quote
Thanks, is there a way to delete records from excel starting from row 2 before I can export record from sql to excel?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 10/04/2006 :  05:37:16  Show Profile  Visit spirit1's Homepage  Reply with Quote
don't do that.
export everything into the sql server and delete there.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jgzabala@yahoo.com
Starting Member

Philippines
18 Posts

Posted - 10/04/2006 :  05:45:39  Show Profile  Reply with Quote
thanks, actually I already used ur script name spExportData I also add some code to generate filename with date but the problem is when I open the file in excel ,cell type is not equal to text, is there a way to automate that?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/04/2006 :  10:57:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Cant you format the sheet before exporting data to it?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.09 seconds. Powered By: Snitz Forums 2000