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 (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:
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:
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.
Are there other SQL problems do you need assistance with?