Solved

vb6 parse text file using split function

Posted on 2011-03-04
9
498 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
ID: 35040164
I gave the program a shot and it worked. Does it work sometimes and then other time no?
0
 

Author Comment

by:Delta7428
ID: 35040214
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
ID: 35040643
Sorry, but I need to ask. Are these the "exact files" that the error came from?
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 10

Expert Comment

by:borgunit
ID: 35044496
If it is hard to track you might need a verification sub to handle it.
0
 

Author Comment

by:Delta7428
ID: 35055180
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
ID: 35055569
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
ID: 35070771
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
ID: 35072215
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
ID: 35112226
......
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macro which automatically sends attachment to Outlook 14 76
MsgBox 2 59
Modifying Conditional Format from VBA code 3 59
Powerpoint 2013: Change cell reference in excel link 3 74
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

792 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