Find array elements

[JailDoctor]

I extract three arrays from an excel spreadsheet. They represent the Client's name, visit number and if the visit was canceled.

Canceled visits are entered as CAN, and active visits are entered as ADM in the spreadsheet.

I want to delete the canceled visits (not active visits) and their corresponding names, and number from the other 2 arrays.

So far I can find the canceled visits, but I can't figure out how to write in an error log that Mr. John Does' visit was canceled, and how to delete the elements in the other two arrays corresponding to the canceled visit.

This is the what I have so far.

CODE

#include"ExcelCom_UDF.au3"

#include"Array.au3"

#include"File.au3"

Global $XLFilePath = @TempDir & "\TestList.xls"

Global $oExcel, $XLArray, $XLArrayName, $XLArrayVisitStatus

;Create an errorLog

$sFile = @DesktopDir & "\ErrorLog.txt"

$oExcel = _ExcelBookOpen($XLFilePath)

$XLFilePath=@TempDir & ("\TestList.xls")

$XLArray =_ExcelReadArray($oExcel, 8, 5, 500, 1) ;corrected

$XLArrayName =_ExcelReadArray($oExcel, 8, 1, 500, 1)

$XLArrayVisitStatus =_ExcelReadArray($oExcel, 8, 7, 500, 1)

_ExcelBookClose($oExcel)

; Remove blank elements from Number

For $n = UBound($XLArray) - 1 To 0 Step -1

If StringStripWS($XLArray[$n], 8) = "" Then _ArrayDelete($XLArray, $n)

Next

; Remove blank elements from Name

For $n = UBound($XLArrayName) - 1 To 0 Step -1

If StringStripWS($XLArrayName[$n], 8) = "" Then _ArrayDelete($XLArrayName, $n)

Next

; Remove blank elements Visit

For $n = UBound($XLArrayVisitStatus) - 1 To 0 Step -1

If StringStripWS($XLArrayVisitStatus[$n], 8) = "" Then _ArrayDelete($XLArrayVisitStatus, $n)

Next

_ArrayDisplay( $XLArray, "Number")

_ArrayDisplay( $XLArrayName, "Name")

_ArrayDisplay( $XLArrayVisitStatus, "Status")

;find the canceled visits

$CanceledVisit = _ArrayFindAll ($XLArrayVisitStatus, "CAN")

_ArrayDisplay( $CanceledVisit, "Visit Status")

; Write first canceled visit to the error log

_FileWriteFromArray($sFile, $XLArray, 0)

Run("notepad.exe " & @DesktopDir & ("\ErrorLog.txt"))

Edited June 10, 2008 by JailDoctor

[Airwolf]

I would recommend using _ArrayDelete(); just delete the corresponding entries in all arrays for the user. If the data is all in a single row for each user, why not use a multi-dimensional array? It will make it easier to delete an entire row.

As for logging, just log the deletion in a .txt file with FileOpen() and FileWriteLine().

[Kerros]

Would it be possible to get small sample of how the data is entered into the Excel sheet. Would be easier to help.

Another option would be, depending on how the information is entered into Excel is to use the either delete row or column options

_ExcelRowDelete

_ExcelColumnDelete

[PsaltyDS]

I extract three arrays from an excel spreadsheet. They represent the Client's name, visit number and if the visit was canceled.

Canceled visits are entered as CAN, and active visits are entered as ADM in the spreadsheet.

I want to delete the canceled visits (not active visits) and their corresponding names, and number from the other 2 arrays.

So far I can find the canceled visits, but I can't figure out how to write in an error log that Mr. John Does' visit was canceled, and how to delete the elements in the other two arrays corresponding to the canceled visit.

This is the what I have so far.

CODE

#include"ExcelCom_UDF.au3"

#include"Array.au3"

#include"File.au3"

Global $XLFilePath = @TempDir & "\TestList.xls"

Global $oExcel, $XLArray, $XLArrayName, $XLArrayVisitStatus

