Reading a text file

[fattyfatpants]

I have recently created an application that reads data from a tab delimited text file and puts it into a non-delimited (is that even a word??) text file for uploading to a mainframe. I got the code to work and it works perfectly, however I know there has to be a better way to do this. Any suggestions are welcome. I apologize for how long this is.
Development environment: IBM T20 Laptop, Win XP Pro, VB6 SP6, SQL Server 2000

Sample Input Data:
Check# <tab char> Date <tab char> Amount
45678 9/3/2004 200.5
45679 9/15/2004 21.14
45890 9/16/2004 100

Sample Output Data: (you'll see how it is formatted in the code)
01XXXXXXXXXXXX000004567800000002005009032004
01XXXXXXXXXXXX000004567900000000211409152004
01XXXXXXXXXXXX000004589000000001000009162004
.
.
.
02XXXXXXXXXXXX0000000003000000032164

Option Explicit

Private Sub Form_Load()
    Dim fso As New FileSystemObject
    Dim inFile As TextStream 'used to hold the input file
    Dim outFile As TextStream ' used to hold the output file
    Dim intDetailCount As Integer 'used to hold the number of detail records read
    Dim intFirstTab As Integer 'used to hold the first tab character
    Dim curGrandTotal As Currency 'used to hold the currency formatted grand total
    Dim strGrandTotal As String 'used to hold the string formatted grand total
    Dim strAmount As String 'used to hold the current rows check amount
    Dim strAcctNumber As String 'used to hold the account number
    Dim strCheckNumber As String 'used to hold the current lines check number
    Dim strIssueDate As String 'used to hold the current lines check issue date
    Dim strCurrentLine As String 'used to hold the current line
    Dim strDetailRec As String 'used to hold the number designating a detail record
    Dim strGroupRec As String 'used to hold the number designating a group record
    
    strAcctNumber = "123456789012"
                    
    'each detail record output to outFile will be preceded by strDetailRec
    strDetailRec = "01"
    
    'each group record output to outFile will be preceded by strGroupRec
    strGroupRec = "02"
    
    'open the input file
    Set inFile = fso.OpenTextFile("C:\Temp\Checks.txt")
    
    'create the output file
    Set outFile = fso.CreateTextFile("C:\Temp\OutChecks.txt", True)
    
    'loop until the file is completely read
    Do Until inFile.AtEndOfStream
    
        'hold the current line being read for use in the loop
        strCurrentLine = inFile.ReadLine
        
        'increment the total number of detail records read
        intDetailCount = intDetailCount + 1
        
        'the first five digits being read from the input file are always the
        'check number
        strCheckNumber = Left(strCurrentLine, 5)
        
        'setting the variable equal to the spot where the second tab character is...
        'starting out at the 7th character because the first 5 are the check number
        'and the next one is a tab character
        intFirstTab = InStr(7, strCurrentLine, vbTab)
        
        'the issue date is immediately following the first tab character, however
        'it is in the format m/d/yyyy so if the date being read is 8/3/2004 the
        'length will be different than a date being read in 8/27/2004...
        'i start at the 7th position of the current line because it is the one
        'immediately following the first tab
        strIssueDate = Format(Mid(strCurrentLine, 7, intFirstTab - 7), "mm/dd/yyyy")
        
        'i now want to strip the '/' out of the string to make it into the required
        'format of mmddyyyy
        strIssueDate = Replace(CStr(strIssueDate), "/", "")
        
        'setting the variable equal to the current lines check amount
        strAmount = Mid(strCurrentLine, intFirstTab + 1, Len(strCurrentLine))
        
        'because the check amount can be placed in the input file in any of these
        'formats: 100 , 100.1 , 100.15 designating a whole dollar amount,
        'a dollar and a tenths amount, or a complete dollar and cent amount respectively
        'the format needs to be built in the 100.15 style...
    
        'test to see if the amount has a decimal place, if it doesn't
        If InStr(strAmount, ".") = 0 Then
        
            'add a decimal and two zeros
            strAmount = strAmount & ".00"
            
        'else if it does have a decimal but only has a tenths position we need to
        'add one zero at the end
        ElseIf Len(strAmount) - InStr(strAmount, ".") <> 2 Then
            strAmount = strAmount & "0"
            
        'else it has the decimal and hundreths position
        Else
            strAmount = strAmount
        End If
            
        'add this amount to the grand total
        curGrandTotal = curGrandTotal + CCur(strAmount)
        
        'strip the decimal out of the amount to get it into the correct format of
        '10015 where there originally would have been the decimal between the
        'zero and the one
        strAmount = Replace(strAmount, ".", "")
        
        'write the formatted line
        '01XXXXXXXXXX00000000000000000000000000000000
        outFile.WriteLine strDetailRec & strAcctNumber & Format(strCheckNumber, "0000000000") & Format(strAmount, "000000000000") & Format(strIssueDate, "00000000")
    Loop
    
    'strip the decimal out of the grand total to get it into the correct format of
    '10015
    strGrandTotal = Replace(curGrandTotal, ".", "")
    
    'write the formatted group line
    '02XXXXXXXXXX0000000000000000000000
    outFile.WriteLine strGroupRec & strAcctNumber & Format(intDetailCount, "0000000000") & Format(strGrandTotal, "000000000000")
    
    'close the file
    outFile.Close
    
    'clean up
    Set fso = Nothing
    Set inFile = Nothing
    Set outFile = Nothing
End Sub

 

[fredericofonseca]

I can hardly think of a better way to do it, apart from doing it on the mainframe side, which depending on the mainframe and on volume of records may be faster.

Clean code.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[VBrit]

A much faster way to do this would be creating a DTS package using SQL Server where the transformation occurs between two text file data sources.

As far as your code reads, there is not many (if any) checks for data type and I would recommend an oo approach using classes to represent fields and tables to implement data type, validation etc. and produce structured error reports. This would also allow you to repeat this procedure with other file type transformations.

VBrit