Using AutoIT to pull specific numbers from spreadsheet

[water]

OK, quick and dirty:
The result gets displayed using _Arraydisplay. When you press "Run user Func" the array gets written to a new sheet in the open Excel workbook and the workbook gets displayed.

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, $iResultCount = 0, $aResultArray[1000][3], $sFuncName = _CopyToExcel
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 User Names
_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
ReDim $aResultArray[$iResultCount][3] ; Resize the result array
_Excel_Close($oExcel, False) ;close out the excel window

_Arraydisplay($aResultArray, $sScriptTitle, "", 64, Default, "ANI|Name|Count", Default, Default, $sFuncName)
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
            ; Write the record to the result array
            $aResultArray[$iResultCount][0] = $sANI
            $aResultArray[$iResultCount][1] = $sName
            $aResultArray[$iResultCount][2] = $iCount
            $iResultCount = $iResultCount + 1
            ExitLoop
        EndIf
    Next
    $iCount = 0
EndFunc   ;==>_NewCustomer

Func _CopyToExcel($aArray, $aSelectedItems)
    Local $aHeader[][] = [["ANI", "Name", "Count"]]; Add a sheet to the Excel workbook
    Local $oResultSheet = _Excel_SheetAdd($oWorkbook, -1, False, 1, "Result")
    ; Write the result to the new worksheet
    _Excel_RangeWrite($oWorkbook, $oResultSheet, $aHeader, "A1")
    _Excel_RangeWrite($oWorkbook, $oResultSheet, $aArray, "A2")
    ; Make Excel visible again
    $oExcel.Visible = True
EndFunc

 

[JadeRae]

Bahhh!  It works!

Thank you so much @water!

One question What is the difference between copy data & HDR/row and Copy Data Only?  

edited: asked a dumb question...

 

Edited May 19, 2016 by JadeRae

[water]

There are no dumb questions, only unasked questions

[JadeRae]

haha, you had already 'Run User Func' does, and I asked it anyways...so perhaps it was more of a 'negligent' question.

 

Thank you for all of your help @water.  Seriously, this forum is awesome, my local IT STILL hasn't responded to me...and I asked them on Monday.  I'm sure they'll follow up tomorrow afternoon just as I'm about to leave for the weekend... 

 

I do have one other question, that isn't really a big deal, since what I needed for the client I now have...but...for my own sake I was wondering: with the script that we have now, if I were to enter a much larger listing of numbers into the CSV file, and then only pull certain ones via the InputBox prompt would it only return results for the ones that I entered in the message box while still having a much larger repository of matched name/numbers in the CSV file?

[water]

Glad to be of service

The script processes the whole file, sorts it on column P (the ANI number) and when the number changes, calls function _NewCustomer. If it is one of the customers you want to pull via InputBox then it is added to the array. Else it is ignored.

[JadeRae]

Perfect, that makes it very useful further down the road as well.  (I'm sure in the future there will be more requests like this.)  

Now to run it on the BIG document!!

Once again, thank you for all of you help on this, you have made my job 1000 times easier

[water]

If performance is too slow then there are other ways to process the workbook:

• Use _Excel_RangeFind to only select the rows you need
• Use ADO and process the workbook like an SQL database