Compare Excel Data and make Changes

[inov8iv]

I have this excel file and a text file with 1 field matching, I want to be able to pull up with this file field [7], then run through the entire text file searching for a match, there may or may not be a match, if there is a match, they will add field [7] of the first file and field [3] of the second(txt) file.

It reads the data file for the excel and the text file it reads the first line and just loops through the first line to no end. I thought there was a For $i = 1 to EOF function, but I could not find one.

For $r = 1 To $avExcel[0][0]
    $OwnName = $avExcel[$r][1]      ; Owner Name in Column 1 (A)
    $OwnAdd1 = $avExcel[$r][2]      ; Owner Address 1 in Column 2 (B)
    $OwnAdd2 = $avExcel[$r][3]      ; Owner Address 2 in Column 3 (C)
    $OwnCity = $avExcel[$r][4]      ; Owner City in Column 4 (D)
    $OwnState = $avExcel[$r][5]     ; Owner State in Column 5 (E)
    $OwnZIP = $avExcel[$r][6]       ; Owner ZIP in Column 6 (F)
    $OwnTaxID = $avExcel[$r][7]     ; Owner Tax ID in Column 7 (G)
    $OwnRent = $avExcel[$r][8]      ; Owner Rent Earned Amount in Column 8 (H)

    $OwnName = StringReplace($OwnName, "&", "&")
    MsgBox(0, "PWRead Data", $OwnName & "°" & $OwnAdd1 & "°" & $OwnAdd2 & "°" & $OwnCity & "°" & $OwnState & "°" & $OwnZIP & "°" & $OwnTaxID & "°" & $OwnRent)
    
    
    While 1
        $TPRead = FileReadLine($TPFile)
        MsgBox(0, "TPRead Data", "TPRead Data = " & $TPRead)
        $TPReadS = StringSplit($TPRead, "°")
        $TPName = $TPReadS[1]
        $TPTaxID = $TPReadS[2] 
        $TPRent = $TPReadS[3]
        
        If $OwnTaxID = $TPTaxID Then $OwnRent = $OwnRent + $TPRent
    
    WEnd
    
    ; Convert String to 2 Decimal Places
    $OwnRent = StringFormat("%.2f", $OwnRent)
    
    ;ORIGINAL LINE
    MsgBox(0, "PWRead Data", $OwnName & "°" & $OwnAdd1 & "°" & $OwnAdd2 & "°" & $OwnCity & "°" & $OwnState & "°" & $OwnZIP & "°" & $OwnTaxID & "°" & $OwnRent)
    FileWriteLine($ACHFile, $OwnName & "°" & $OwnAdd1 & "°" & $OwnAdd2 & "°" & $OwnCity & "°" & $OwnState & "°" & $OwnZIP & "°" & $OwnTaxID & "°" & $OwnRent)
    ;FileWriteLine($ACHFile, $OwnName & "°" & $OwnAdd1 & "°" & $OwnAdd2)
FileClose($TPFile)
Next

Any help greatly appreciated

Edited January 18, 2010 by inov8iv

[enaiman]

It is better to use _FileReadToArray instead of While/Wend or For/Next + FileReadLine.

It is faster and it saves you a big headache.