Solved

vb6 parse text file using split function

Posted on 2011-03-04
9
491 Views
Last Modified: 2012-05-11
I have a script that reads a text file and parses out the strings and writes them to another text file.  I have recently started getting a few bad records in the destination text file where the timestamp and closing quote is left off the date time stamp.  

In the 2 strings in allmoves.txt (source file) below, the first one goes into the destination file,  morv.imp correctly.  The time stamp and the closing quote is left off the second record.

I have attached text files with the the complete records.

allmoves.txt
"MORV00","DBG","03/03/2011","008:01:22","0","C",.....
"MORV00","DBG","03/03/2011","009:02:45","0","C",.....

morv.imp
"MORV00","DBG","03/03/2011 08:01:22",,"0","C",.....
"MORV00","DBG","03/03/2011 ,,"0","C",........
Function Main()

    	TheDir = "c:\test\"
    	NewDir = "c:\testnew"
	X = ""
    	Set fso = CreateObject("Scripting.FileSystemObject")
    
    	Set M = fso.CreateTextFile(NewDir & "inva.imp")
    	Set N = fso.CreateTextFile(NewDir & "imtr.imp")
    	Set O = fso.CreateTextFile(NewDir & "morv.imp")
    	Set P = fso.OpenTextFile(NewDir & "ALLMOVES.TXT")
 
    	sLine = P.ReadLine
	Do While sLine <> ""
        		if Left(sLine,1) = """" Then

			X = mid(sline, 2, 4)
			if X = "IMTR" then
				sCOMT = Split(sLine, ",", 24, 1)
			elseif X = "INVA" then
				sCOMT = Split(sLine, ",", 22, 1)
			elseif X = "MORV" then
				sCOMT = Split(sLine, ",", 40, 1)
			end if
			if sCOMT(4) = """9""" or sCOMT(4) = """0""" then
				sCOMT(2) = left(sCOMT(2), 11) & " " & right(sCOMT(3),9)
				sCOMT(3) = ""
				if sCOMT(0) = """MORV00""" then 
					sCOMT(39) = ""
					if sCOMT(34) = """""" then 
						sCOMT(34) ="N"
					end if
				elseif sCOMT(0) = """IMTR01""" then 
					sCOMT(23) = ""
				elseif sCOMT(0) = """INVA01""" then 
					sCOMT(21) = ""
				end if
				sLine = Join(sCOMT, ",")
	
			        	If Mid(sLine, 2, 4) = "IMTR" Then
			            		N.WriteLine (sLine)
		        		ElseIf Mid(sLine, 2, 4) = "MORV" Then
		            			O.WriteLine (sLine)
			        	ElseIf Mid(sLine, 2, 4) = "INVA" Then
					sLine = Replace(sLine, ",.,", ",.0,")
			            		M.WriteLine (sLine)
		        		End If

	        		End If
         		end if

        		if not P.AtEndOfStream then
            			sLine = P.ReadLine
        		else
             			exit do
        		end if
    	Loop

    	P.Close
    	O.Close
    	N.Close
    	M.Close


    	Main = DTSTaskExecResult_Success

End Function

Open in new window

ALLMOVES.txt
morv.txt
0
Comment
Question by:Delta7428
  • 5
  • 4
9 Comments
 
LVL 10

Expert Comment

by:borgunit
Comment Utility
I gave the program a shot and it worked. Does it work sometimes and then other time no?
0
 

Author Comment

by:Delta7428
Comment Utility
Yes it has been working.  All of a sudden, it generated a couple of bad records 3 days in a row this week.   I have searched for something out of kilter in the source record that would cause it.  Nothing that I can find.
0
 
LVL 10

Expert Comment

by:borgunit
Comment Utility
Sorry, but I need to ask. Are these the "exact files" that the error came from?
0
 
LVL 10

Expert Comment

by:borgunit
Comment Utility
If it is hard to track you might need a verification sub to handle it.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Delta7428
Comment Utility
The attachments were exact sample records from the files.  Attached are the exact complete files.  I changed the extensions from imp to txt so they would attach.

Allmoves.txt is the source file.  Inva, Morv and Imtr are the destination files.  Inva has 1 bad record on line 252.  Morv has 1 bad record on line 94.  Imtr formatted correctly.  All other records in Inva and Morv formatted correctly.

What do I need as far as a verification sub?
AllMoveS.txt
morv.txt
inva.txt
imtr.txt
0
 
LVL 10

Accepted Solution

by:
borgunit earned 500 total points
Comment Utility
The sub could check for string length or use the LIKE method.

If len(expected string) <> 9 then

or

if not expectedstring LIKE "###:##:##" then

That sort of thing
0
 

Author Comment

by:Delta7428
Comment Utility
I discovered the "bad records" are actually dups of the preceding line, minus the time stamp and closing quote.  It seems the line is being read twice for some reason.    If you see anything that jumps out at you, let me know.  I'm still troubleshooting ... off and on when I'm not pulled away on something else.  
"INVA01","BGG","03/07/2011","018:22:28","0","C","TPDLAW","NW","STAGE","O","NWIMTR","+",2,"R","01-000-0000-50401",2.96,2,1.48,"EA","N","","","","","","","","","","","","",,"","","","","","","","","","","","","","","","","","",.92,.56,.00,.00
"INVA01","BGG","03/07/2011","018:22:28","0","C","TPOSNP","NW","STAGE","O","NWIMTR","+",2,"R","01-000-0000-50401",4.42,2,2.21,"EA","N","","","","","","","","","","","","",,"","","","","","","","","","","","","","","","","","",1.57,.64,.00,.00

Function Main()

    	TheDir = "\\Mwwdc\MWW1\fs\mfgsys\" 				'"\\mww1\sys\fs\mfgsys\"
    	NewDir = "\\Mwwdc\MWW1\fs\mfgsys\MOVEFLDR\"			'"\\mww1\sys\fs\mfgsys\MOVEFLDR\"
	X = ""
    	Set fso = CreateObject("Scripting.FileSystemObject")
'    	Set A = fso.GetFile(NewDir & "fshift.imp")				'Change the file name to fshift.*** in case if you want to import data from a different file (in case if you forget to import)
'    	A.Copy NewDir & "fshift.imp"
    
    	Set M = fso.CreateTextFile(NewDir & "inva.imp")
    	Set N = fso.CreateTextFile(NewDir & "imtr.imp")
    	Set O = fso.CreateTextFile(NewDir & "morv.imp")
    	Set P = fso.OpenTextFile(NewDir & "ALLMOVES.TXT")
 
    	sLine = P.ReadLine
'    	iCount=0
	Do While sLine <> ""
        		if Left(sLine,1) = """" Then
