Check if text file is empty 1

[stapet]

Does anyone know how to check a text file to see if contains data. I am currently writing to a text file, but everytime I write I overwrite what is in the file all ready. Thanks

 

[JohnEfford]

How are your writing to the text file, via a DTS package?

if so this script will check if the file exists. Then perform the stage of the DTS package that writes to the file on the steps success.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
'This script returns sucsess if the export file does not exist
'and returns falure if the file exists.
'The rest of the DTS package is not run if the file exists as


Function Main()
	Dim  fso
        	Dim FileSpec
	
	'This path & file name should be the'Text File Destination' connection
	Const FileName = "Your file name"
	Const Path = "The path from the SQL server"
	
	FileSpec = Path & FileName
 	Set fso = CreateObject("Scripting.FileSystemObject")
	If Not (fso.FileExists(FileSpec)) Then
		Main = DTSTaskExecResult_Success
	Else
		Main = DTSTaskExecResult_Failure
	End if
End Function

Cheers
John Efford

 

[stapet]

I'm a newbie. Maybe I need to be more clear, sorry about that.
I am using SQL Server 2000. This is what my SP looks like:

CREATE PROCEDURE [dbo].[USP_Advances]@Name varchar(100),
@No varchar(50),
@DueDate datetime,
@PayDate datetime,
@Amount numeric (9,2), @InvoiceNo varchar(50),

DECLARE @UploadDir varchar(1000)
DECLARE @UploadFileName varchar(100)
DECLARE @UploadData varchar(4000)

SET @UploadDir = '\\MyServer\testdata\'
SET @UploadFileName = 'advances.txt'

SET @UploadData = ' echo ' + @Name + ',' + @No + ',' + CONVERT(char(8),@DueDate,1) + ',' + CONVERT(char(8),@PayDate,1) + ',' + CONVERT(varchar(15),@Amount) + ' > ' + @UploadDir + @UploadFileName

EXEC master..xp_cmdshell @UploadData



Every time I execute this SP it outputs my variables to the text file, but keeps overwriting the first record. The text file is going to be uploaded by a different program(and cleared out) at any given time. So I never know if the text file is empty or full. I need to check that before writing my variables to it. If it has records I need to append to the text file. Thanks in advance.

 

[donutman]

Try using >> instead of just one >.
-Karl

 

[JayKusch]

You could write your file to another name like 'append.txt' then execute another xp_cmdshell to merge the two files together into the original one. That way it doesn't matter if there is or is not any data in the original file. Something like ...

CREATE PROCEDURE [dbo].[USP_Advances]@Name varchar(100),
                     @No varchar(50),
                     @DueDate datetime,
                     @PayDate datetime,
                            @Amount numeric (9,2),                     @InvoiceNo varchar(50),

DECLARE @UploadDir  varchar(1000)
DECLARE @UploadData varchar(4000)

DECLARE @MergeFileName varchar(100)


SET @UploadDir = '\\MyServer\testdata\'

SET @MergeFileName = 'merge.txt'

SET @UploadData = ' echo ' + @Name + ',' + @No + ',' + CONVERT(char(8),@DueDate,1) +  ',' + CONVERT(char(8),@PayDate,1) + ',' + CONVERT(varchar(15),@Amount) + ' > ' + @UploadDir + @MergeFileName

EXEC  master..xp_cmdshell @UploadData

EXEC  master..xp_cmdshell 'COPY \\MyServer\testdata\advances.txt + \\MyServer\testdata\merge.txt  
\\MyServer\testdata\advances.txt '

Thanks

J. Kusch

 

[stapet]

Thanks donutman. That worked great. Here's a star!