;Create an errorLog

$sFile = @DesktopDir & "\ErrorLog.txt"

$oExcel = _ExcelBookOpen($XLFilePath)

$XLFilePath=@TempDir & ("\TestList.xls")

$XLArrayName =_ExcelReadArray($oExcel, 8, 1, 500, 1)

$XLArrayVisitStatus =_ExcelReadArray($oExcel, 8, 7, 500, 1)

_ExcelBookClose($oExcel)

; Remove blank elements from Number

For $n = UBound($XLArray) - 1 To 0 Step -1

If StringStripWS($XLArray[$n], 8) = "" Then _ArrayDelete($XLArray, $n)

Next

; Remove blank elements from Name

For $n = UBound($XLArrayName) - 1 To 0 Step -1

If StringStripWS($XLArrayName[$n], 8) = "" Then _ArrayDelete($XLArrayName, $n)

Next

; Remove blank elements Visit

For $n = UBound($XLArrayVisitStatus) - 1 To 0 Step -1

If StringStripWS($XLArrayVisitStatus[$n], 8) = "" Then _ArrayDelete($XLArrayVisitStatus, $n)

Next

_ArrayDisplay( $XLArray, "Number")

_ArrayDisplay( $XLArrayName, "Name")

_ArrayDisplay( $XLArrayVisitStatus, "Status")

;find the canceled visits

$CanceledVisit = _ArrayFindAll ($XLArrayVisitStatus, "CAN")

_ArrayDisplay( $CanceledVisit, "Visit Status")

; Write first canceled visit to the error log

_FileWriteFromArray($sFile, $XLArray, 0)

Run("notepad.exe " & @DesktopDir & ("\ErrorLog.txt"))

I don't see anything setting data into $XLArray, but assuming something not shown did:

You get an array of matching indexes in $CanceledVisit, so walk it BACKWARDS (i.e. For $i = Ubound($CanceledVisit) - 1 To 0 Step -1) and _ArrayDelete() them.

[JailDoctor]

Clt Name Number Status

John Doe 123456 ADM

Jane Sam 789100 CAN

Billy Bob 223344 ADM

John Doe and Billy Bob have active visits (ADM), Jane canceled her visit (CAN).

I want to let the operator know that Jane canceled her visit and hence will not be processed and delete the item from my arrays. So a notepad will run with the list of all the canceled visits.

Edited June 19, 2008 by JailDoctor

[JailDoctor]

I don't see anything setting data into $XLArray, but assuming something not shown did:

Thanks, I removed the line with all the other comments and "tests" for clarity.

I fixed the post.

$XLArray =_ExcelReadArray($oExcel, 8, 5, 500, 1)

[Kerros]

This is what I think you are looking for, correct me if I'm wrong.

I commented out some of your code for my trial.

expandcollapse popup

#include"ExcelCom_UDF.au3"
#include"Array.au3"
#include"File.au3"

Global $XLFilePath = @TempDir & "\TestList.xls"
Global $oExcel, $XLArray, $XLArrayName, $XLArrayVisitStatus
#cs
;Create an errorLog
$sFile = @DesktopDir & "\ErrorLog.txt"
$oExcel = _ExcelBookOpen($XLFilePath)
$XLFilePath=@TempDir & ("\TestList.xls")
$XLArray =_ExcelReadArray($oExcel, 8, 5, 500, 1);corrected
$XLArrayName =_ExcelReadArray($oExcel, 8, 1, 500, 1)
$XLArrayVisitStatus =_ExcelReadArray($oExcel, 8, 7, 500, 1)
_ExcelBookClose($oExcel)
#CE
Local $XLArrayName[3] = ['John Doe','Jane Sam','Billy Bob']
Local $XLArrayVisitStatus[3] = ['ADM','CAN','ADM']
Local $XLArray[3] = ['123456','789100','223344']

