compare txt from excel & notepad

[urikorulzs2]

i want to grab a row of txt with a loop and use it to search a txt file for the instance of that exact data, and then on that same lime grab other data and paste it into a new excell file.

so far ive gotten it to loop through reading the excel files and reading the line in the txt, but im nt sure how to search the line for the other data and send it back to the excel file. Heres my code.

expandcollapse popup

#Include <Excel.au3>

; open the excell workbook 
$sFilePath1 = @ScriptDir & "\JB307_02ENVIRONMENT_031911.xls" 
$oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
        Exit
ElseIf @error = 2 Then
    MsgBox(0, "Incorrect File name!")
    Exit
EndIf

; read a cell of data

$var1 = "Ccomp" 
$var2 = "Store"
$var3 = "Stan"
$row = 4
$searchstring =  ( "\s", $var1 + "," + $var2 + "," + $var3 )



$var1 = _ExcelReadCell($oExcel, $row, 1)    
$row += 1

; while loop to scan excel doc for the search string
While 1
    
    $var2 = _ExcelReadCell($oExcel, $row, 2)
    $var3 = _ExcelReadCell($oExcel, $row, 9)

        ; open the logs to search for the search string 
        $search = FileFindFirstFile("*.*") 
        
        If $search = -1 Then
            MsgBox(0, "Error", "No files/directories ")
            Exit
        EndIf

        While 1
            $file = FileFindNextFile($search) 
            If @error Then ExitLoop
                EndIf   
            While 1
                $line = FileReadLine($file)
                If @error = -1 Then ExitLoop
                EndIf
                
                $result = StringInStr("$line", "$searchstring")         
                
                if $result = 1
                    
                
                $oExcel = _ExcelBookNew() 
                
                
                $var6 = "timestamp"
                $var4 = "Amnt"
                $var5 = "Trans Time"
                
                
                
                while 1 
                    $var4 <> " "
                    $var5 <> " "

                    $var4 = _ExcelWriteCell($oExcel, "$var4", 1, 1)
                    $var5 = _ExcelWriteCell($oExcel, "$var5", 1, 2) 
                WEnd
            
            Wend
            
                
        WEnd
        
        FileClose($search)

; increment the row from the excel ssearch
    $row += 1

    if _ExcelReadCell($oExcel, $row, 1) <>"" Then
        $var1 = _ExcelReadCell($oExcel, $row, 1)
        $row += 1
        
    elseif _ExcelReadCell($oExcel, $row, 2) == "" Then 
        ExitLoop
        
    EndIf
            
    MsgBox(0, "", "The Company is: " & $var1, 5) 
    MsgBox(0, "", "The Stor is: " & $var2, 1) 
    MsgBox(0, "", "The Stan is: " & $var3, 1) 

    WEnd

[enaiman]

Hello urikorulzs2 and welcome to the forum.

The way you wrote your script is not very efficient; by using arrays it can be made simpler and faster.

My suggestions:

- read whole Excel file into an array (_ExcelReadSheetToArray)

- read the whole log file into a different array (_FileReadToArray)

- use these arrays to search in them, retrieve elements ... there are plenty of array related functions to help you deal with this.

Your code is also messed up because you are using variables enclosed in quotes which will make them to be regarded as text ($result = StringInStr("$line", "$searchstring")), you are opening a new Excel file with the same object name ($oExcel) which will lead to trouble, also you are creating first some $var1, $var2, $var3 then you overwrite them ...

As it looks now, your code does not have many chances to work - I cannot figure what you want to find and to do. If you want some help you better give some details about how the Excel file structure looks, what you want to find and what you want to do next.

That's the way it works, if you need help, you need to post more detailed info because nobody I know is a mind-reader.

[urikorulzs2]

here ids what im trying to do.

i want the script to read the excel file line by line but for lines 2,3 5,6 only columns 2 and 6.

1118

abcd 460 01 01 black 35282

abcd 461 01 01 black 51286

2113 Count = 5

abcd 4 01 01 black 24153

abcd 5 02 01 black 50480

it needs to read this data above, and find the line below that contains the data in coloum 1, 2 and 6 and then write the time stamp of that line to a new excel file.

the last row of data is in the second time stamp which should be colored red.

3/19/2011 12:21:40.067 - [LogDeclinedTransaction:66.227.255.114] Client Key: {47D1A2D3-CCD1-4835-A7C5-C69C905F4143}, REQ: MTX<E8><00>2TAa101394<1C>Ab1518<1C>Ac000901<1C>Ad20110319150749<1C>Ae18648<1C>Af209<1C>Ah00000245<1C>AoComm Line Down<1C>ArN<1C>Bd**********0706<1C>BfS<1C>BnCK<1C>BqCKCheck Auth<1C>Ca272483015<1C>Cb10041885500706<1C>Cc5444<1C>CeP<1C>Da1042<1C>EaMR<1C>Eb272483015T10041885500706A5444<1C>EcS<1C>Ga01<1C>Gb1115<1C>GfG

3/19/2011 12:21:49.504 - [LogDransacti209.163.221.11] Client Key: {47D1A2D3-CCD1-4835-A7C5-C69C905F4143}, REQ: MTX<B4><00>2TAa2113<1C>Ab5<1C>Ac000201<1C>Ad20110319141934<1C>Ae50480<1C>Af209<1C>Ah127<1C>AoComm Line Down<1C>ArY<1C>Bd************8466<1C>Be****<1C>BfS<1C>BnVS<1C>BqVSVISA<1C>Da933<1C>Ga05<1C>Gb130<1C>GfG<1C>Ha*********************<1C>Ic099-590-1443

3/19/2011 12:21:53.817 - [LogDeclinedTransaction:216.216.80.178] Client Key: {47D1A2D3-CCD1-4835-A7C5-C69C905F4143}, REQ: MTX<CA><00>2TAa101130<1C>Ab909<1C>Ac000201<1C>Ad20110319150744<1C>Ae15929<1C>Af209<1C>Ah00005592<1C>AoComm Line Down<1C>ArY<1C>Bd************9402<1C>Be****<1C>BfS<1C>BkC<1C>BnMC<1C>BqMCMaster Card<1C>Da2864<1C>Ga01<1C>Gb144<1C>GfG<1C>Ha**********************<1C>Ic763-263-926

3/19/2011 12:21:53.989 - [LogDeclinedTransaction:74.239.58.205] Client Key: {47D1A2D3-CCD1-4835-A7C5-C69C905F4143}, REQ: MTX<C9><00>2TAa4192<1C>Ab806358<1C>Ac000101<1C>Ad20110319140448<1C>Ae59712<1C>Af209<1C>Ah347846<1C>AoComm Line Down<1C>ArY<1C>Bd************3452<1C>Be****<1C>BfS<1C>BnDB<1C>BqDBDebit Card<1C>Da2894<1C>Ga05<1C>Gb18<1C>GfG<1C>Ha***********************<1C>Ib0280900064C000DC

Edited March 28, 2011 by urikorulzs2