Sign in to follow this  
Followers 0
kor

how to compare very large arrays and ReDim based on comparisons?

34 posts in this topic

Below is a sample of what I'm looking for. I have 2 arrays that are very large (several thousand rows) and I need to compare values between them.

I have an input array ($aAD) and an Array that I'm comparing it to ($aGenesis)

$aGenesis would be treated as the "correct" information. So if a row ($i) has any value that is different to a corresponding row in $aGenesis it should be loaded into the new array and an appropriate "add, delete, move" should be appended to column 6 in the new array.

If a row with username X is in $aAD but not in $aGenesis that would be action "delete"

If a row with username X has a different value for any column with the same corresponding username X in $aGenesis that would be action "change/update"

If a row with username X in $aGenesis does not exist in $sAD that would be a "create"

I have no problem with the code after I have all the information loaded into $aFinal. I already have code built if the actions are add, delete, change, move, etc.

What I've never had to do before is compare two arrays .. but not only compare them.. look at every single value in them and compare with every single value in the other array.

#include <Array.au3>

Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"], ["John", "Smith", "3456", "Location1", "9th"], _
["Jason", "Jackson", "5678", "Location4", "4th"], ["Chris", "Black", "4445", "Location2", "2nd"], ["Lee", "Chan", "9876", "Location1", "2nd"]]

Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"], ["John", "Smith", "3456", "Location1", "9th"], _
["Jason", "Jackson", "5678", "Location1", "2nd"]]

_ArrayDisplay($aAD)
_ArrayDisplay($aGenesis)

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him

Global $aFinal[3][6] = [["Jason", "Jackson", "5678", "Location1", "2nd", "Move"], ["Chris", "Black", "4445", "Location2", "2nd", "Delete"], _
["Lee", "Chan", "9876", "Location1", "2nd", "Delete"]]

_ArrayDisplay($aFinal)

Share this post


Link to post
Share on other sites



And the question is?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I think he wants code written for him. Search _ArrayDiff.


_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 04/09/2015

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

And the question is?

the question is the title of the thread.

how to compare very large arrays and ReDim based on comparisons?

EDIT:

is _ArrayDiff a custom udf? It isn't in the help file.

Edited by kor

Share this post


Link to post
Share on other sites

A first (slow) example could look like:

#include <Array.au3>

Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location4", "4th"],["Chris", "Black", "4445", "Location2", "2nd"],["Lee", "Chan", "9876", "Location1", "2nd"]]

Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location1", "2nd"]]

_ArrayDisplay($aAD)
_ArrayDisplay($aGenesis)

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him
$iFinal = 0
Global $aFinal[1][6]
For $iAD = 0 To UBound($aAD, 1) - 1
    $bFound = False
    For $iGenesis = 0 To UBound($aGenesis, 1) - 1
        If $aAD[$iAD][0] = $aGenesis[$iGenesis][0] And $aAD[$iAD][1] = $aGenesis[$iGenesis][1] Then  ; Name found in $aGenesis
            $bFound = True
            If $aAD[$iAD][2] <> $aGenesis[$iGenesis][2] Or $aAD[$iAD][3] <> $aGenesis[$iGenesis][3] Or $aAD[$iAD][4] <> $aGenesis[$iGenesis][4] Then ; Content different
                If $iFinal > 0 Then ReDim $aFinal[UBound($aFinal, 1)+1][6]
                $aFinal[$iFinal][0] = $aGenesis[$iGenesis][0]
                $aFinal[$iFinal][1] = $aGenesis[$iGenesis][1]
                $aFinal[$iFinal][2] = $aGenesis[$iGenesis][2]
                $aFinal[$iFinal][3] = $aGenesis[$iGenesis][3]
                $aFinal[$iFinal][4] = $aGenesis[$iGenesis][4]
                $aFinal[$iFinal][5] = "Move"
                $iFinal = $iFinal + 1
            Endif
        EndIf
    Next
    If $bFound = False Then ; Name not found
        If $iFinal > 0 Then ReDim $aFinal[UBound($aFinal, 1)+1][6]
        $aFinal[$iFinal][0] = $aAD[$iAD][0]
        $aFinal[$iFinal][1] = $aAD[$iAD][1]
        $aFinal[$iFinal][2] = $aAD[$iAD][2]
        $aFinal[$iFinal][3] = $aAD[$iAD][3]
        $aFinal[$iFinal][4] = $aAD[$iAD][4]
        $aFinal[$iFinal][5] = "Delete"
        $iFinal = $iFinal + 1
    Endif
