bcp Export Text File with Header
Posted: November 15, 2014 Filed under: Uncategorized Leave a commentALTER PROCEDURE [dbo].[Export_TXTFile] (@DB varchar(128)='testSample',@TABLE_NAME varchar(128), @Dir varchar(255)='c:\bcp\', @File varchar(250)='myexport',@IncludeHeader bit=1) AS Set nocount on; --http://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an DECLARE @CmdExc varchar(8000)='' --BEGIN Ensure Dir variable has a backslash as the final character IF NOT RIGHT(@Dir,1) = '\' BEGIN SET @Dir=@Dir+'\' END --END IF @IncludeHeader=0 BEGIN --BEGIN Create Data file SET @CmdExc ='BCP "'+@DB+'.dbo.'+@TABLE_NAME+'" out "'+@Dir+@File +'" -c -t^| -T -S'+ @@servername EXEC master..xp_cmdshell @CmdExc --END END else BEGIN declare @colsql nvarchar(500)= 'SELECT stuff(( select ''| ''+quotename(column_Name,char(34)) as col FROM '+@DB+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@TABLE_NAME+''' FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(4000)''), 1,1,'''')' SET @CmdExc ='BCP "' +@colsql+'" queryout '+@Dir+@File+ ' -q -c -T -S'+ @@servername EXEC master..xp_cmdshell @CmdExc,no_output --BEGIN Create Data file SET @CmdExc ='BCP "'+@DB+'.dbo.'+@TABLE_NAME+'" out "'+@Dir+'Data_'+@TABLE_NAME+'.txt" -c -t^| -T -S'+ @@servername EXEC master..xp_cmdshell @CmdExc --END --BEGIN Merge Data File With Header File SET @CmdExc = 'TYPE '+@Dir+'Data_'+@TABLE_NAME+'.txt >> '+@Dir+@File EXEC master..xp_cmdshell @CmdExc,no_output --END --BEGIN Delete Data File SET @CmdExc = 'DEL /q '+@Dir+'Data_'+@TABLE_NAME+'.txt' EXEC master..xp_cmdshell @CmdExc,no_output --END END
Advertisements