| Author |
 Topic  |
|
|
jgzabala@yahoo.com
Starting Member
Philippines
18 Posts |
 Posted - 10/02/2006 : 03:07:54
|
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
22460 Posts |
|
|
jgzabala@yahoo.com
Starting Member
Philippines
18 Posts |
Posted - 10/03/2006 : 03:46:15
|
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) |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/03/2006 : 03:49:39
|
Use UPDATE OPENROWSET and use Named range in the target location.
Peter Larsson
Helsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 10/03/2006 : 08:40:42
|
Read that thread fully to know Named Ranges
Madhivanan
Failing to plan is Planning to fail |
|
|
|
jgzabala@yahoo.com
Starting Member
Philippines
18 Posts |
Posted - 10/04/2006 : 05:31:47
|
| Thanks, is there a way to delete records from excel starting from row 2 before I can export record from sql to excel? |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 10/04/2006 : 05:37:16
|
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 |
|
|
|
jgzabala@yahoo.com
Starting Member
Philippines
18 Posts |
Posted - 10/04/2006 : 05:45:39
|
| 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? |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 10/04/2006 : 10:57:03
|
Cant you format the sheet before exporting data to it?
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
Excel
