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 procedureCREATE proc spExportData ( @dbName varchar(100) = 'crewing', @sql varchar(5000) = '', @fullFileName varchar(150) = '')asif @sql = '' or @fullFileName = ''begin select 0 as ReturnValue -- failure returnend -- if DB isn't passed in set it to masterselect @dbName = 'use ' + @dbName + ';'if object_id('##TempExportData') is not null drop table ##TempExportDataif object_id('##TempExportData2') is not null drop table ##TempExportData2-- insert data into a global temp tabledeclare @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 @tempSQLif @@error > 0begin select 0 as ReturnValue -- failure returnend -- build 2 lists-- 1. column names-- 2. columns converted to nvarcharSELECT @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_nameFROM tempdb.INFORMATION_SCHEMA.ColumnsWHERE table_name = '##TempExportData'-- execute select query to insert data and column names into new temp tableSELECT @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 @sqlexec (@sql)-- build full BCP queryselect @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'-- execute BCPExec master..xp_cmdshell @sqlif @@error > 0begin select 0 as ReturnValue -- failure returnenddrop table ##TempExportDatadrop table ##TempExportData2select 1 as ReturnValue -- successGOthanks |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
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 procedureCREATE PROCEDURE ExportToExcelSheet(@fileName varchar(255),@sheetName varchar(255))asDeclare @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
30421 Posts |
Posted - 2006-10-03 : 03:49:39
|
Use UPDATE OPENROWSET and use Named range in the target location.Peter LarssonHelsingborg, Sweden |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-03 : 08:40:42
|
Read that thread fully to know Named RangesMadhivananFailing to plan is Planning to fail |
|
|
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? |
|
|
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 |
|
|
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? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-04 : 10:57:03
|
Cant you format the sheet before exporting data to it?MadhivananFailing to plan is Planning to fail |
|
|
|