reading & writing from/to flat files within a T-sql script
-
Is there a way within an SQLServer 2000 script to read a flat file 1 record at a time, operate on the data & then write a single record to a flat file, and so on until the input flat file is exhausted ??
-
That would be more of a DTS (Data Transformation Service) than T-SQL.
I suggest you try playing with that - it can read from a text file, do edits (transact sql and/or some vbscipt) and write it to a file as so desired and can even be scheduled.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Thanks for the reply.
I recently had to write a complex COBOL program with imbedded sql commands that read in 2 input files and also had to write out 4 different output files.
I was wondering if T-sql can also do the same.
I realize that T-sql script can execute a DTS package, but that means that the script has to hold all the data in temporary tables until the end and then execute DTS packages to write out the flat files.
-
You don't have to hold all the data in temporary tables. You can create an ActiveX script with DTS where you can process one row at a time.
-
If you really must do this through T-SQL this works.
/* Working with the file system object in TSQL*/
DECLARE @fsoToken Int
DECLARE @error Int
DECLARE @tsToken Int
DECLARE @fToken Int
DECLARE @fileContents VarChar(2000)
DECLARE @src VarChar(500)
DECLARE @desc VarChar(500)
--first create the file manipulation object
EXEC @error = sp_oaCreate 'scripting.filesystemobject', @fsoToken OUT
-- a non 0 result in @error indicates failure
IF @error <>0
BEGIN
EXEC sp_oaGetErrorInfo @fsoToken, @src OUT, @desc OUT
PRINT 'Error creating fileSystemObject token'
SELECT Error=Convert(VarBinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
--next create the object to read the file
EXEC @error = sp_oaMethod @fsoToken, 'OpenTextFile', @tsToken OUT, 'fully qualified file name'
-- a non 0 result in @error indicates failure
IF @error <>0
BEGIN
EXEC sp_oaGetErrorInfo @tsToken, @src OUT, @desc OUT
PRINT 'error creating ts token'
SELECT Error=Convert(VarBinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
--next read the file Into a local variable
EXEC @error = sp_oaMethod @tsToken, 'readline', @fileContents OUT
EXEC @error = sp_oaMethod @tsToken, 'readline', @fileContents OUT
--SELECT @iPos = SELECT PATINDEX(',', @fileContents)
WHILE @error = 0
BEGIN
EXEC @error = sp_oaMethod @tsToken, 'readline', @fileContents OUT
-- PRINT @fileContents -- for debugging
IF @error <> 0 and @error <> 0x800A003E
BEGIN
EXEC sp_oaGetErrorInfo @tsToken, @src OUT, @desc OUT
PRINT 'error reading ts token file'
SELECT Error=Convert(VarBinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
END
--next close the text stream
EXEC @error = sp_oaMethod @tsToken, 'close'
-- a non 0 result in @error indicates failure
IF @error <> 0
BEGIN
EXEC sp_oaGetErrorInfo @tsToken, @src OUT, @desc OUT
PRINT 'error closing ts token file'
SELECT Error=Convert(VarBinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
--destroy the objects to release resources
EXEC @error = sp_oaDestroy @tsToken
-- a non 0 result in @error indicates failure
IF @error <>0
BEGIN
PRINT 'Destroy ts token'
EXEC sp_oaGetErrorInfo @tsToken
RETURN
END
EXEC @error = sp_oaDestroy @fsoToken
-- a non 0 result in @error indicates failure
IF @error <>0
BEGIN
PRINT 'Destroy fso token'
EXEC sp_oaGetErrorInfo @fsoToken
RETURN
END
-
Thanks for the script.
I have to study this further.
In discussing this further with a colleague, we came up with a solution for appending single records to an output file. Use the dos "ECHO" command to send a string of data to an output file.
example -
declare @var1 char(100)
declare @query varchar(400)
set @var1 = 'mary had a little lamb'
set @query = 'echo ' + @var1 + '>> e:\mf_work\mikef_19.txt'
set @query = 'master.dbo.xp_cmdshell ' + '''' + @query + ''''
print @query
exec (@query)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply