Jump to content
Sign in to follow this  
JailDoctor

Find array elements

Recommended Posts

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 by JailDoctor

Share this post


Link to post
Share on other sites
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().


Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

Share this post


Link to post
Share on other sites
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


Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Share this post


Link to post
Share on other sites
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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
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 by JailDoctor

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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.

#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.


Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by Kerros

Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Share this post


Link to post
Share on other sites
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 by JailDoctor

Share this post


Link to post
Share on other sites
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)

Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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. :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×