Exporting Data to Text File with Text qualifier (double quotes) and Header


USE [testSample]
GO
/****** Object: StoredProcedure [dbo].[Export_TXTFileDoublequotes] Script Date: 11/15/2014 4:29:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Export_TXTFileDoublequotes] 
(@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 char(34)+'+stuff(( select ','+ '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\",\" -r\"\n -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

 

To import this file back to SQL Server, it is very easy to use Import/Export Wizard to use
Text Qualifier and check the first row as columns header.

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