; Remove blank elements from Number
For $n = UBound($XLArray) - 1 To 0 Step -1
If StringStripWS($XLArray[$n], 8) = "" Then _ArrayDelete($XLArray, $n)
Next
; Remove blank elements from Name
For $n = UBound($XLArrayName) - 1 To 0 Step -1
If StringStripWS($XLArrayName[$n], 8) = "" Then _ArrayDelete($XLArrayName, $n)
Next
; Remove blank elements Visit
For $n = UBound($XLArrayVisitStatus) - 1 To 0 Step -1
If StringStripWS($XLArrayVisitStatus[$n], 8) = "" Then _ArrayDelete($XLArrayVisitStatus, $n)
Next

;~ _ArrayDisplay( $XLArray, "Number")
;~ _ArrayDisplay( $XLArrayName, "Name")
;~ _ArrayDisplay( $XLArrayVisitStatus, "Status")

;find the canceled visits
$CanceledVisit = _ArrayFindAll ($XLArrayVisitStatus, "CAN")
;~ _ArrayDisplay( $CanceledVisit, "Visit Status")
For $icc = 0 To UBound($CanceledVisit)-1
ConsoleWrite($XLArrayName[$CanceledVisit[$icc]]&' Canceled visit number: '&$XLArray[$CanceledVisit[$icc]]&@CRLF)
; Write canceled visit to the error log
FileWriteLine($sFile,$XLArrayName[$CanceledVisit[$icc]]&' Canceled visit number: '&$XLArray[$CanceledVisit[$icc]])
Next

Run("notepad.exe " & @DesktopDir & ("\ErrorLog.txt"))

now this will only work as long as all three arrays stay in sync. I would be hesitant to assume that would always be the case.

[JailDoctor]

It worked pretty well, although kind of slow.

$CanceledVisit = _ArrayFindAll ($XLArrayVisitStatus, "CAN")

I was looking for searching for not equal to "ADM" to speed things up (or for future new codes) but <> does not work for strings.

I tried all combinations of operators but it didn't work.

Any suggestions?

Thanks Kerros, your idea got me learning about multidimensional arrays, but it is difficult to find much info about using them. It took me a while to populate the arrays from Excel, but I finally did it.

[Kerros]

Just thinking about the speed of your script, I'm wondering if it's not the read from excel is that is taking so long.

you could put timers around your current code, and then try this this as well.

As it's only going to the last used cell it should be faster, but I can't promise anything.

;Create an errorLog
$sFile = @DesktopDir & "\ErrorLog.txt"
$oExcel = _ExcelBookOpen($XLFilePath)
$XLFilePath=@TempDir & ("\TestList.xls")
$XLLastCell = _ExcelSheetUsedRangeGet($oExcel, _ExcelSheetNameGet($oExcel))
$XLArray =_ExcelReadArray($oExcel, 8, 5, $XLLastCell, 1);corrected
$XLArrayName =_ExcelReadArray($oExcel, 8, 1, $XLLastCell, 1)
$XLArrayVisitStatus =_ExcelReadArray($oExcel, 8, 7, $XLLastCell, 1)
_ExcelBookClose($oExcel)

Edit: Fixed code tages

Edited June 11, 2008 by Kerros

[JailDoctor]

$XLLastCell = _ExcelSheetUsedRangeGet($oExcel, _ExcelSheetNameGet($oExcel))

$XLArray =_ExcelReadArray($oExcel, 8, 5, $XLLastCell, 1);corrected

$XLArrayName =_ExcelReadArray($oExcel, 8, 1, $XLLastCell, 1)

$XLArrayVisitStatus =_ExcelReadArray($oExcel, 8, 7, $XLLastCell, 1)

_ExcelBookClose($oExcel)

I got this error.

H:\My Documents\ExcelCom_UDF.au3 (606) : ==> Array variable subscript badly formatted.:

Local $aArray[$iNumCells + $iIndexBase]

