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:
- 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).
- 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 code 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 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 =
'C:\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:
- How fixed or repeating values can easily be included in the export while variable data from the database tables is also incorporated
- How to left-pad values with zeros, a not infrequent requirement in ACH files supplied to banking institutions
- How to set the data type to CHAR, rather than VARCHAR, for an ASCII (fixed-length) file type
- 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:
In the outfile20240730-104717.txt file, the end of the row is on the right of the letter a in the where “More static data” is located due to fixed-length.
Perhaps you might find this simple code example beneficial in building a collaborative environment across your supply chain.