Next

;Global $aFinal[3][6] = [["Jason", "Jackson", "5678", "Location1", "2nd", "Move"],["Chris", "Black", "4445", "Location2", "2nd", "Delete"], _
;        ["Lee", "Chan", "9876", "Location1", "2nd", "Delete"]]

_ArrayDisplay($aFinal)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

And here the version without "ReDim":

#include <Array.au3>

Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location4", "4th"],["Chris", "Black", "4445", "Location2", "2nd"],["Lee", "Chan", "9876", "Location1", "2nd"]]

Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location1", "2nd"]]

_ArrayDisplay($aAD)
_ArrayDisplay($aGenesis)

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him
$iFinal = 0
Global $aFinal[UBound($aAD, 1) - 1][6]
For $iAD = 0 To UBound($aAD, 1) - 1
    $bFound = False
    For $iGenesis = 0 To UBound($aGenesis, 1) - 1
        If $aAD[$iAD][0] = $aGenesis[$iGenesis][0] And $aAD[$iAD][1] = $aGenesis[$iGenesis][1] Then  ; Name found in $aGenesis
            $bFound = True
            If $aAD[$iAD][2] <> $aGenesis[$iGenesis][2] Or $aAD[$iAD][3] <> $aGenesis[$iGenesis][3] Or $aAD[$iAD][4] <> $aGenesis[$iGenesis][4] Then ; Content different
                $aFinal[$iFinal][0] = $aGenesis[$iGenesis][0]
                $aFinal[$iFinal][1] = $aGenesis[$iGenesis][1]
                $aFinal[$iFinal][2] = $aGenesis[$iGenesis][2]
                $aFinal[$iFinal][3] = $aGenesis[$iGenesis][3]
                $aFinal[$iFinal][4] = $aGenesis[$iGenesis][4]
                $aFinal[$iFinal][5] = "Move"
                $iFinal = $iFinal + 1
            Endif
        EndIf
    Next
    If $bFound = False Then ; Name not found
        $aFinal[$iFinal][0] = $aAD[$iAD][0]
        $aFinal[$iFinal][1] = $aAD[$iAD][1]
        $aFinal[$iFinal][2] = $aAD[$iAD][2]
        $aFinal[$iFinal][3] = $aAD[$iAD][3]
        $aFinal[$iFinal][4] = $aAD[$iAD][4]
        $aFinal[$iFinal][5] = "Delete"
        $iFinal = $iFinal + 1
    Endif
Next
ReDim $aFinal[$iFinal][6]

_ArrayDisplay($aFinal)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@water, I'll load up your code and see what it does.

I did find this :

but the inputs can only be 1D arrays.

Share this post


Link to post
Share on other sites

It sounds like you need to use a different method of data storage/retrieval. You may want to investigate SQL and do things with queries. Or even some form of balanced tree structure where the data is retrieved in fewer operations. Brute force permutation is bound to be slowest.

Share this post


Link to post
Share on other sites