Local $aArray[^ ERROR

->13:39:13 AutoIT3.exe ended.rc:1

It seems ExcelCom_UDF.au3 doesn't understand to look up to the last cell?

CODE

$oExcel = _ExcelBookOpen($XLFilePath)

$XLFilePath=@TempDir & ("\My test.xls")

$XLLastCell = _ExcelSheetUsedRangeGet($oExcel, _ExcelSheetNameGet($oExcel))

$XLArray =_ExcelReadArray($oExcel, 8, 5, 500, 1)

$XLArrayName =_ExcelReadArray($oExcel, 8, 1, 500, 1)

$XLArrayVisitStatus =_ExcelReadArray($oExcel, 8, 7, 500, 1)

_ExcelBookClose($oExcel)

Edited June 11, 2008 by JailDoctor

[Kerros]

Sorry that was my fault, _ExcelSheetUsedRangeGet returns an array not a value. That's the problem with untested code, sometime it still doesn't work.

Depending on how your information is formated you need the $XLLastCell[2] or $XLLastCell[3]

$oExcel = _ExcelBookOpen($XLFilePath)
$XLFilePath=@TempDir & ("\TestList.xls")
$XLLastCell = _ExcelSheetUsedRangeGet($oExcel, _ExcelSheetNameGet($oExcel))
_ArrayDisplay($XLLastCell)
$XLArray =_ExcelReadArray($oExcel, 8, 5, $XLLastCell[3], 1);corrected
$XLArrayName =_ExcelReadArray($oExcel, 8, 1, $XLLastCell[3], 1)
$XLArrayVisitStatus =_ExcelReadArray($oExcel, 8, 7, $XLLastCell[3], 1)
_ExcelBookClose($oExcel)
_ArrayDisplay($XLArray)
_ArrayDisplay($XLArrayName)
_ArrayDisplay($XLArrayVisitStatus)

[JailDoctor]

$XLArray =_ExcelReadArray($oExcel, 8, 5, $XLLastCell[3], 1);corrected

$XLArrayName =_ExcelReadArray($oExcel, 8, 1, $XLLastCell[3], 1)

$XLArrayVisitStatus =_ExcelReadArray($oExcel, 8, 7, $XLLastCell[3], 1)

By looking at the _ExcelReadArray line, describing the rows and columns as row 8, colum 5, then 8,1 and then 8,7 it follows the format should be $XLLastCell[1] -The last cell used, in R1C1 format. But I get this error.

H:\My Documents\multidimension.au3 (25) : ==> Subscript used with non-Array variable.:

$XLArray =_ExcelReadArray($oExcel, 8, 5, $XLLastCell[1], 1)

$XLArray =_ExcelReadArray($oExcel, 8, 5, $XLLastCell^ ERROR

->15:34:52 AutoIT3.exe ended.rc:1

All other possibilities, [0] for A1, [2] for column, and [3] for row caused the very same error.

Any ideas?

[JailDoctor]

By looking at the _ExcelReadArray line, describing the rows and columns as row 8, colum 5, then 8,1 and then 8,7 it follows the format should be $XLLastCell[1] -The last cell used, in R1C1 format. But I get this error.

H:\My Documents\multidimension.au3 (25) : ==> Subscript used with non-Array variable.:

$XLArray =_ExcelReadArray($oExcel, 8, 5, $XLLastCell[1], 1)

$XLArray =_ExcelReadArray($oExcel, 8, 5, $XLLastCell^ ERROR

->15:34:52 AutoIT3.exe ended.rc:1

All other possibilities, [0] for A1, [2] for column, and [3] for row caused the very same error.

Any ideas?

I figured out that H:\My Documents\multidimension.au3 (25) : ==> Subscript used with non-Array variable.: shows up whenever there is an error. So if I'm looking for a value that doesn't exist in the array , the error is reported as H:\My Documents\multidimension.au3 (25) : ==> Subscript used with non-Array variable.:

So I spent all the time looking for answers in the code when I needed to check that the values were existant in the array.

I am implementing a routine for dealing with the not found @error (I believe -1) and will keep you posted.