'			iCount=iCount + 1
'			if iCount >5 then exit Do
			X = mid(sline, 2, 4)
			if X = "IMTR" then
				sCOMT = Split(sLine, ",", 24, 1)
			elseif X = "INVA" then
				sCOMT = Split(sLine, ",", 22, 1)
			elseif X = "MORV" then
				sCOMT = Split(sLine, ",", 40, 1)
			end if
			if sCOMT(4) = """9""" or sCOMT(4) = """0""" then
				sCOMT(2) = left(sCOMT(2), 11) & " " & right(sCOMT(3),9)
				sCOMT(3) = ""
				if sCOMT(0) = """MORV00""" then 
					sCOMT(39) = ""
					if sCOMT(34) = """""" then 
						sCOMT(34) ="N"
					end if
				elseif sCOMT(0) = """IMTR01""" then 
					sCOMT(23) = ""
				elseif sCOMT(0) = """INVA01""" then 
					sCOMT(21) = ""
				end if
				sLine = Join(sCOMT, ",")
	
			        	If Mid(sLine, 2, 4) = "IMTR" Then
			            		N.WriteLine (sLine)
		        		ElseIf Mid(sLine, 2, 4) = "MORV" Then
		            			O.WriteLine (sLine)
			        	ElseIf Mid(sLine, 2, 4) = "INVA" Then
					sLine = Replace(sLine, ",.,", ",.0,")
			            		M.WriteLine (sLine)
		        		End If
