Read file look for text and check for a blank line

[sauce1979]

I am currently trying to import a text file into sql server. I am using SQL server 2000 DTS to read in the file. I then want to add an active X transformation task to split the file into multiple files which will then be uploaded into separate sql tables. I am using 2 active x tasks, one tocheck if the file exists and the other to split the file. The script I use to check for the file is as follows:

Option Explicit

Function Main()
Dim oFSO, sFileName
 ' Get the name of the file from the Connection "Text File (Source)"
sFilename = DTSGlobalVariables.Parent.Connections("Text File (Source)").DataSource
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Check for file and return appropriate result
 If oFSO.FileExists(sFilename) Then
      Main = DTSTaskExecResult_Success
   Else
      MsgBox "The " & sFilename & " does not exists"
      Main = DTSTaskExecResult_Failure

 End If

        Set oFSO = Nothing
End Function

I would like to amend this script to first check for the file. Once it has found the file read it and look for a line containing "[ownership]". Once it find this it should check if the next line is blank. If not add a blank line and complete the script. If the next line is a blank then complete the script. Any ideas?

 

[mrmovie]

have a look at the oFSO.OpenTextFile method, then the .ReadLine method, this might put start you in the right direction?

Set objTS = oFSO.OpenTextFile(strFile, 1, True) 'appending? no that wont work
blnFound = False
Do While Not objTS.AtEndOfStream
strLine = ""
strLine = objTS.ReadLine
If InStr(strLine, "[ownership]") Then 'case?
blnFound = True
End If
If blnFound = True Then
If strLine = "" Then
'oh good we dont need to do anything
Wscript.Echo "blank line is there after the []"
Else
'hmm we need to add a line, hmm we should have read the file into memory, added a line then written it back out again...
Wscript.Echo "blank line is NOT there after the []"
End If
blnFound = False
'should be bug out of the loop or are there more than one instance/s of [ownership]?
'Exit Do?
End If
Loop
objTS.Close
Set objTS = Nothing

 

[sauce1979]

Many thanks. I think I should be able to take it from here. Much appreciated.

 

[sauce1979]

I have tried to use the blueprint given above to add the blank Line but my amendment now blanks the file I want to write to. The code is :

' Pkg 211 (File Exists - 1)
' Option Explicit

Function Main()
        Dim oFSO, sFileName,objTS,strLine,NextstrLine,ts
         Const ForReading = 1
         Const ForWriting = 2
        

        ' Get the name of the file from the Connection "Text File (Source)"
        sFilename = DTSGlobalVariables.Parent.Connections("Text File (Source)").DataSource

        Set oFSO = CreateObject("Scripting.FileSystemObject")

        ' Check for file and return appropriate result
        If oFSO.FileExists(sFilename) Then
	
	Set objTS = oFSO.OpenTextFile( sFilename, ForReading)
	       blnFound = False
	       strLine = ""
	     Do While Not objTS.AtEndOfStream
                    
   	        strLine = objTS.ReadLine
  		 If InStr(strLine, "[ownership]") Then 
		           NextstrLine = objTS.ReadLine
				If Len(NextstrLine) = 0 Then	
					'BlankLine Found
					  blnFound = True
				End If
		End If

      		If blnFound = True Then
		
			MsgBox "blank line is there after the [ownership]"
		
		Else
			strLine = strLine  & vbNewline
   		End If
	      Loop

	objTS. Close
	Set objTS = Nothing

	set ts = oFSO.OpenTextFile( sFilename, ForWriting)
	 ts.Write(strLine)
	ts.Close
	
               Main = DTSTaskExecResult_Success
        Else

		MsgBox "The " & sFilename & " does not exists"
		Main = DTSTaskExecResult_Failure

        End If

        Set oFSO = Nothing
End Function

I imagine it is something simple but my mind has gone stale. Any help would be much appreciated

 

[tsuji]

[tt]Function Main()
Dim oFSO, sFileName,objTS,strLine,NextstrLine,ts[red],strText[/red]
Const ForReading = 1
Const ForWriting = 2


' Get the name of the file from the Connection "Text File (Source)"
sFilename = DTSGlobalVariables.Parent.Connections("Text File (Source)").DataSource

Set oFSO = CreateObject("Scripting.FileSystemObject")

' Check for file and return appropriate result
If oFSO.FileExists(sFilename) Then

Set objTS = oFSO.OpenTextFile( sFilename, ForReading)
blnFound = False
strLine = ""
[red]NextstrLine = ""[/red]
[red]strText = ""[/red]
Do While Not objTS.AtEndOfStream