If sorting the arrays isn't an issue, then a binary search of the array would be the fastest way to check one array against another. You'd loop through one array, and search for the item in the other using this modified version of _ArrayBinarySearch

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayBinarySearch2D
; Description ...: Uses the binary search algorithm to search through a 1 or 2-dimensional array.
; Syntax.........: _ArrayBinarySearch2D(Const ByRef $avArray, $vValue[, $iColumn = 0[, $iStart = 0[, $iEnd = 0]]])
; Parameters ....: $avArray - Array to search
;                 $vValue  - Value to find
;                 $iColumn - [optional] Which column to search on [Default = First (0) column]
;                 $iStart  - [optional] Index of array to start searching at [Default = start of array]
;                 $iEnd    - [optional] Index of array to stop searching at [Default = end of array]
; Return values .: Success - Index that value was found at
;                 Failure - -1, sets @error to:
;                 |1 - $avArray is not an array
;                 |2 - $vValue outside of array's min/max values
;                 |3 - $vValue was not found in array
;                 |4 - $iStart is greater than $iEnd
;      |5 - $iColumn is greater than actual number of columns
;                 |6 - $avArray has too many dimensions
; Author ........: Jos van der Zande <jdeb at autoitscript dot com>
; Modified.......: Ultima - added $iEnd as parameter, code cleanup
; Modified.......: BrewManNH - added ability to search a 2D array
; Remarks .......: When performing a binary search on an array of items, the contents of the column being searched MUST be
;                 sorted before the search is done, otherwise undefined results will be returned.
; Related .......: _ArrayFindAll, _ArraySearch, _ArrayBinarySearch
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ArrayBinarySearch2D(Const ByRef $avArray, $vValue, $iColumn = 0, $iStart = 0, $iEnd = 0)
 If UBound($avArray, 0) > 2 Then Return SetError(6, 0, -1)
 Local $i2D = False
 If UBound($avArray, 0) > 1 Then
  $i2D = True
 EndIf
 If UBound($avArray, 2) < $iColumn Then Return SetError(5, 0, -1)
 If Not IsArray($avArray) Then Return SetError(1, 0, -1)
 Local $iUBound = UBound($avArray) - 1
 ; Bounds checking
 If $iEnd < 1 Or $iEnd > $iUBound Then $iEnd = $iUBound
 If $iStart < 0 Then $iStart = 0
 If $iStart > $iEnd Then Return SetError(4, 0, -1)
 Local $iMid = Int(($iEnd + $iStart) / 2)
 If Not $i2D Then
  If $avArray[$iStart] > $vValue Or $avArray[$iEnd] < $vValue Then Return SetError(2, 0, -1)
  ; Search
  While $iStart <= $iMid And $vValue <> $avArray[$iMid]
   If $vValue < $avArray[$iMid] Then
    $iEnd = $iMid - 1
   Else
    $iStart = $iMid + 1
   EndIf
   $iMid = Int(($iEnd + $iStart) / 2)
  WEnd
  If $iStart > $iEnd Then Return SetError(3, 0, -1) ; Entry not found
  Return $iMid
 Else
  If $avArray[$iStart][$iColumn] > $vValue Or $avArray[$iEnd][$iColumn] < $vValue Then Return SetError(2, 0, -1)
  ; Search
  While $iStart <= $iMid And $vValue <> $avArray[$iMid][$iColumn]
   If $vValue < $avArray[$iMid][$iColumn] Then
    $iEnd = $iMid - 1
   Else
    $iStart = $iMid + 1
   EndIf
   $iMid = Int(($iEnd + $iStart) / 2)
  WEnd
  If $iStart > $iEnd Then Return SetError(3, 0, -1) ; Entry not found
  Return $iMid
 EndIf
EndFunc   ;==>_ArrayBinarySearch2D

I use this function in a media player to search for duplicate media files when adding a folder to the player, even when the music list is over 4000 entries long and the array I'm looking to compare the known list against is 4000+ entries, this search still takes less than a second or 2, and I only use ReDim once when it's done comparing.


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

#10 ·  Posted (edited)

Here's my approach...

Edit 1: Upsa, does not meet the request :), will re-post in a couple of minutes...

Edit 2: This should be better...

#include <Array.au3>

Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location4", "4th"],["Chris", "Black", "4445", "Location2", "2nd"],["Lee", "Chan", "9876", "Location1", "2nd"]]

Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
        ["Jason", "Jackson", "5678", "Location1", "2nd"]]

; _ArrayDisplay($aAD)
; _ArrayDisplay($aGenesis)

Global $oDict_Compare = ObjCreate('Scripting.Dictionary')
$oDict_Compare.CompareMode = 1
Global $i_Results = 0, $s_Value, $a_Value
Global $aFinal[500][6]

For $i = 0 To UBound($aGenesis) - 1
    $oDict_Compare.Add($aGenesis[$i][0] & "|||" & $aGenesis[$i][1], $aGenesis[$i][2] & "|||" & $aGenesis[$i][3] & "|||" & $aGenesis[$i][4])
Next


