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.
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:
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:
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').
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.