Find text in files from values in excel

[pranaynanda]

I cut short the file sizes and used multiple tools like excel and grep in linux. So now there's only two files which weigh at most 184 MB with all the necessary information. Now I need assistance with passing values from excel as regex and writing it to another pre-existing excel. Any help please?

[jguinch]

Grep seems to be a good way for big files + search my regex. It's also available for Windows : http://gnuwin32.sourceforge.net/packages/grep.htm
I'm pretty sure that it's easily doable using PowerShell. @iamtheky works with it, maye he has already done this kind of job ?

For Excel, there is an Excel UDF coming with your AutoIt installation.
Please, look at the help file, there is an example or more per function.

 

[junkew]

Maybe some steps back

1. why you want this?
2. what you want? it seems to be getting longer request compared to original post
3. how you want it?

Starting at 2 (as 1 is related to other things) it starts with requirements like

• speed of intended soluton you want
• size of your logfiles and the # of files
• frequency your script has to run (every second or once a day makes a huge difference for the solution you choose.
• number of users / rows in your excel column
• ....

for 3 the how you seem to have choosen a solution in excel with AutoIt and for whatever reason no VBA solution (or javascript in excel 2016) and for me not clear why not to choose intended tools like GREP / Powershell for the solution you want. Many standard solutions are there to analyse logfiles.

• If you make your own solution maybe you need it multhithreaded to speed things up a little.

 

regarding your latest question

• " Now I need assistance with passing values from excel as regex and writing it to another pre-existing excel. Any help please? "

you need to copy a range from a to b

_Excel_RangeCopyPaste, Excel_RangeRead, _Excel_RangeWrite will handle that for you

[iamtheky]

Can you provide a small reproducer of each file.  And are you on win10 where bash grep will be just as viable a solution as powershell?

[pranaynanda]

I did this finally:

$conUserList=_Excel_Open()
    $WorkbookUserList="userlist.xlsx"
    $WorksheetUserList="Table1"
    $WorkbookUserList=_Excel_BookOpen($conUserList,$WorkbookUserList)
    $UserNames=_Excel_RangeRead($WorkbookUserList,$WorksheetUserList,$WorkbookUserList.ActiveSheet.Usedrange.Columns("B:B"),1)

    $conMachineName=_Excel_Open()
    $WorkbookMachineName="linux_workstations.xlsx"
    $WorksheetMachineName="Table1"
    $WorkbookMachineName=_Excel_BookOpen($conUserList,$WorkbookMachineName)
    $MachineName=_Excel_RangeRead($WorkbookMachineName,$WorksheetMachineName,$WorkbookMachineName.ActiveSheet.Usedrange.Columns("A:A"),1)
    
    For $j=0 To UBound($MachineName)-1
    For $i=0 To UBound($UserNames)-1
    $sRegEx= "((" & $MachineName[$j] & ")(\.)(-?\d*)(\.)(\d*)(\.)(\b" & $UserNames[$i] & "\b))"
    ConsoleWrite($sRegEx & @CRLF)
    If StringRegExp($sFileContent,$sRegEx) Then
        FileWriteLine($Output,$MachineName[$j] & @TAB & $UserNames[$i])
    EndIf
    Next
    Next

I know it's kinda redundant and I would like to optimize the functionality as it takes much longer but this is what I need right now. I'll use VLOOKUP in excel to derive exactly what I need.

[pranaynanda]

Can anyone provide ideas to speed this up? It usually takes about 5 hours for this code to parse through more than 3 million permutations of these Machine names and user names.

[iamtheky]

unless you can meet the request in #24, probably not.

[kylomas]

You are spinning $usernames for every instance of $machinename.  You can try turning $usernames into a delimited string and use  regex to search the string once for each instance of $machinename.

kylomas

[pranaynanda]

On 2/11/2017 at 7:21 PM, iamtheky said:

And are you on win10 where bash grep will be just as viable a solution as powershell?

No. It's Windows 7.

On 2/11/2017 at 7:21 PM, iamtheky said:

Can you provide a small reproducer of each file

Probably no. It's meant to be kept secure.

[pranaynanda]

5 hours ago, kylomas said:

You are spinning $usernames for every instance of $machinename.  You can try turning $usernames into a delimited string and use  regex to search the string once for each instance of $machinename.

Sounds beautiful! Except for the fact that I did not understand anything and how to do it.

[iamtheky]

Just to be sure:

You cant anonymize a couple of lines to even show us what you are iterating through, and you expect people to just keep guessing?

[pranaynanda]

16 hours ago, iamtheky said:

You cant anonymize a couple of lines to even show us what you are iterating through,

I'm sorry. I'll try though.