For $i = 0 To UBound($aAD) - 1
    If $oDict_Compare.Exists($aAD[$i][0] & "|||" & $aAD[$i][1]) Then
        $s_Value = $oDict_Compare.Item($aAD[$i][0] & "|||" & $aAD[$i][1])
        If $s_Value <> $aAD[$i][2] & "|||" & $aAD[$i][3] & "|||" & $aAD[$i][4] Then ; move
            $i_Results += 1
            If Mod($i_Results, 500) Then ReDim $aFinal[UBound($aFinal) + 500][6]
            $a_Value = StringSplit($s_Value, "|||", 1)
            $aFinal[$i_Results - 1][0] = $aAD[$i][0]
            $aFinal[$i_Results - 1][1] = $aAD[$i][1]
            $aFinal[$i_Results - 1][2] = $a_Value[1]
            $aFinal[$i_Results - 1][3] = $a_Value[2]
            $aFinal[$i_Results - 1][4] = $a_Value[3]
            $aFinal[$i_Results - 1][5] = "Move"
        EndIf
    Else ; Delete
        $i_Results += 1
        If Mod($i_Results, 500) Then ReDim $aFinal[UBound($aFinal) + 500][6]
        $aFinal[$i_Results - 1][0] = $aAD[$i][0]
        $aFinal[$i_Results - 1][1] = $aAD[$i][1]
        $aFinal[$i_Results - 1][2] = $aAD[$i][2]
        $aFinal[$i_Results - 1][3] = $aAD[$i][3]
        $aFinal[$i_Results - 1][4] = $aAD[$i][4]
        $aFinal[$i_Results - 1][5] = "Delete"
    EndIf
Next

ReDim $aFinal[$i_Results][6]

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him

#cs
    Global $aFinal[3][6] = [["Jason", "Jackson", "5678", "Location1", "2nd", "Move"],["Chris", "Black", "4445", "Location2", "2nd", "Delete"], _
    ["Lee", "Chan", "9876", "Location1", "2nd", "Delete"]]
#ce

_ArrayDisplay($aFinal)
Edited by KaFu

Share this post


Link to post
Share on other sites

Google showed but clicking the link did not get a response. A tree implementation will be slower loading, but the item retrieveal should be much faster.

Share this post


Link to post
Share on other sites

@water, your code "works" but seems to break down when I feed it information other than the sample array in the code.

I've attached a sample output in CSV of my AD and Genesis outputs.

If everything would work right with your code then rows 0 - 24 would be ignores, rows 25 - 28 would be moves, row 29 would be a delete, and rows 30 and 31 would be an adds.

@Kafu, I tried your code and it took over a minute with my CPU pegged and it still didn't complete so I scraped it. (i fed it my actual data at 3000+ rows)

genesis.txt

ad.txt

expected output.txt

Share this post


Link to post
Share on other sites

How do you fill the arrays with the specified data?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

How do you fill the arrays with the specified data?

during the loops? Or are you talking about in general.. IE where are the output files being generated from?

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

Another simpler alternative.. if the UserID is guaranteed to be unique, or can be made so, you can use Scripting.Dictionary and just look up the User's data with the UserID as the key. Can't get much simpler. Also it's likely to be more sophisticated lookup than looping through the entire data set for each lookup.

Here's a basic test app to show how to create it, access items by key, and delete it

$aList = ObjCreate("Scripting.Dictionary")
If @error Then
    MsgBox(0,@ScriptName,"ObjCreate failed")
    Exit
EndIf

$aList("somestuff") = "somestuff to store in array"
$aList("morestuff") = "morestuff to store in array"

MsgBox(0,@ScriptName,$aList("somestuff"))
MsgBox(0,@ScriptName,$aList("morestuff"))
$aList.RemoveAll()
$aList = 0
Edited by MilesAhead

Share this post


Link to post
Share on other sites

At the start of the script you need to fill arrays $aAD and $aGenesis with the example data you posted above. Do you read and parse the files or do you enter the data manually?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

Another simpler alternative.. if the UserID is guaranteed to be unique, or can be made so, you can use Scripting.Dictionary and just look up the User's data with the UserID as the key. Can't get much simpler. Also it's likely to be more sophisticated lookup than looping through the entire data set for each lookup.

The userid is unique.

