Using AutoIT to pull specific numbers from spreadsheet

[JadeRae]

Hello,

I am very new to using AutoIT, however, it has already worked very well for me for a simple heads up display that we are using at my company, so I figured I would give it a go with another need we have here.

My real question is, is this possible with AutoIT?  If it is, I'm sure I can figure it out, but if someone with more experience than me looks at this and deems it impossible I will move on.

I would like to create a script that opens an excel document with 1000+ rows, pulls the count of several different cell values (phone numbers) from the spreadsheet, and then outputs the number pulled, the count, and a name associated with the number to another excel document.

The goal is to see how many times certain individuals are calling in.

Is this feasible?

Thank you in advance!

[JLogan3o13]

Hi, @JadeRae, welcome to the forum. The short answer to your question is yes, what you're looking to do can be done with the Excel functions that come with AutoIt. Judging by your question, I would guess you'll be needing the functions below as a start (plenty more help and examples to be found in the help file):

_Excel_Open
_Excel_BookOpen
_Excel_RangeRead

I would start with the example under _Excel_Open, and try to modify that to your needs. If you get stuck, don't hesitate to post what you have here (even if it isn't doing what you would like it to), and we will do our best to assist

Edited May 16, 2016 by JLogan3o13

[JadeRae]

Thank you so much for the fast answer @JLogan3o13!  I will start there and see where I get.  I must say, AutoIT has some of the best help documentation I have ever seen!  Hopefully I will be able to figure it out with that, but if not, I will be reaching out again.

Thank you!

[water]

Welcome to AutoIt and the forum!
As JLogan3o13 has already stated: Yes, you can!
Can you give us some more information about the Excel workbook?
I understand from your post that it has a row for each call that holds (at least) the number and the name of the caller.
Is the worksheet already sorted on the phone number?

[JadeRae]

I attached a (very simplistic) example of the kind of call data that I am dealing with.  Under the ANI column I can have thousands of different numbers, depending on how large of a date range I use.  I need to have a fast way to pull out about 15 key numbers, count how many of each, and then export those results to a database while also adding on the name of the user that is associated with that number.

So just for the example that I uploaded there are 14 instances of the ANI number '5551234567'.  And say that number is associated with Jim Bean.  I would want the row in my newly generated Excel file to look like this, "Jim Bean/5551234567/14"

Hope that makes sense, thank you for you help!

example.xlsx

[water]

If 2 people called at different times of the day are the records mixed or already sorted?

 

[JadeRae]

@water They are default sorted by date, so the phone numbers would not be together

[water]

OK, so I would start with this piece of code (untested):

#include <Excel.au3>
Global $sPrevNumber = "", $iCount = 0
Global $oExcel = _Excel_Open() ; Start Excel or connect to a running instance
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\test.xlsx") ; Open the workbook <== You need to change the the path
Global $aRecords = _Excel_RangeRead($oWorkbook) ; Reads the whole workbook into the array
_ArraySort($aRecords, 0, 0, 0, 6) ; Sort the array on column 6 (0 = first column)
For $i = 0 to UBound($aRecords, 1) - 1
    If $sPrevNumber <> $aRecords[$i][6] Then ; Number has changed
        If $sPrevNumber <> "" Then
            ; <== Do whatever needs to be done when the number changes
            ConsoleWrite("Customer " & $sPrevNumber & " called " & $iCount & " times." & @CRLF) ; <== Just an example
            $iCount = 0
        EndIf
        $sPrevNumber = $aRecords[$i][6]
        $iCount = $iCount + 1
    EndIf
Next
; Process last customer
; <== Do whatever needs to be done when the number changes
ConsoleWrite("Customer " & $aRecords[$i][6] & " called " & $iCount & " times." & @CRLF) ; <== Just an example

Lines marked with "; <==" need to be changed by you.

[JadeRae]

