Write Text File Direct from Transact-SQL

There are many times that exchanging data with other systems in the supply chain might be done via text-based files (such as ASCII files, CSV files, and more). Sometimes it is to make the pertinent data available to external systems—like Microsoft® Excel™. Other times, it may be for some form of EDI (electronic data interchange) with a customer or vendor.

So, in this article, I am going to show you a simple way to write-out data to text file using only components available to you in SQL Server's Transact-SQL (T-SQL) environment.

A stored procedure to write-out the data

First, we will need a stored procedure that we can call to write-out to the designated file the data that we will gather and pass to it. Here is the T-SQL code for such a procedure:

IF
EXISTS
(SELECT *
            FROM
SysObjects
            WHERE Name =
'spWriteToFile_RKL'
                and
Type
=
'P')
    BEGIN
        DROP
PROC dbo.spWriteToFile_RKL
    END
GO

/******************************************************************************
    CREATE PROCEDURE spWriteToFile_RKL
*******************************************************************************
With this Stored procedure you can write directly from SQL to a text file.
*******************************************************************************
    PARAMETERS
*******************************************************************************
@Text        VARCHAR(8000)    What you want written to the output file.
@File        VARCHAR(255)    Path and file name to which you wish to write.
@Overwrite    BIT = 0            Overwrite flag (0 = Append / 1 = Overwrite)

Returns: NULL
*******************************************************************************
    USAGE and EXAMPLE(S)
*******************************************************************************
May be used to write out error logs.
exec dbo.spWriteToFile_RKL
     'This is the text I want to write to the file. I can have up to 8000 characters in this string.'
    , 'D:\DataOut\spWriteToFile_RKL.txt'
    , 0
*******************************************************************************
Adapted from original code by Anees Ahmed. Please see
http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.695/lngWId.5/qx/vb/scripts/ShowCode.htm
for potential copyright details.
*******************************************************************************
(c)2008, 2015                RKL eSolutions, LLC                    RDCushing
*******************************************************************************/
create
proc dbo.spWriteToFile_RKL
        @text        varchar(8000),
        @file        varchar(100),
        @overwrite    bit
= 0
as
begin
-- Activate xp_cmdshell
    exec
sp_configure
'show advanced options', 1;
    reconfigure;
    exec
sp_configure
'xp_cmdshell', 1;
    reconfigure;
    set
nocount
on
    declare @query        varchar(255)
    --Debugging or "echo" code
    set @query =
'ECHO '
+
coalesce(ltrim(@text),'-')
            +
case
                when (@overwrite = 1) then
' > '
                else
' >> '
             end
            +
rtrim(@file)
    exec
master..xp_cmdshell
@query>
    -- Debugging only
    --print @query
    set
nocount
off
    -- Deactivate xp_cmdshell
    exec
sp_configure
'xp_cmdshell', 0;
    reconfigure;
end
go
grant
exec
on dbo.spWriteToFile_RKL to
public
go

There are some things worth noting here:

1. The procedure does not gather any of the text to be written to the file. Its sole purpose is to take the text gathered elsewhere and passed to it, and write it to the designated file (including the path).
2. The procedure leverages an extended stored procedure named xp_cmdshell. You expose your SQL Server to hackers or others with ill intent by leaving xp_cmdshell active. Therefore, you will note that the procedure activates xp_cmdshell for its own use and then immediately deactivates it again, once it has completed its writing out of the data.

Some T-SQL scripting to gather the data to be exported

For our example, we have prepared a simple script that 1) writes a header line into the file, and 2) writes a number of rows of data based on (in this case) vendor payments. Here is the T-SQL script:

/******************************************************************************
    EXAMPLE of METHOD for BUILDING OUTPUT FILE using spWriteToFile_RKL
*******************************************************************************/
-- Declare Visible Parameters -------------------------------------------------
declare @iBatchKey            int
declare @iFile                varchar(255)    -- Path and file name to which you wish to write.
declare @iOverwrite            bit            -- Overwrite flag (0 = Append / 1 = Overwrite)
declare @iVariable1            char(30)        -- Some fixed value needed in outfile
declare @iVariable2            char(25)        -- Some fixed value needed in outfile
-- Set Parameter Values -------------------------------------------------------
set @iBatchKey        = 989
/******************************************************************************
    Here we set the PATH and FILENAME for the OutFile to include the
    date and time the file was produced
*******************************************************************************/
set @iFile        =
'D:\DataOut\Outfile'
                    +
