Find text in files from values in excel

[pranaynanda]

I have a folder with a bunch of files (basically JBoss log files). I want to check if the username in the Excel that I have exists in the log file. I tried using various methods like putting a split or searching for a delimiter but the results aren't as efficient as I would like them to be.

 

This is my current code:

 

Global $open=FileSelectFolder("Select Folder","")
    ;$sFolder = ControlGetText("Automation", "", "Edit1")
    Global $FileList = _FileListToArrayRec($open, "*.*",1,1,1,2)

        If @error = 1 Then
            MsgBox(0, "", "No Folders Found.")
            Exit
        EndIf
        If @error = 4 Then
            MsgBox(0, "", "No Files Found.")
            Exit
        EndIf
        
        FileReadToArray($FileList)
        
        For $i = 1 To $FileList[0]
            StringSplit($FileList[$i]," ")
        Next

I want to try using ExcelRangeRead to match it up with the list of users but I'm not sure how.

 

Any help is appreciated.

Edited February 8, 2017 by pranaynanda

[water]

1 minute ago, pranaynanda said:

I want to check if the username in the Excel that I have exists in the log file

Is the username a part of the filename or of the log files content?

[pranaynanda]

It's the Log files content

[pranaynanda]

I want to workout something like VLOOKUP in Excel. But the elements to search are in an Excel file and the content to search from is a text file.

[water]

Q&D:

#include <File.au3>

Global $aUserNames[] = ["User1", "User2"] ; Usernames need to be read from Excel
Global $sFolderPath = FileSelectFolder("Select Folder", "")
Global $aFileList = _FileListToArrayRec($sFolderPath, "*.*", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_SORT, $FLTAR_FULLPATH)
If @error = 1 Then Exit MsgBox(0, "", "No Folders Found.")
If @error = 4 Then Exit MsgBox(0, "", "No Files Found.")

For $i = 1 To $aFileList[0]
    $sFileContent = FileRead($aFileList[$i])
    For $j = 0 To UBound($aUserNames) - 1
        If StringInStr($sFileContent, $aUserNames[$j]) Then MsgBox(0, "Info", "User " & $aUserNames[$j] & " found in file " & $aFileList[$i])
    Next
Next

A regular expression might be faster to find all occurrences of user names in the string.

[pranaynanda]

3 hours ago, water said:

A regular expression might be faster to find all occurrences of user names in the string.

The problem is that the user names do not occur in regular expression.

[water]

Regular Expression is a tool to process strings. So with the correct PCRE all users could be searched in one go and would replace processing the whole string for each user with StringInStr.

[jguinch]

Using a RegEx :

#include <File.au3>

Global $aUserNames[] = ["User1", "User2"] ; Usernames need to be read from Excel
Global $sFolderPath = FileSelectFolder("Select Folder", "")
Global $aFileList = _FileListToArrayRec($sFolderPath, "*.*", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_SORT, $FLTAR_FULLPATH)
If @error = 1 Then Exit MsgBox(0, "", "No Folders Found.")
If @error = 4 Then Exit MsgBox(0, "", "No Files Found.")

Local $sRegEx = "(?i)"
For $i = 0 To UBound($aUserNames) - 1
    $sRegEx &= "\b" & $aUserNames[$i] & "\b|"
Next
$sRegEx = StringTrimRight($sRegEx, 1)

For $i = 1 To $aFileList[0]
    $sFileContent = FileRead($aFileList[$i])
    If StringRegExp($sFileContent, $sRegEx) Then MsgBox(0, "Info", "One of more users found in file " & $aFileList[$i])
Next

 

[pranaynanda]

Guys, you have been great help so far. Call it lack of experience for me, but I right now I'm unable to comprehend the concept of Regular Expressions here altogether.

[water]

12 minutes ago, pranaynanda said:

but I right now I'm unable to comprehend the concept of Regular Expressions here altogether.

Welcome to the club

[pranaynanda]

People, I tried executing this code. AutoIt returns an error. Any ideas? 

[pranaynanda]

Hi! I'm trying to use this code. Do you think this will work? I intend to loop through the folder using _FindInFile()

How does that sound for an idea?

$file="userlist.xlsx"
$Array=_Excel_RangeRead($file,Default,"B1")
_ArrayDisplay($Array)

 

[junkew]

why use excel and autoit?

• there are tools like grep or powershell for this

http://stackoverflow.com/questions/8153750/how-to-search-a-string-in-multiple-files-and-return-the-names-of-files-in-powers

Get-ChildItem -recurse | Select-String -pattern "dummy" | group path | select name

 

[pranaynanda]

4 hours ago, junkew said:

why use excel and autoit?

Because it's just not one string, it's a whole column in Excel that needs to be searched through multiple files in multiple separate folders. I can manage doing it manually for each folder but I'd rather have automated the other part.

[pranaynanda]

Okay guys. All the text in one of those folders weighs hefty 5.8 GBs. Others weigh equivalent. I get a message about error allocating memory. Any ideas to help it? My computer has 8 GB of RAM.

[water]

FileRead tries to read the whole file into a single variable. A variable is limited to 2GB (according tot he help file: MAX_STRINGLEN = 2,147,483,647 Maximum string length.)
Seems you need another approach. Either read the file in chunks or use one of the suggested tools.

[pranaynanda]

2 minutes ago, water said:

A variable is limited to 2GB (according tot he help file: MAX_STRINGLEN = 2,147,483,647 Maximum string length.)

There's some answer that I have been looking for. I checked the folders and the largest file is sized approximately around 800 MB which I think according to what you shared should make it work. But it does not? I believe it was very stupid of me to concatenate all those files into a single file around around 5.8 GBs.

[water]

You might run into problems even if the variable should hold less than 2GB. The allocated storage for a variable needs to be contiguous.

[pranaynanda]

it seems that file read has a hard time with any file larger than 500 MB as I was able to load the ones lesser than that in StringRegExpGUI.au3. Do you think file _FileListToArrayRec will help?

https://www.autoitscript.com/forum/index.php?act=Attach&type=post&id=7670

[junkew]

http://stackoverflow.com/questions/37470964/how-to-search-multiple-files-for-multiple-strings