strLine = objTS.ReadLine
If InStr(strLine, "[ownership]") Then
NextstrLine = objTS.ReadLine
If Len(NextstrLine) = 0 Then
'BlankLine Found
blnFound = True
End If
End If

If blnFound = True Then 'found [ownership] and empty next line

MsgBox "blank line is there after the [ownership]"
[red]strLine = strLine & vbNewLine & NextstrLine[/red] 'NextstrLine would be empty here

[red]ElseIf NextstrLine <> "" Then 'found [ownership] and nonempty next line
strLine = strLine & vbNewline & vbNewLine & NextstrLine[/red]
Else 'not found [ownership]
[red]'strLine = strLine[/red] 'or do nothing about
End If
[red]If strText <> "" Then
strText = strText & vbNewLine & strLine
Else
strText = strLine
End If[/red]

Loop

objTS. Close
Set objTS = Nothing

set ts = oFSO.OpenTextFile( sFilename, ForWriting)
ts.Write(str[red]Text[/red])
ts.Close

Main = DTSTaskExecResult_Success
Else

MsgBox "The " & sFilename & " does not exists"
Main = DTSTaskExecResult_Failure

End If

Set oFSO = Nothing
End Function
[/tt]
'ps: slightly awkward in the conditional because blnFound is not completely specifying the possible outcome. If you add a boolean on next line situation that would be more symmetric in the look

 

[sauce1979]

THANKS A LOT. THATS SEEMS TO HAVE DONE THE TRICK. MUCH APPRECIATED.

 

[tsuji]

[0] That would deliver reasonably good result in most cases. But, there are fundamental flaws, re-reading about it, apart from some minor resetting position error here and there. These are the fundamental flaws...
[0.1] What if it is at the last line where you find the [ownership] line? Next line concept would be an error.
[0.2] What if you have consecutive [ownership] lines? In that case, you've to test [ownership] particle in the next line as well to be sure.
[0.3] You would lost the beginning successive blank lines if there are, because strText would be empty as well...

[1] In view of the fundamental flaws, I think it is necessary to re-write it in a grand scale. In fact, a rewrite would simplify the script, eliminate the clumsiness, and make it much more robust. Here is the re-write of it.
[tt]
Function Main()
Dim oFSO, sFileName,objTS,strLine,strText,blnFound,blnFirst
Const ForReading = 1
Const ForWriting = 2
' Get the name of the file from the Connection "Text File (Source)"
sFilename = DTSGlobalVariables.Parent.Connections("Text File (Source)").DataSource
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Check for file and return appropriate result
If oFSO.FileExists(sFilename) Then
Set objTS = oFSO.OpenTextFile( sFilename, ForReading)
blnFirst=true
blnFound = False
strText = ""
Do While Not objTS.AtEndOfStream
strLine = objTS.ReadLine
if blnFound and strLine<>"" then 'if whitespaces only is taken as empty line, use trim(strLine), otherwise not.
strLine=vbNewLine & strLine
end if
If blnFirst Then
strText=strLine
blnFirst=False
Else
strText = strText & vbNewLine & strLine
End If
blnFound=cbool(InStr(strLine, "[ownership]")<>0)
if blnFound and objTS.AtEndOfStream then
strText=strText & vbNewLine
end if
Loop
objTS. Close
Set objTS = Nothing
set objTS = oFSO.OpenTextFile( sFilename, ForWriting)
set objTS = oFSO.OpenTextFile( sFilename, ForWriting, true)
objTS.Write strText
objTS.Close
set objTS=nothing
Main = DTSTaskExecResult_Success
Else
MsgBox "The " & sFilename & " does not exists"
Main = DTSTaskExecResult_Failure
End If
Set oFSO = Nothing
End Function
[/tt]

 

[sauce1979]

I understand what you are saying. I just forgot to mention that the text file is an Essbase hierarchy meta data extraction. Hence i will always know the format of the extraction. In this case the extraction that I make will only have one occurrence of [ownership] as a line on its own. Also I know it is the third block of data in the text file. There are 23 in all. The issue was that the structure is as follows:

data.....

data.....

in the case of the ownership heading there isn't a blank line between it and the data. So i needed to put a new line under the ownership heading which would allow me to uniform the format and split the text file into multiple text files based on headings using an array.

Nonetheless you have made some very valid points and I will take them on board.

Much appreciated.​

 

[tsuji]

[1.1] amendment: I had accidentally put two consecutive set objTS lines (one with create if not exists). Just comment out the second would be fine.

[2] I know well sometimes we have additional knowledge of the special format of the text file. As long as the constraint is forumulate on generic terms, the script should exactly reflect that, no less.

 

[sauce1979]

will do. thanks again