left(replace(replace(replace(convert(varchar(30),getdate(),126),':',''),'-',''),'T','-'),15)

                    +
'.txt'
set @iOverwrite    = 0    -- Append, not overwrite
set @iVariable1    =
'This is some static data'
set @iVariable2    =
'More static data'
-- Declare Hidden Parameters --------------------------------------------------
declare @Text                varchar(8000)
-- Create and insert a header row into the file -------------------------------
set @Text =
'This is a NEW header row for the file'
    +
';'
+
convert(char(10),getdate(),126)
-- Write the line to the output file
exec dbo.spWriteToFile_RKL
     @Text
    , @iFile
    , @iOverwrite
-- DECLARE Loop Variables -----------------------------------------------------
declare @CurrVendPmtKey        int
-- INITIALIZE Loop Variables --------------------------------------------------
select @CurrVendPmtKey =
min(VendPmtKey)
    from dbo.tapVendPmt
    where BatchKey = @iBatchKey
-- LOOP PROCESSING ------------------------------------------------------------
while @CurrVendPmtKey is
not
null
begin    -- @CurrVendPmtKey Loop
    set
nocount
on
    select @Text =
         right('000000000000000000000' +
rtrim(vp.TranNo),20)
        +
';'
+
right('0000000000' +
cast(cast(vp.TranAmtHC as
dec(15,2)) as
varchar),10)
        +
';'
+
cast(v.VendName as
char(30))
        +
';'
+ @iVariable1
        +
';'
+ @iVariable2
    from dbo.tapVendPmt vp
    join dbo.tapVendor v
        on vp.VendKey = v.VendKey
    where vp.VendPmtKey = @CurrVendPmtKey
    -- Write the line to the output file
    exec dbo.spWriteToFile_RKL
         @Text
        , @iFile
        , @iOverwrite
    -- INCREMENT Loop Variables
    select @CurrVendPmtKey =
min(VendPmtKey)
    from dbo.tapVendPmt
    where BatchKey = @iBatchKey
        and VendPmtKey > @CurrVendPmtKey
end        -- @CurrVendPmtKey Loop

Note that in populating the @iFileName variable, we included code to supply the full path and to append a date-time stamp to the filename. We also provided examples of:

1. How fixed or repeating values can easily be included in the export while variable data from the database tables is also incorporated
2. How to left-pad values with zeros, a not infrequent requirements in ACH files supplied to banking institutions
3. How to set the data type to CHAR, rather than VARCHAR, for an ASCII (fixed-length) file type
4. How to create a loop structure without using a SQL CURSOR.

Note that, if a CSV is the desired outcome, replace the semicolons in building the @Text string with commas, and change the file extension (in the @iFile code, to '.csv' in lieu of '.txt').

Here's what the resulting file looks like

So, when this is executed (in our demo database), here is what the resulting file contains:

This is a NEW header row for the file;2015-07-27
00000000000000000248;0001284.41;Pacific Bell ;This is some data ;More data
00000000000000000246;0003202.33;Intuitive InterLan ;This is some data ;More data
00000000000000000249;0000344.14;Smart Office Solutions ;This is some data ;More data
00000000000000000250;0000626.05;Smart Office Solutions ;This is some data ;More data
00000000000000000251;0000669.18;Smart Office Solutions ;This is some data ;More data
00000000000000000252;0000208.68;Smart Office Solutions ;This is some data ;More data
00000000000000000244;0001878.91;Clark Paper Supplies ;This is some data ;More data
00000000000000000240;0001197.70;Atlantic Trade Shows ;This is some data ;More data
00000000000000000241;0003000.00;Corporate Executive Office Man;This is some data ;More data
00000000000000000242;0001725.50;Corporate Executive Office Man;This is some data ;More data
00000000000000000243;0001690.99;Corporate Executive Office Man;This is some data ;More data
00000000000000000245;0000948.51;InFocus Rentals ;This is some data ;More data
00000000000000000247;0007429.42;Mary Jones ;This is some data ;More data
00000000000000000253;0000525.84;Top Hat Productions ;This is some data ;More dataß End of row is here, due to fixed-length

Perhaps in building a collaborative environment across your supply chain, you might find this simple code example beneficial.

How's your SQL?

Are there other SQL problems do you need assistance with?