bcp Export Text File with Header

ALTER 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

---