Export SQL Table to Excel (Keep leading Zeros for Text Number)



alter PROCEDURE [dbo].[Export_csv] 
(@DB varchar(128)='testSample',@TABLE_NAME varchar(128), @Dir varchar(255)='c:\bcp\', @File varchar(250)='myexport.txt',@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)='' --EXEC commands


--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
 
 
declare @colsql1 nvarchar(500)
declare @colsql2 nvarchar(500)=' FROM '+@DB+'.dbo.'+@TABLE_NAME
SELECT @colsql1='SELECT '+stuff(( select ','+ 'CHAR(31)+Cast('+column_Name + ' as varchar(2000)) as ' +quotename(column_Name) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME Order by ordinal_position FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(4000)'), 1,1,'') +@colsql2
SET @CmdExc ='BCP "' +@colsql1+'" queryout "'+@Dir+'Data_'+@TABLE_NAME+'.csv" -q -c -t\, -T -S'+ @@servername
EXEC master..xp_cmdshell @CmdExc


 --BEGIN Merge Data File With Header File
 SET @CmdExc = 'TYPE '+@Dir+'Data_'+@TABLE_NAME+'.csv >> '+@Dir+@File EXEC master..xp_cmdshell @CmdExc,no_output
 --END

 --BEGIN Delete Data File
 SET @CmdExc = 'DEL /q '+@Dir+'Data_'+@TABLE_NAME+'.csv' EXEC master..xp_cmdshell @CmdExc,no_output
 --END
 END


















USE [testSample]
GO
/****** Object:  StoredProcedure [dbo].[Export_TXTFile_Piple]    Script Date: 11/15/2014 8:52:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[Export_excel] 
(@DB varchar(128)='testSample',@TABLE_NAME varchar(128), @Dir varchar(255)='c:\bcp\', @File varchar(250)='myexport.txt',@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)=''           --EXEC commands


--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 char(9)+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
 
		
declare @colsql1 nvarchar(500)
declare @colsql2 nvarchar(500)=' FROM '+@DB+'.dbo.'+@TABLE_NAME
SELECT @colsql1='SELECT '+stuff(( select ','+ 'CHAR(31)+Cast('+column_Name + ' as varchar(2000))  as ' +quotename(column_Name) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME Order by ordinal_position FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(4000)'), 1,1,'') +@colsql2
SET  @CmdExc ='BCP "' +@colsql1+'" queryout "'+@Dir+'Data_'+@TABLE_NAME+'.txt" -q -c -t\t  -T -S'+ @@servername
EXEC master..xp_cmdshell @CmdExc


        --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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s