My regular readers will certainly be aware that usually write on business-related matters. However, today’s article will be different. And, while different, not entirely unrelated to supply chain matters.

 

After all, there are many time 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™, for example. 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. Exposing your SQL Server to hackers or others will ill intent by leaving xp_cmdshell active is not a good policy. 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. Let us know if you do.

 

#############################################

 

Follow us on Twitter: @RKLeSolutions and @RDCushing
LIKE us on Facebook: RKL eSolutions and GeeWhiz2ROI