Using AutoIT to pull specific numbers from spreadsheet

[water]

But you always get the blue "A" icon in the task bar. Hover the mouse over it and you will see a tooltip with the currently executed statement.

[JadeRae]

OK, it was only showing up for a minute when I ran the script, that's why I wasn't seeing it.

I was able to get the data that it shows right before it disappears: "Excel.au3 Line 67: $oExcel = ObjCreate ("Excel.Application")"

[water]

I will test tomorrow when I return to my office

[JadeRae]

Thank you sir!

[water]

The following script works here with the example Excel you posted plus a CSV file that looks like:

5551234566,User 1
5551234567,User 2
5551234568,User 3
5551234569,User 4

expandcollapse popup

;Pull Phone Numbers and Print Data

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

Local $sScriptTitle = "Pioneer Phone Report" ; Script title
Local $sPrevNumber = "", $iCount = 0, $aANINumbers
Local $iCol = 6 ; Offset of the column where ANI is stored (0 = column A)
Local $sWorkbook = @ScriptDir & "\Example.xlsx" ; Path of the workbook to be analyzed
Local $sANINames = @ScriptDir & "\Example.csv" ; Path of the CSV file with ANI numbers and names
Local $aANINames
Local $sANINumbers = InputBox($sScriptTitle, "Please enter the ANI numbers to process separated by a space:", "5551234566 5551234567 5551234568 5551234569", "", 500, 130)
If @error Then Exit ; Cancel button pressed
$aANINumbers = StringSplit($sANINumbers, " ", $STR_NOCOUNT)
_FileReadToArray($sANINames, $aANINames, $FRTA_NOCOUNT, ",") ; Split the file into a 2D array. Separator is ","
If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error opening file '" & $sANINames & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
If UBound($aANINames, 0) <> 2 Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "File '" & $sANINames & "' is not a 2D array.")
Local $oExcel = _Excel_Open(False) ; Start Excel in the background or connect to a running instance
If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error starting Excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) ; Open workbook
If @error Then ; Notify if cannot find specific file
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error opening workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
Local $aRecords = _Excel_RangeRead($oWorkbook) ; Reads the whole workbook into the array
If @error Then
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error reading workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
If UBound($aRecords, 2) < $iCol + 1 Then ; Check the minimum number of needed columns
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "There are not at least " & $iCol + 1 & " columns in the workbook '" & $sWorkbook & "'.")
    _Excel_Close($oExcel)
    Exit
EndIf
_ArraySort($aRecords, 0, 1, 0, $iCol) ; Sort the array on the column with ANI (ignore heading line)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error sorting the input data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
For $i = 1 To UBound($aRecords, 1) - 1 ; Ignore heading line
    If $sPrevNumber <> $aRecords[$i][$iCol] Then
        If $sPrevNumber <> "" Then _NewCustomer($sPrevNumber, $iCount)
        $sPrevNumber = $aRecords[$i][$iCol]
    EndIf
    $iCount = $iCount + 1
Next
_NewCustomer($aRecords[$i - 1][$iCol], $iCount) ; Process last customer
_Excel_Close($oExcel, False)
Exit

Func _NewCustomer($sANI, ByRef $iCount)
    Local $iIndex = 0, $sName = "** No Name Found **"
    For $iIndex = 0 To UBound($aANINumbers, 1) - 1
        If $aANINumbers[$iIndex] = $sANI Then ; Only display results for selected ANINumbers
            For $iIndex2 = 0 To UBound($aANINames, 1) - 1 ; Grab the name for the ANINumber
                If $aANINames[$iIndex2][0] = $sANI Then
                    $sName = $aANINames[$iIndex2][1]
                    ExitLoop
                EndIf
            Next
            ConsoleWrite("ANI: " & $sANI & ", Name: " & $sName & ", Count: " & $iCount & @CRLF)
            ExitLoop
        EndIf
    Next
    $iCount = 0
EndFunc   ;==>_NewCustomer

 

[water]

The problem was caused by the ANI numbers to be processed (hard coded in the script) not showing up in the Excel file.

BTW: If performance is an issue then we might have a look at a solution using _Excel_RangeFind.

Edited May 18, 2016 by water