However KaFu's code utilized Scripting.Dictionary and when I fed it my 3000+ row array his code didn't work. just pegged my cpu.

@water, the Genesis array is pulled in using "FileRead" then formatted to get everything how it appears in my genesis.txt

The AD file is actually pulled from Active Directory on the fly.

Here are my two functions for formatting both sets of data.

Func _FormatGenesis("C:genesis.txt")
    $sInput = FileRead($sInput) ; read csv file
    If @error = 1 Then ConsoleWrite("ERROR - Unable to open input file" & @CR)
    Local $iRows = StringSplit($sInput, @CR) ; make each line a new row
    Global $aRaw[$iRows[0] + 1][5] ; create raw array with dimensions based on rows
    For $i = 1 To $iRows[0]
        Local $aTemp = StringSplit($iRows[$i], ",", 2) ; create temporary array to split all values into
        For $n = 0 To UBound($aTemp) - 1
            $aRaw[$i][$n] = $aTemp[$n] ; populate array with split data
        Next
    Next
    ;_ArrayDelete($aRaw, UBound($aRaw) - 1) ; delete the last line because it is always blank
    _ArrayDelete($aRaw, 0) ; delete row count in element 0
    FileClose($sInput)
    Return $aRaw
EndFunc   ;==>_FormatGenesis


Func _FormatAD()
    _AD_Open()
    Local $aRaw = _AD_GetObjectsInOU("", "(&(memberof:1.2.840.113556.1.4.1941:=CN=All Student Users,OU=a,DC=a,DC=b,DC=c)" & _
    "(objectCategory=user)(&(!cn=family)(!sn=user)(!sn=student)(!sn=kindergarten)(!sn=1st grade)))", 2, "givenName,sn,sAMAccountName,distinguishedName,extensionAttribute3", "givenName")
    For $i = 1 To UBound($aRaw) - 1
        If StringInStr($aRaw[$i][3], "location1") > 0 Then $aRaw[$i][3] = "location1"
        If StringInStr($aRaw[$i][3], "location2") > 0 Then $aRaw[$i][3] = "location2"
        If StringInStr($aRaw[$i][3], "location3") > 0 Then $aRaw[$i][3] = "location3"
        If StringInStr($aRaw[$i][3], "location4") > 0 Then $aRaw[$i][3] = "location4"
        If StringInStr($aRaw[$i][3], "location5") > 0 Then $aRaw[$i][3] = "location5"
    Next
    _AD_Close()
    _ArrayDelete($aRaw, 0) ; delete row count in element 0
    Return $aRaw
EndFunc   ;==>_FormatAD
Edited by kor

Share this post


Link to post
Share on other sites

#include <Array.au3>

$a_Input = StringSplit(FileRead("ad.txt"), @LF)
Global $aAD[$a_Input[0]][5]
For $i = 1 To $a_Input[0]
    $a_temp = StringSplit($a_Input[$i], ",")
    For $j = 0 To 4
        $aAD[$i - 1][$j] = $a_temp[$j + 1]
    Next
Next

$a_Input = StringSplit(FileRead("genesis.txt"), @LF)
Global $aGenesis[$a_Input[0]][5]
For $i = 1 To $a_Input[0]
    $a_temp = StringSplit($a_Input[$i], ",")
    For $j = 0 To 4
        $aGenesis[$i - 1][$j] = $a_temp[$j + 1]
    Next
Next

#cs
    Global $aAD[5][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
    ["Jason", "Jackson", "5678", "Location4", "4th"],["Chris", "Black", "4445", "Location2", "2nd"],["Lee", "Chan", "9876", "Location1", "2nd"]]
#ce

#cs
    Global $aGenesis[3][5] = [["Mike", "Jones", "1234", "Location2", "5th"],["John", "Smith", "3456", "Location1", "9th"], _
    ["Jason", "Jackson", "5678", "Location1", "2nd"]]
#ce

; _ArrayDisplay($aAD)
; _ArrayDisplay($aGenesis)

Global $oDict_Compare = ObjCreate('Scripting.Dictionary')
$oDict_Compare.CompareMode = 1
Global $s_Value, $a_Value

Global $aFinal[UBound($aAD)][6]

