SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Samardeep Khera / BCP command to export data to excel with column headers / Latest Posts

RE: BCP command to export data to excel with column headers

rlowery — Tue, 27 Oct 2009 08:26:34 GMT

Thanks for the quick reply... here is my SP.USE [MES - Reporting]GO/****** Object: StoredProcedure [dbo].[MBT_RPT_Export_Excel_TEST] Script Date: 10/26/2009 17:08:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date: 10/24/09-- Description: Used to export data to Excel-- =============================================ALTER PROCEDURE [dbo].[MBT_RPT_Export_Excel_TEST] -- @filename varchar(100),-- @sql varchar(8000), -- @reportid int,-- @msgsubject varchar(1000),-- @bodytext varchar(1000),-- @reccount int OUTAS BEGIN Declare @filename varchar(100)Declare @sql varchar(8000)--**Added**Declare @sorttext varchar(500)--**Declare @reportid intDeclare @msgsubject varchar(1000)Declare @bodytext varchar(1000)Declare @reccount int DECLARE @tempSQL varchar(8000) DECLARE @columnNames varchar(8000) DECLARE @columnConvert varchar(8000) DECLARE @bcpCommand VARCHAR(8000) DECLARE @distlist NVARCHAR(MAX) DECLARE @distlist_cc NVARCHAR(MAX) DECLARE @distlist_bcc NVARCHAR(MAX)Set @filename = 'G:\SQLData\restor\RPT-DispenseDaily.xls'Set @sql = 'Select * from [Rpt - Daily Dispense Detail]'Set @reportid = 1Set @msgsubject = 'Dispense Detail: ' + convert(varchar,GetDate(),101)Set @bodytext = 'Attached please find the Dispense Detail Report'Set @reccount = 0 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 select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' + substring(@sql, charindex('from', @sql)-1, len(@sql)) EXEC(@tempSQL) -- build 2 lists -- 1. column names -- 2. columns converted to nvarchar SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name, @columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),' + column_name + CASE When data_type in ('datetime', 'smalldatetime') Then ',100' --+ 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 ',100' 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]' EXEC (@sql) -- build full BCP query SET @bcpCommand = 'bcp "SELECT * from ##TempExportData2" queryout "' SET @bcpCommand = @bcpCommand + @filename + '" -c -w -T -S"' + @@Servername + '"'-- Print @bcpCommand EXEC master..xp_cmdshell @bcpCommand Drop Table ##TempExportData Drop Table ##TempExportData2 Exec MBT_RPT_GetDistList @reportid, @distlist OUT, @distlist_cc OUT, @distlist_bcc OUT EXEC msdb.dbo.sp_send_dbmail @recipients=@distlist, @copy_recipients=@distlist_cc, @blind_copy_recipients=@distlist_bcc, @subject = @msgsubject, @file_attachments = @filename, @body = @bodytext ;END

RE: BCP command to export data to excel with column headers

SAMARDEEP — Tue, 27 Oct 2009 00:14:18 GMT

Hi,Glad to know that it helped you..If you could post your script then i'll try to look into it..Regards

RE: BCP command to export data to excel with column headers

rlowery — Mon, 26 Oct 2009 18:51:58 GMT

Thanks so much for this code. It has really helped me a great deal.I am having one problem. When I pull a small amount of data, 5 to 10 rows, the headers are appearing at the top of the spreadsheet. When I pull about 400 rows the headers appear about 50 lines down in the spreadsheet.Do you have any ideas how this could happen?Thanks!

RE: BCP command to export data to excel with column headers

dianal — Wed, 07 Oct 2009 10:23:57 GMT

Hi Samar,How do you remove the credentials from a query? I searched for an answer on the web, but found nothing. In the stored procedure properties, I added the abdc\dianal user and gave it full permissions including "take ownership", but it still failed.I will look into my settings to see why the prompt comes up, but at least the query works on the production server if I enter the paramaters into the prompt. Thanks for your help,Diana

RE: BCP command to export data to excel with column headers

SAMARDEEP — Tue, 06 Oct 2009 23:13:07 GMT

Hi Diana..Glad to know that it worked fine on your remote server..since you are running with windows authentication on your local server then try to remove the credentials from the query..and then run it..As far as prompting the parameter is concerned..it should not ask it everytime..try to see your local settings..Regards..Samar

RE: BCP command to export data to excel with column headers

dianal — Tue, 06 Oct 2009 12:21:24 GMT

Hi Samar,I was running the query on my local server and getting the error, so I tried it on my remote server and it worked perfectly.The remote production server database owner is "sa". I log into the remote server with the SA credentials.My local test server database owner is "abdc\dianal", and I use Windows Authentication to log in as abdc\dianal.The owner of the stored procedure on both servers is "dbo". Since the query worked on the remote server, should I change ownership of all the databases on my local server to "sa"? I'm not sure how the ownership was setup as "abdc\dianal" in the first place.Also, there is another question I have. Every time I execute the procedure on either server, it opens a dialog box that asks for the variable information again, so it must not be reading it from the query. The dialog box lists the parameter name, data type, output parameter (for each line it says "No"), pass null value (checkbox is always unchecked), and value. I enter the values into the dialog box and click OK, then the query continues. Is that supposed to happen?Diana

RE: BCP command to export data to excel with column headers

SAMARDEEP — Tue, 06 Oct 2009 01:28:23 GMT

Hi Diana,I cheked your script on my local environment and it worked absolutely fine.Now few things i have in my mind.Check the userid and password for your DB in the SQL query for BCP.another thing are you running this script on remote server or your local server?please check these and then let me know..RegardsSamar

RE: BCP command to export data to excel with column headers

dianal — Mon, 05 Oct 2009 09:50:25 GMT

Hi Samardeep,I tried using the debugger, but it only allows me to step into the code once before it exists the debugger. There again, maybe I'm doing something wrong with the debugger too. I tried to figure it out using the online books to no avail. It really is frustrating, so I appreciate your help.Thank you,DianaHere is my code:[code="sql"]USE [MSG]GO/****** Object: StoredProcedure [dbo].[spExportData_n] Script Date: 10/05/2009 08:39:47 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Batch submitted through debugger: SQLQuery6.sql|7|0|C:\Documents and Settings\dianal\Local Settings\Temp\~vs1ED.sqlALTER proc [dbo].[spExportData_n] ( @dbName varchar(100) = 'MSG', @sql varchar(8000) = 'select requestID,submittedDate,neededDate,projectID from dt_Requests', @fullFileName varchar(100) = 'C:\Inetpub\wwwroot\Admin\test.xls') 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) 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),' + 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]' exec (@sql) -- build full BCP query DECLARE @bcpCommand VARCHAR(8000) SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout' SET @bcpCommand = @bcpCommand + ' C:\Inetpub\wwwroot\Admin\test.xls -c -w -T -U sa -P sa","-CRAW' EXEC MSG..xp_cmdshell @bcpCommand if @@error > 0 begin select 0 as ReturnValue -- failure return end drop table ##TempExportData drop table ##TempExportData2 set @columnNames =' ' set @columnConvert =' ' set @tempSQL =' ' select 1 as ReturnValue[/code]

RE: BCP command to export data to excel with column headers

SAMARDEEP — Sun, 04 Oct 2009 23:56:49 GMT

Hi diana..You can use the variable instead of the path in the query..and thats actually intended for dynamic path only..For error..please debug and print the dynamic SQL getting prepared..and rectify the error..or if that doesnt solve the problem... send your full query..Regards,

RE: BCP command to export data to excel with column headers

dianal — Thu, 01 Oct 2009 10:42:52 GMT

Hello,I am very new to SQL 2008 Express, and am not that comfortable with stored procedures yet. However, I love your post about exporting to Excel. It will be extremely helpful. I just need some help implementing it.I copied the code and changed the lines you indicated: 1,8,9,10,57,58. But actually, line 10 was blank in the copied code, so I added it even though I don't see where the variable is used in the script below. In line 57, I entered the revised path, but couldn't that line be changed to use the @fullFileName variable? Or should @fullFileName be the path without the actual file name?The first time I executed the procedure, I received the following error, so I found instructions on Microsoft's website as to how to enable the use of xp_cmdshell, which appeared to work.[i]Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.[/i]The second time I ran the procedure, the output was as follows:[i]Warning: -w overrides -c.SQLState = S0002, NativeError = 208Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '##TempExportData2'.SQLState = 37000, NativeError = 8180Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.NULL[/i]Do you have any idea what I am doing wrong?Thank you so much for any help you're able to give.Diana

BCP command to export data to excel with column headers

SAMARDEEP — Fri, 25 Sep 2009 12:47:43 GMT

Comments posted to this topic are about the item [B]BCP command to export data to excel with column headers[/B]