Jump to content
Sign in to follow this  
Althalus

Delete multidimensional array row based on criteria

Recommended Posts

Althalus

I am trying to delete rows from my multidimensional array.  With my code I am not getting any errors but it is obvious that it is not finishing.  Would anyone be able to give me some pointers on what I am doing wrong between lines 21 to 28?

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


;-----------------------------------------------------------------------------------------------------------------------------
; Open/Read/Manipulate the Excel File
;-----------------------------------------------------------------------------------------------------------------------------
Global $excelFile = FileOpenDialog("Open File That Is To The Clearinghouse", @ScriptDir & "\", "Excel files (*.xlsx)", 1)

; Open User Specified Excel file
Global $openExcelFile = _ExcelBookOpen($excelFile,1,True)

; Message to inform user something is happening...
MsgBox(0, "Reading File", "Reading File. Please wait.", 2)

; Copy Excel file to Array - starting on row 2 to avoid the column headers from being in array[1]
Global $excelArray = _ExcelReadSheetToArray($openExcelFile,2)

; $i is the array's rows and it will increase by 1 each time the loop completes until the array ends
For $i = 0 to UBound($excelArray)
    ; If the Enrollment Status Column[16] is Null or the Graduated Column[17] is 'Y' then delete the row
    If $excelArray[$i][16] = "" Then
    _ArrayDelete($excelArray, $i)
    ElseIf $excelArray[$i][17] = "Y" Then
    _ArrayDelete($excelArray, $i)
    EndIf
Next

; Display Array - Delete after finishing the Array section
_ArrayDisplay($excelArray, "Display File's Array")

; Close Excel file without saving and with and Excel message alerts disabled
_ExcelBookClose($openExcelFile,0,0)

Exit

Share this post


Link to post
Share on other sites
BrewManNH

Try looping through the array in reverse, use this.
 

For $i = UBound($excelArray) - 1 to 0 Step -1

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
Althalus

Thank you BrewManNH, that worked.  Would you be able to tell me why going backwards through the array works, instead of forwards?

Share this post


Link to post
Share on other sites
BrewManNH

Once you delete 1 row from your array, every row after it will be decreased by 1, so you're not deleting the rows you think you are.

  • Delete row 3
  • Row 4 is now the new row 3
  • Delete row 4, which is the former row 5
  • You've now deleted 5 instead of 4 and 4 is still there in a new slot.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
Althalus

Makes sense, thank you again.

Share this post


Link to post
Share on other sites
Althalus

I thought about opening another request for this but as it is still on the _ArrayDelete function I decided it might be best to continue on this post.  I have finished my program, except for a section where I am trying to delete rows for a 2D array if one of its columns matches one of the values in a 1D array.  I have removed the rest of the program after the error, as it is working correctly.  Lines 56 - 62 is my latest attempt to delete it with a nested for loop, it does not return errors but the rows are not deleted in the array.

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

;-----------------------------------------------------------------------------------------------------------------------------
; Open/Read/Manipulate the Excel File
;-----------------------------------------------------------------------------------------------------------------------------
Global $excelFile = FileOpenDialog("Open File That Is From The Clearinghouse", @ScriptDir & "\", "Excel files (*.xlsx)", 1)

; Open User Specified Excel file
;--------------------------------------------------------------------------------------------------------------------------------------------
Global $openExcelFile = _ExcelBookOpen($excelFile,1,True)

; Message to inform user something is happening...
MsgBox(0, "Reading File", "Reading File. Please wait.", 5)

; Copy Excel file to Array - starting on row 2 to avoid the column headers from being in array[1]
;--------------------------------------------------------------------------------------------------------------------------------------------
Global $excelArray = _ExcelReadSheetToArray($openExcelFile,2)

; Close Excel file without saving and with and Excel message alerts disabled
;--------------------------------------------------------------------------------------------------------------------------------------------
_ExcelBookClose($openExcelFile,0,0)

; If the Enrollment Status Column[16] is Null or the Graduated Column[17] is 'Y' then add the unique Idenifier to $aArray
;--------------------------------------------------------------------------------------------------------------------------------------------
Global $aArray[UBound($excelArray)]

For $i = UBound($excelArray) - 1 to 0 Step -1
    If $excelArray[$i][16] <> " " Then
    _ArrayAdd($aArray, $excelArray[$i][6])
    ElseIf $excelArray[$i][17] = "Y" Then
    _ArrayAdd($aArray, $excelArray[$i][6])
    EndIf
Next

_ArrayDisplay($aArray, "$aArray")

; Bring over the unique values in $aArray to $uniqueIdArray and delete row 0(which will be replace with an empty row, which cannot be gotton rid of) and any empty rows.
;--------------------------------------------------------------------------------------------------------------------------------------------
Global $aUniqueIdArray = _ArrayUnique($aArray)

For $i = UBound($aUniqueIdArray) - 1 to 0 Step -1
    If $i = 0 Then
    _ArrayDelete($aUniqueIdArray, $i)
    ElseIf $aUniqueIdArray[$i] = " " Then
    _ArrayDelete($aUniqueIdArray, $i)
    EndIf
Next

_ArrayDisplay($aUniqueIdArray, "$aUniqueIdArray")


; Delete rows from $excelArray, which has the Unique Ids that are stored in $uniqueIdArray
;--------------------------------------------------------------------------------------------------------------------------------------------
For $i = UBound($excelArray,0) - 1 to 0 Step -1
    For $x = UBound($aUniqueIdArray) - 1 to 0 Step -1
        If $aUniqueIdArray[$x] == $excelArray[$i][6] Then
        _ArrayDelete($excelArray, $i)
        EndIf
    Next
Next

#cs
; search Banner id's for ADP id's
For $i = UBound($excelArray,0) - 1 to 0 Step -1
    ; if not in Banner then add to array
    If _ArraySearch($excelArray[$i][6], $aUniqueIdArray[$i]) = -1 Then
    _ArrayDelete($excelArray, $i)
    EndIf
Next
#ce

_ArrayDisplay($excelArray, "$excelArray")

Share this post


Link to post
Share on other sites
BrewManNH

Don't use UBound with 0 in the Dimension parameter in your $I loop, that only gives you the array's subitems ("columns"), and then you're using it in the $item ("row") location in the search routine. Use the default for Ubound, that gives you the row count.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
jdelaney

Ubound Dimension paramater note from helpfile: [optional] Which dimension of a multi-dimensioned array to report the size of. Default is 1, which is the first dimension. If this parameter is 0, the number of subscripts in the array is returned.

Subscripts are the count of brackets on the array.

Small example:

Local $array1[10][8][6][4][2]
ConsoleWrite("There are [" & UBound($array1,0) & "] subscripts in the array" & @CRLF)
For $i = 1 To UBound($array1,0)
    ConsoleWrite("Dimension [" & $i & "] includes a ubound of [" & UBound($array1,$i) & "]" & @CRLF)
Next

output:

There are [5] subscripts in the array
Dimension [1] includes a ubound of [10]
Dimension [2] includes a ubound of [8]
Dimension [3] includes a ubound of [6]
Dimension [4] includes a ubound of [4]
Dimension [5] includes a ubound of [2]

Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites
Althalus

I had tried that originally, it returns an error of:

Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

If $aUniqueIdArray[$x] == $excelArray[$i][6] Then
If $aUniqueIdArray[$x] == ^ ERROR
 
For $i = UBound($excelArray) - 1 to 0 Step -1
    For $x = UBound($aUniqueIdArray) - 1 to 0 Step -1
        If $aUniqueIdArray[$x] == $excelArray[$i][6] Then
        _ArrayDelete($excelArray, $i)
        EndIf
    Next
Next
 
I have the search Array section commented off, as it did not work for me to delete either.

Share this post


Link to post
Share on other sites
jdelaney

Looks fine to me.

You don't need to reverse step through the inner loop.

Try adding an _arraydisplay of the $excelArray, just prior to the outer loop.  Make sure it's what you expect it to be.


IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites
BrewManNH

You need to exit the $x loop (ExitLoop) after you've deleted the array row, otherwise you'll be searching the wrong row again.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
Althalus

I think the ExitLoop did it.  Thank you both very much, without you I do not believe I could have finished this project.

; Delete rows from $excelArray, which has the Unique Ids that are stored in $uniqueIdArray
;--------------------------------------------------------------------------------------------------------------------------------------------
For $i = UBound($excelArray,1) - 1 to 0 Step -1
    For $x = UBound($aUniqueIdArray) - 1 to 0 Step -1
        If $aUniqueIdArray[$x] == $excelArray[$i][6] Then
        _ArrayDelete($excelArray, $i)
        ExitLoop
        EndIf
    Next
Next

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  

×