For $i = 0 To UBound($aGenesis) - 1
    $oDict_Compare.Add($aGenesis[$i][0] & "|||" & $aGenesis[$i][1], $aGenesis[$i][2] & "|||" & $aGenesis[$i][3] & "|||" & $aGenesis[$i][4])
Next


For $i = 0 To UBound($aAD) - 1
    If $oDict_Compare.Exists($aAD[$i][0] & "|||" & $aAD[$i][1]) Then
        $s_Value = $oDict_Compare.Item($aAD[$i][0] & "|||" & $aAD[$i][1])
        If $s_Value <> $aAD[$i][2] & "|||" & $aAD[$i][3] & "|||" & $aAD[$i][4] Then ; move
            $a_Value = StringSplit($s_Value, "|||", 1)
            $aFinal[$i][0] = $aAD[$i][0]
            $aFinal[$i][1] = $aAD[$i][1]
            $aFinal[$i][2] = $a_Value[1]
            $aFinal[$i][3] = $a_Value[2]
            $aFinal[$i][4] = $a_Value[3]
            $aFinal[$i][5] = "Move"
        Else ; ignore
            $aFinal[$i][0] = $aAD[$i][0]
            $aFinal[$i][1] = $aAD[$i][1]
            $aFinal[$i][2] = $aAD[$i][2]
            $aFinal[$i][3] = $aAD[$i][3]
            $aFinal[$i][4] = $aAD[$i][4]
            $aFinal[$i][5] = "Ignore"
        EndIf
        $oDict_Compare.Remove($aAD[$i][0] & "|||" & $aAD[$i][1])
    Else ; Delete
        $aFinal[$i][0] = $aAD[$i][0]
        $aFinal[$i][1] = $aAD[$i][1]
        $aFinal[$i][2] = $aAD[$i][2]
        $aFinal[$i][3] = $aAD[$i][3]
        $aFinal[$i][4] = $aAD[$i][4]
        $aFinal[$i][5] = "Delete"
    EndIf
Next
$iCount_Old = UBound($aFinal)
ReDim $aFinal[UBound($aFinal) + $oDict_Compare.Count()][6]
$i_Enum = 0
For $i In $oDict_Compare.Keys()
    $a_Value = StringSplit($i, "|||", 1)
    $aFinal[$iCount_Old + $i_Enum][0] = $a_Value[1]
    $aFinal[$iCount_Old + $i_Enum][1] = $a_Value[2]
    $a_Value = StringSplit($oDict_Compare.Item($i), "|||", 1)
    $aFinal[$iCount_Old + $i_Enum][2] = $a_Value[1]
    $aFinal[$iCount_Old + $i_Enum][3] = $a_Value[2]
    $aFinal[$iCount_Old + $i_Enum][4] = $a_Value[3]
    $aFinal[$iCount_Old + $i_Enum][5] = "Add"
    $i_Enum += 1
Next

; mike jones is the same so ignore him in the new array
; john smith is the same so ignore him in the new array
; jason jackson has a different location so move him using the information from $aGenesis
; chris black is not present so delete him
; lee chan is not present so delete him

#cs
    Global $aFinal[3][6] = [["Jason", "Jackson", "5678", "Location1", "2nd", "Move"],["Chris", "Black", "4445", "Location2", "2nd", "Delete"], _
    ["Lee", "Chan", "9876", "Location1", "2nd", "Delete"]]
#ce

_ArrayDisplay($aFinal)

Share this post


Link to post
Share on other sites

@Kafu... wow!

Your code works. If you have the time could you add some comments as to what the code is doing?

I am not familiar with the objects and calls your code is making so I'm very interested to learn.

Share this post


Link to post
Share on other sites

The userid is unique.

However KaFu's code utilized Scripting.Dictionary and when I fed it my 3000+ row array his code didn't work. just pegged my cpu.

That would seem to indicate a 3000+ row array is not the way to go. A tool that knows how to store the data on disk and performs efficient caching may take all the load off your shoulders. These problems have been solved decades ago. Why roll your own now? For trivial data sets then sure, just doing it out of your head is faster than learning to use some tool. But when it's gets large better to use the tool designed by the egghead who does nothing else but.

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  
Followers 0