[JadeRae]

@water  Thank you!

I made the recommended changes.  Here is what the script looks like.

expandcollapse popup

; Extract Contact Data v2
; Pull Phone Numbers and Print Numbers of times they called in

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

AutoItSetOption("TrayIconDebug", 1)

; CONFIGURATION
Local $sScriptTitle = "Pioneer Phone Report" ; Script title for MsgBox
Local $sPrevNumber = "", $iCount = 0, $aANINumbers
Local $iCol = 15 ; Offset of the column where ANI is stored (0 = column A)
Local $sWorkbook = @ScriptDir &  "C:\Users\ENSXI\Desktop\sheet1.xlsx" ; Path of the workbook to be analyzed
Local $sANINames = @ScriptDir & "C:\Users\ENSXI\Documents\AutoIT\Scripts\Contact Names.csv" ; Path of the CSV file with technician numbers and names
Local $aANINames
Local $sANINumbers = InputBox($sScriptTitle, "Please enter the ANI numbers to process separated by a space:", "6125702362 9523220388 4026460312 3176174256 3143717106 6018740303 6412200969", "", 500, 130)

If @error Then Exit ; Cancel button pressed
$aANINumbers = StringSplit($sANINumbers, " ", $STR_NOCOUNT)

;Read Excel file
_FileReadToArray($sANINames, $aANINames, $FRTA_NOCOUNT, ",") ; Split the file into a 2D array. Separator is ","

If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error opening file '" & $sANINames & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
If UBound($aANINames, 0) <> 2 Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "File '" & $sANINames & "' is not a 2D array.")

; OPEN EXCEL WORKBOOK
Local $oExcel = _Excel_Open(False) ; Start Excel in the background or connect to a running instance
If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error starting Excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) ; Open workbook

If @error Then ; Notify if cannot find specific file
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error opening workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

Local $aRecords = _Excel_RangeRead($oWorkbook) ; Reads the whole workbook into the array

If @error Then
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error reading workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

;CHECK DATA APPEARS VALID
If UBound($aRecords, 2) < $iCol + 1 Then ; Check the minimum number of needed columns
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "There are not at least " & $iCol + 1 & " columns in the workbook '" & $sWorkbook & "'.")
    _Excel_Close($oExcel)
    Exit
EndIf

; SORT THE DATA SET
_ArraySort($aRecords, 0, 1, 0, $iCol) ; Sort the array on the column with ANI (ignore heading line)

;Error if unable to sort
If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error sorting the input data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; LOOP THROUGH ALL CALL RECORDS
For $i = 1 To UBound($aRecords, 1) - 1 ; Ignore heading line
    If $sPrevNumber <> $aRecords[$i][$iCol] Then ; Number has changed
        If $sPrevNumber <> "" Then _NewCustomer($sPrevNumber, $iCount)
        $sPrevNumber = $aRecords[$i][$iCol]
    EndIf
    $iCount = $iCount + 1
Next

_NewCustomer($aRecords[$i - 1][$iCol], $iCount) ; Process last customer
_Excel_Close($oExcel, False) ;close out the excel window
Exit

Func _NewCustomer($sANI, ByRef $iCount)
    Local $iIndex = 0, $sName = "** No Name Found **"
    For $iIndex = 0 To UBound($aANINumbers, 1) - 1 ; loops through technicians
        If $aANINumbers[$iIndex] = $sANI Then ; Only display results for selected ANINumbers
            For $iIndex2 = 0 To UBound($aANINames, 1) - 1 ; Grab the name for the ANINumber
                If $aANINames[$iIndex2][0] = $sANI Then ; find technician's name
                    $sName = $aANINames[$iIndex2][1] ; set the call record's name to be the technician's name
                    ExitLoop
                EndIf
            Next
            ConsoleWrite("ANI: " & $sANI & ", Name: " & $sName & ", Count: " & $iCount & @CRLF)
            ExitLoop
        EndIf
    Next
    $iCount = 0
EndFunc   ;==>_NewCustomer

I am getting an error saying, "Error Opening File, 'C:/.../Contact Names.CSV' @error=1, @extended=0"

I'm guessing this is something simple that I did wrong.

Edited May 18, 2016 by JadeRae