Ok, so first part of that is what I have right now, opening the excel document and reading it.  However I believe the second part of the script (I could be wrong, I'm very new at this) needs to be a search for a few different numbers and counting how many of each there are.  Perhaps using Function_ArrayBinarySearch?

 

Thank you again for your help!

[water]

I have never used _ArrayBinarySearch but I'm sure it won't always find the first record of a ANI number. So you would have to read backwards to get the first record and then read forward until the ANI number changes or you have reached the end of the array.

Do you have collected all the ANI numbers you are looking for when you open the Excel workbook?

[JadeRae]

Yes I have the list of them.

I need to find the total count of each in the document, not the first reference, I apologize if I misspoke!  

I'm trying to use _Excel_FilterSet, it's still not working correctly though.  This is what I have right now.  (only the first filter is done)

;Pull Phone Numbers and Print Data

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $sPrevNumber = "", $iCount = 0
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;notify if cannot start Excel
Local $oExcel = _Excel_Open() ; Start Excel or connect to a running instance
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\ENSXI\Desktop\sheet1.xlsx") ; Open the workbook

If @error Then ;Notify if cannot find specific file
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; Set filters
_Excel_FilterSet($oWorkbook, Default, "A:P", 3, "2162547506") ; Julie
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $aShow[] = ["20", "40", "60"]
_Excel_FilterSet($oWorkbook, Default, "A:P", 2, $aShow, $xlFilterValues)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Filters set:" & @CRLF & "  Column B: values = 20, 40 or 60." & @CRLF & "  Column C: values (216) 254 7506.")

; *****************************************************************************
; Display information about the filters on the active worksheet.
; *****************************************************************************
Local $aFilters = _Excel_FilterGet($oWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example 1", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ArrayDisplay($aFilters, "Excel UDF: _Excel_FilterGet Example 1", Default, Default, Default, "Filter on|#areas|Criteria1|Criteria2|Operator|Range|#Records")

 

[water]

FilterSet does not help in this case because the function only selects the rows that get DISPLAYED by Excel.

Can I assume that you store the ANI numbers to be processed in an array? If yes, then the changes to the script I posted would be minimal.

[JadeRae]

After looking at the array function it does look like this would work to store my data.  I am looking into getting the numbers loaded into the array.  If I get my data into the array, would it be easier for me to reference it later on when searching excel for # of occurrences of each number?

I apologize for my obvious noobness.  Thank you so much for your patience and support!

[water]

I assume that the  ANI numbers you are looking for have been stored in array $aANINumbers.
So I modified my previous script.
The whole workbook is being read into an array and for all customers the count is being processed but only displayed for those found in $aANINumbers.
If this works for you then the next task would be to fill this array

#include <Excel.au3>
Global $aANINumbers[3] = ["number1", "number2", "number3"]
Global $sPrevNumber = "", $iCount = 0
Global $oExcel = _Excel_Open() ; Start Excel or connect to a running instance
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\test.xlsx") ; Open the workbook <== You need to change the the path
Global $aRecords = _Excel_RangeRead($oWorkbook) ; Reads the whole workbook into the array
_ArraySort($aRecords, 0, 0, 0, 6) ; Sort the array on column 6 (0 = first column)
For $i = 0 to UBound($aRecords, 1) - 1
    If $sPrevNumber <> $aRecords[$i][6] Then ; Number has changed
        If $sPrevNumber <> "" Then _NewCustomer($sPrevNumber, $iCount)
        $sPrevNumber = $aRecords[$i][6]
        $iCount = $iCount + 1
    EndIf
Next
_NewCustomer($aRecords[$i][6], $iCount) ; Process last customer
Exit

Func _NewCustomer($sANI, ByRef $iCount)
    $iIndex = 0
    For $iIndex = 0 to UBound($aANINumbers, 1) - 1
        If $aANINumbers[$iIndex] = $sANI Then
            ConsoleWrite("ANI: " & $sANI & ", Count: " & $iCount & @CRLF)
            ExitLoop
        EndIf
    Next
    $iCount = 0
EndFunc

 

[JadeRae]

Question:  for the first feild $aANINumbers what would go into the quotes, what is that specifying?

 

Thanks, I think this is going to help a lot

[water]

For the example you posted:

Global $aANINumbers[1] = ["5551234567"]

 

[iamtheky]

Thats an array.  he declared it with [3] elements, and then specified those elements on the same line.

[JadeRae]

Ok, makes sense, I'm going to work on this for awhile.  I will get back in touch if I'm still having issues... (a very real possibility)

Thank you!

 

[JadeRae]

expandcollapse popup

;Pull Phone Numbers and Print Data

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>

Local $aANINumbers[7] = ["6412200969", "6018740303", "3176174256", "4026460312", "9523220388", "3143717106", "6125702362"]
Local $sPrevNumber = "", $iCount = 0
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;notify if cannot start Excel
Local $oExcel = _Excel_Open() ; Start Excel or connect to a running instance
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\ENSXI\Desktop\sheet1.xlsx") ; Open the workbook
Local $aRecords = _Excel_RangeRead($oWorkbook) ; Reads the whole workbook into the array
    _ArraySort($aRecords, 0, 0, 0, 15) ; Sort the array on column 15 (0 = first column)

If @error Then ;Notify if cannot find specific file
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

For $i = 0 to UBound($aRecords, 1) - 1
    If $sPrevNumber <> $aRecords[$i][6] Then ; Number has changed
        If $sPrevNumber <> "" Then _NewCustomer($sPrevNumber, $iCount)
        $sPrevNumber = $aRecords[$i][6]
        $iCount = $iCount + 1
    EndIf
Next
_NewCustomer($aRecords[$i][6], $iCount) ; Process last customer
Exit

Func _NewCustomer($sANI, ByRef $iCount)
    $iIndex = 0
    For $iIndex = 0 to UBound($aANINumbers, 1) - 1
        If $aANINumbers[$iIndex] = $sANI Then
            ConsoleWrite("ANI: " & $sANI & ", Count: " & $iCount & @CRLF)
            ExitLoop
        EndIf
    Next
    $iCount = 0
EndFunc

So here is what I am doing right now.  

 

I am getting an error on line 28 saying that "Array variable has incorrect number of subscripts or subscript dimension range exceeded.

 

[iamtheky]

Local $aRecords = _Excel_RangeRead($oWorkbook) ; Reads the whole workbook into the array
    _ArraySort($aRecords, 0, 0, 0, 15) ; Sort the array on column 15 (0 = first column)

Here, you are just assuming it has at least 15 columns, without verifying.  I dont know that it is the issue, but it couldnt hurt to put a check in between those lines that

If ubound($aRecords , 2) < 15 then advise the user and exit gracefully