Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Excel

Author  Topic 

jgzabala@yahoo.com
Starting Member

18 Posts

Posted - 2006-10-02 : 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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-02 : 09:31:45
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

18 Posts

Posted - 2006-10-03 : 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)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 03:49:39
Use UPDATE OPENROWSET and use Named range in the target location.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-03 : 08:40:42
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

18 Posts

Posted - 2006-10-04 : 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?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-04 : 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
Go to Top of Page

jgzabala@yahoo.com
Starting Member

18 Posts

Posted - 2006-10-04 : 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?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-04 : 10:57:03
Cant you format the sheet before exporting data to it?

Madhivanan

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

- Advertisement -