[JadeRae]

Actually I believe I figured out why it wasn't working, I removed @scriptdir from both lines and it is loading the file fine now.

 

However I am still not getting any output from it

[water]

Does the Excel file contain any of this numbers? That's why I got no output at all when I started to test.

6125702362 9523220388 4026460312 3176174256 3143717106 6018740303 6412200969

[JadeRae]

Yes it does.  

I am searching the correct column, right?  The information is in column 'P' so I put the column as 15 in my script.

Would it make a difference that the report I am pulling has 11 rows of header information before it gets to the actual data?

[water]

No, it shouldn't make any difference.
I will add some debugging information tomorrow so we know where something goes wrong.

[JadeRae]

10-4

Thank you @water

[water]

I just tested your script with the example Excel file you provided. It works perfect and spits out the expected result.
I only had to change the file names and the ANI numbers to look for.

I have added a debug line in function _NewCustomer to display some data when the ANI number changes. Could you please run this script with your Excel file?

expandcollapse popup

; Extract Contact Data v2
; Pull Phone Numbers and Print Numbers of times they called in

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

AutoItSetOption("TrayIconDebug", 1)

; CONFIGURATION
Local $sScriptTitle = "Pioneer Phone Report" ; Script title for MsgBox
Local $sPrevNumber = "", $iCount = 0, $aANINumbers
Local $iCol = 15 ; Offset of the column where ANI is stored (0 = column A)
Local $sWorkbook = @ScriptDir & "C:\Users\ENSXI\Desktop\sheet1.xlsx" ; Path of the workbook to be analyzed
Local $sANINames = @ScriptDir & "C:\Users\ENSXI\Documents\AutoIT\Scripts\Contact Names.csv" ; Path of the CSV file with technician numbers and names
Local $aANINames
Local $sANINumbers = InputBox($sScriptTitle, "Please enter the ANI numbers to process separated by a space:", "6125702362 9523220388 4026460312 3176174256 3143717106 6018740303 6412200969", "", 500, 130)

If @error Then Exit ; Cancel button pressed
$aANINumbers = StringSplit($sANINumbers, " ", $STR_NOCOUNT)

;Read Excel file
_FileReadToArray($sANINames, $aANINames, $FRTA_NOCOUNT, ",") ; Split the file into a 2D array. Separator is ","

If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error opening file '" & $sANINames & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
If UBound($aANINames, 0) <> 2 Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "File '" & $sANINames & "' is not a 2D array.")

; OPEN EXCEL WORKBOOK
Local $oExcel = _Excel_Open(False) ; Start Excel in the background or connect to a running instance
If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error starting Excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) ; Open workbook

If @error Then ; Notify if cannot find specific file
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error opening workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

Local $aRecords = _Excel_RangeRead($oWorkbook) ; Reads the whole workbook into the array

If @error Then
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error reading workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

;CHECK DATA APPEARS VALID
If UBound($aRecords, 2) < $iCol + 1 Then ; Check the minimum number of needed columns
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "There are not at least " & $iCol + 1 & " columns in the workbook '" & $sWorkbook & "'.")
    _Excel_Close($oExcel)
    Exit
EndIf

; SORT THE DATA SET
_ArraySort($aRecords, 0, 1, 0, $iCol) ; Sort the array on the column with ANI (ignore heading line)

;Error if unable to sort
If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error sorting the input data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; LOOP THROUGH ALL CALL RECORDS
For $i = 1 To UBound($aRecords, 1) - 1 ; Ignore heading line
    If $sPrevNumber <> $aRecords[$i][$iCol] Then ; Number has changed
        If $sPrevNumber <> "" Then _NewCustomer($sPrevNumber, $iCount)
        $sPrevNumber = $aRecords[$i][$iCol]
    EndIf
    $iCount = $iCount + 1
Next

_NewCustomer($aRecords[$i - 1][$iCol], $iCount) ; Process last customer
_Excel_Close($oExcel, False) ;close out the excel window
Exit