'			else
'				msgbox "--------" & sCOMT(4) & "--------"
'				exit do
	        		End If
         		end if

        		if not P.AtEndOfStream then
            			sLine = P.ReadLine
        		else
             			exit do
        		end if
    	Loop

    	P.Close
    	O.Close
    	N.Close
    	M.Close

'    	Set P = fso.GetFile(NewDir & "fshift.imp")
'    	P.Delete
    	Main = DTSTaskExecResult_Success

End Function

Open in new window

0
 

Author Comment

by:Delta7428
Comment Utility
The duplicate records were not being read twice as I thought but somehow they are being written twice.  The second write is minus the time stamp.  I can't figure out why it's doing that so as a work around I am checking the writeline for the presence of the next field in the position which would normally be the date stamp before writing it.  That will always be "0".  

sCrap = """" & "0" & """"
If Mid(sline, 30, 3) <> sCrap Then
      M.WriteLine (sline)
end if

If Mid(sline, 30, 3) <> sCrap Then
         O.WriteLine (sline)
End If

I am open to the real solution to the real problem if anyone has one.  Otherwise I'll live with this for now.
Function Main()
Dim iCount As Integer
        TheDir = "c:\0"
        NewDir = "c:\test"
        
        sCrap = """" & "0" & """"
        
        x = ""
        Set fso = CreateObject("Scripting.FileSystemObject")
    
        Set M = fso.CreateTextFile(NewDir & "inva.imp")
        Set N = fso.CreateTextFile(NewDir & "imtr.imp")
        Set O = fso.CreateTextFile(NewDir & "morv.imp")
        Set P = fso.OpenTextFile(NewDir & "ALLMOVES.TXT")
 
        sline = P.ReadLine
    Do While sline <> ""
        If Left(sline, 1) = """" Then
            x = Mid(sline, 2, 4)
            If x = "IMTR" Then
                sCOMT = Split(sline, ",", 24, 1)
            ElseIf x = "INVA" Then
                sCOMT = Split(sline, ",", 22, 1)
            ElseIf x = "MORV" Then
                sCOMT = Split(sline, ",", 40, 1)
            End If
            If sCOMT(4) = """9""" Or sCOMT(4) = """0""" Then
                sCOMT(2) = Left(sCOMT(2), 11) & " " & Right(sCOMT(3), 9)
                sCOMT(3) = ""
                If sCOMT(0) = """MORV00""" Then
                    sCOMT(39) = ""
                    If sCOMT(34) = """""" Then
                        sCOMT(34) = "N"
                    End If
                ElseIf sCOMT(0) = """IMTR01""" Then
                    sCOMT(23) = ""
                ElseIf sCOMT(0) = """INVA01""" Then
                    sCOMT(21) = ""
                End If
                sline = Join(sCOMT, ",")
    
                If Mid(sline, 2, 4) = "IMTR" Then
                        N.WriteLine (sline)
                ElseIf Mid(sline, 2, 4) = "MORV" Then
                        If Mid(sline, 30, 3) <> sCrap Then
                            O.WriteLine (sline)
                        End If
                ElseIf Mid(sline, 2, 4) = "INVA" Then
                        sline = Replace(sline, ",.,", ",.0,")
                        If Mid(sline, 30, 3) <> sCrap Then
                            M.WriteLine (sline)
                        End If
                End If
            End If
        End If

        If Not P.AtEndOfStream Then
                sline = P.ReadLine
        Else
                Exit Do
        End If
        
        Loop

        P.Close
        O.Close
        N.Close
        M.Close

        Main = DTSTaskExecResult_Success

End Sub

Open in new window

0
 

Author Closing Comment

by:Delta7428
Comment Utility
......
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now