Func _NewCustomer($sANI, ByRef $iCount)
    ConsoleWrite("ANI found in Excel: " & $sANI & ", Count: " & $iCount & @CRLF)
    Local $iIndex = 0, $sName = "** No Name Found **"
    For $iIndex = 0 To UBound($aANINumbers, 1) - 1 ; loops through technicians
        If $aANINumbers[$iIndex] = $sANI Then ; Only display results for selected ANINumbers
            For $iIndex2 = 0 To UBound($aANINames, 1) - 1 ; Grab the name for the ANINumber
                If $aANINames[$iIndex2][0] = $sANI Then ; find technician's name
                    $sName = $aANINames[$iIndex2][1] ; set the call record's name to be the technician's name
                    ExitLoop
                EndIf
            Next
            ConsoleWrite("ANI selected      : " & $sANI & ", Name: " & $sName & ", Count: " & $iCount & @CRLF)
            ExitLoop
        EndIf
    Next
    $iCount = 0
EndFunc   ;==>_NewCustomer

 

[JadeRae]

I'm still not getting an output, is it supposed to be opening a new excel file to output the data in?

[water]

No output at all?
You should at least get some output from the first line in function _NewCustomer

ConsoleWrite("ANI found in Excel: " & $sANI & ", Count: " & $iCount & @CRLF)

BTW: You should see those lines written to the output pane of SciTE.

Edited May 19, 2016 by water

[JadeRae]

Is it becuase I do not have the script compiled yet?  I was doing some googling and saw a few other people having this issue.

[water]

Just press F5 in SciTE and you should be fine.
When the script is compiled then there is no console available where ConsoleWrite could write its data to.

[JadeRae]

I had no idea it was that easy to test it!  This whole time I've been running it manually to check!

Unfortunately it still did not output any data.  However here are the full results from the window that popped up when I pressed F5, if that helps troubleshoot.

Quote

>"C:\Users\ENSXI\Documents\AutoIT\autoitruntime\..\AutoIt3.exe" "C:\Users\ENSXI\Documents\AutoIT\autoitruntime\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "C:\Users\ENSXI\Documents\AutoIT\Scripts\Extract Contact Data v3.au3" /UserParams    
+>09:09:22 Starting AutoIt3Wrapper v.15.920.938.0 SciTE v.3.6.0.0   Keyboard:00000409  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64    Environment(Language:0409)
+>         SciTEDir => C:\Users\ENSXI\Documents\AutoIT\autoitruntime   UserDir => C:\Users\ENSXI\Documents\AutoIT\autoitruntime\AutoIt3Wrapper
>Running AU3Check (3.3.14.2)  from:C:\Users\ENSXI\Documents\AutoIT  input:C:\Users\ENSXI\Documents\AutoIT\Scripts\Extract Contact Data v3.au3
+>09:09:22 AU3Check ended.rc:0
>Running:(3.3.14.2):C:\Users\ENSXI\Documents\AutoIT\autoit3_x64.exe "C:\Users\ENSXI\Documents\AutoIT\Scripts\Extract Contact Data v3.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
ANI found in Excel: 2162547506, Count: 426
ANI selected      : 3143717106, Name: Julie Wade, Count: 2
ANI selected      : 3176174256, Name: Jeff Lewis, Count: 1
ANI selected      : 9523220388, Name: James Rauls, Count: 2
+>09:09:28 AutoIt3.exe ended.rc:0
+>09:09:28 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 6.639

EDIT: there were a lot of other numbers in there, but I deleted them for privacy.

Edited May 19, 2016 by JadeRae

[water]

The script runs perfect!
 

Quote

ANI found in Excel: 2162547506, Count: 426

means that this ANI was found in the Excel file 426 times but was not selected by you via the Input GUI.

Quote

ANI selected      : 9523220388, Name: James Rauls, Count: 2

means that this ANI was found 2 times in the Excel and was selected by you. That's a valid result!

From the result I see that 3 ANI numbers of the numbers you have selected have been found.

Next question:
Where and how do you want this 3 lines to be stored/saved? On a separate worksheet?

Edited May 19, 2016 by water

[JadeRae]

Ok.  I guess I was just confused.  Thank you @water.

As to how I'd like them to be stored - Ideally I'd like to have a message box pop up with the results in it, and an export option to send the results to an Excel document.

However... just sending them to and Excel document will also serve the purpose, I can work on making a message box later.