Sign in to follow this  
Followers 0
MyEarth

Get duplicate from array

23 posts in this topic

#1 ·  Posted (edited)

Assuming i have an already sorted array:

$aTest[13] =[1,1,1,1,3,4,6,6,8,8,0,8,5]

My real eaxample is definitive bigger than that, how to remove the "unique" element and get only the duplicate one? Fastest as possibile, without ReDim the array or similar. I can create a new array for the result.

Thanks for the input ;)

Edited by MyEarth

Share this post


Link to post
Share on other sites



I can't write a code currently but here's an idea:

- For loop until the end of the array (from 1st to the end)

   - 2nd loop: look for your current value inside the rest array

      - Check if the same value exists

      - If so, the break & add it to your 2nd array, if not, then just go thru' the array.

- End loop

Something like this :)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

The array isn't sorted. Please show a working sample.

Show array before and after.

Edited by Exit

My UDFs, Abbrevs and Snippets
If you like my post, just click the like button :) 

Share this post


Link to post
Share on other sites

I don't have any working example to show, there are sensible information. Before the array has some unique element, after not :D

Share this post


Link to post
Share on other sites

MyEarth,

I would use scripting dictionaries rather like the _ArrayUnique function - although this time we look for multiples rather than unique elements: :)

#include <Array.au3>

Local $aTest[] = [14,1,1,1,1,3,4,6,6,8,8,0,8,5]

Local $aMult = _ArrayGetMultiples($aTest)

_ArrayDisplay($aMult, "Only multiples", Default, 8)

Func _ArrayGetMultiples($aArray)

    ; Create dictionaries
    Local $oDict_Original = ObjCreate("Scripting.Dictionary")
    Local $oDict_Multiple = ObjCreate("Scripting.Dictionary")

    Local $vElem

    ; Loop through array
    For $i = 0 To UBound($aArray) - 1
        ; Extract element
        $vElem = $aArray[$i]
        ; Check if already in original array
        If $oDict_Original.Exists($vElem) Then
            ; Check if already in multiple dictionary
            If Not $oDict_Multiple.Exists($vElem) Then
                ; Add to multiple dictionary
                $oDict_Multiple.Item($vElem)
            EndIf
        Else
            ; Add to original element dictionary
            $oDict_Original.Item($vElem)
        EndIf
    Next

    ; Create the return array from teh multipel dictionary
    Return $oDict_Multiple.Keys()

EndFunc   ;==>_ArrayUnique_COM
That returns just a single example of each multiple element - it is easy to make it return all instances if that is what you require. ;)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Melba,

Yes i need all istance in an array like the first one but without the "unique" value. Thanks

Local $aTest[10] = [9,1,1,1,1,6,6,8,8,8]

$aTest[0] = Number of the element

Edited by MyEarth

Share this post


Link to post
Share on other sites

The array isn't "already sorted" by any mean.

Also it is unclear whether you want a resulting array with multiple entries per duplicate or just a list of entries appearing multiple times in the source array.

Why the constraint on not using ReDim?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

MyEarth,

Moving the goalposts in mid-thread I see. That makes it much more complicated - so be prepared for a wait. ;)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Melba,

I don't have move anything :D

I have write "how to remove the "unique" element and get only the duplicate one" and "I can create a new array for the result", maybe i wasn't clear. Sure i can wait.

Redimming the array slow the code, i don't have 10 element but 250.000 approssimative or more, every MS saved is many minutes gained for me. I want to get an array ( also a new array, i don't care ) of only duplicate element, i don't need the unique one and i don't know how to exaplain in a better way :D

The opposite of _ArrayUnique?

Returns the Elements from a column of a 1D or 2D array, removing all unique element

Edited by MyEarth

Share this post


Link to post
Share on other sites

MyEarth,

Try this function - it works well on your initial array: ;)

#include <Array.au3>

Local $aTest[] = [14,1,1,1,1,3,4,6,6,8,8,0,8,5]

Local $aMult = _ArrayNoSingles($aTest)

_ArrayDisplay($aMult, "Only multiples", Default, 8)

Func _ArrayNoSingles($aArray)

    ; Create dictionary
    Local $oDict = ObjCreate("Scripting.Dictionary")

    Local $vElem
    ; Loop through array
    For $i = 0 To UBound($aArray) - 1
        ; Extract element
        $vElem = $aArray[$i]
        ; Check if already in dictionary
        If $oDict.Exists($vElem) Then
            ; Increase count
            $oDict($vElem) = $oDict($vElem) + 1
        Else
            ; Add to dictionary
            $oDict.Item($vElem) = 1
        EndIf
    Next

    ; Create return array large enough for all elements
    Local $aRet[UBound($aArray) + 1], $iIndex = 0

    ; Loop through dictionary and transfer multiple elements to return array
    For $vKey in $oDict
        ; Get count
        $iCount = $oDict($vKey)
        ; if more than 1
        If $iCount > 1 Then
            ; Add as many as required
            For $i = 1 To $iCount
                $iIndex += 1
                $aRet[$iIndex] = $vKey
            Next
        EndIf
    Next
    ; Add count to [0]element
    $aRet[0] = $iIndex
    ; Remove ampty elements of return array
    ReDim $aRet[$iIndex + 1] ; You need this one ReDim, honest!!! <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    Return $aRet

EndFunc
Run it on your 250k array and let me know how it gets on. :D

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Melba,

I'll try soon but based on your super great experience with array and co. ( based on your script ) this can be faster or slower?

#include <Array.au3>

Local $aTest[14] = [1, 1, 1, 1, 3, 4, 6, 6, 8, 8, 0, 8, 5]
Local $aMult = _ArrayNoSingles($aTest)

If IsArray($aMult) Then _ArrayDisplay($aMult, "Only multiples")

Func _ArrayNoSingles($aArray)
    ; Create dictionary
    Local $oDict = ObjCreate("Scripting.Dictionary")

    Local $vElem
    ; Loop through array
    For $i = 0 To UBound($aArray) - 1
        ; Extract element
        $vElem = $aArray[$i]
        ; Check if already in dictionary
        If $oDict.Exists($vElem) Then
            ; Increase count
            $oDict($vElem) = $oDict($vElem) + 1
        Else
            ; Add to dictionary
            $oDict.Item($vElem) = 1
        EndIf
    Next

    Local $aRet
    ; Loop through dictionary and transfer multiple elements to return array
    For $vKey In $oDict
        ; Get count
        $iCount = $oDict($vKey)
        ; if more than 1
        If $iCount > 1 Then
            ; Add as many as required
            For $i = 1 To $iCount
                $aRet &= $vKey & "|"
            Next
        EndIf
    Next
    If Not StringInStr($aRet, "|") Then Return False
    Return StringSplit(StringTrimRight($aRet, 1), "|")
EndFunc   ;==>_ArrayNoSingles
Edited by MyEarth

Share this post


Link to post
Share on other sites

ReDim significantly slows things down only when carelessly repeated within a loop.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

MyEarth,

 

this can be faster or slower?

I imagine it might be slightly slower as you are first creating a string and then splitting it into an array rather then adding elements directly to an array before a single ReDim - but why not try on your giant array and let us know the result? :)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

Before try on the big i have make some change to the code, i have think to do by myself but seems not :(

The database i have is like:

[1]NAME|SURNAME|ID

[2]NAME|SURNAME|ID

[3]NAME|SURNAME|ID

[4]NAME|SURNAME|ID

etc.

The ID is a number, the value "duplicate" i need to get ( so that's why i have write that "example" array )

#include <Array.au3>

Local $aTest[4] = ["Melba23|SURNAME|1", "jchd|SURNAME|3", "MyEarth|SURNAME|1", "jchd|SURNAME|4"]
;~ Local $aTest[14] = [1, 1, 1, 1, 3, 4, 6, 6, 8, 8, 0, 8, 5]

Local $aMult = _ArrayNoSingles($aTest)

If IsArray($aMult) Then _ArrayDisplay($aMult, "Only multiples")

Func _ArrayNoSingles($aArray)

    ; Create dictionary
    Local $oDict = ObjCreate("Scripting.Dictionary")

    Local $vElem, $vElem2
    ; Loop through array
    For $i = 0 To UBound($aArray) - 1
        ; Extract element
        $vElem = StringTrimLeft($aArray[$i], StringInStr($aArray[$i], "|", 0, -1))
        $vElem2 = $aArray[$i]
        ; Check if already in dictionary
        If $oDict.Exists($vElem) Then
            ; Increase count
            $oDict($vElem2) = $oDict($vElem) + 1
        Else
            ; Add to dictionary
            $oDict.Item($vElem) = 1
        EndIf
    Next

    ; Create return array large enough for all elements
    Local $aRet[UBound($aArray) + 1], $iIndex = 0

    ; Loop through dictionary and transfer multiple elements to return array
    For $vKey in $oDict
        ; Get count
        $iCount = $oDict($vKey)
        ; if more than 1
        If $iCount > 1 Then
            ; Add as many as required
            For $i = 1 To $iCount
                $iIndex += 1
                $aRet[$iIndex] = $vKey
            Next
        EndIf
    Next
    ; Add count to [0]element
    $aRet[0] = $iIndex
    ; Remove ampty elements of return array
    ReDim $aRet[$iIndex + 1]
    Return $aRet
EndFunc

Why my name came twice instead of one? Why i don't have the "Melba23" value?  :(

Edited by MyEarth

Share this post


Link to post
Share on other sites

At any rate a bunch of 250K names is a job for a database.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

MyEarth,

 

Why my name came twice instead of one? Why i don't have the "Melba23" value?

Because once again you are changing your requirements in mid-thread and asking the code to do something different. This is something that seriously annoys me and you have now done it twice - this had better be the last time. :mad:

And having now seen what you are actually trying to do, I fully agree with jchd that a database is better suited to your requirements. But I will give it one last try. ;)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

MyEarth,

And here is the modified function which will work on the new multiple element array you have provided:

#include <Array.au3>

Local $aTest[4] = ["Melba23|SURNAME|1", "jchd|SURNAME|3", "MyEarth|SURNAME|1", "jchd|SURNAME|4"]

Local $aMult = _ArrayNoSingles($aTest)

If IsArray($aMult) Then _ArrayDisplay($aMult, "Only multiples")

Func _ArrayNoSingles($aArray)

    ; Create dictionary
    Local $oDict = ObjCreate("Scripting.Dictionary")

    For $i = 0 To UBound($aArray) - 1

        $vElem = $aArray[$i]
        $vUnique = StringSplit($vElem, "|")[3] ; Get the unique element at the end of the string

        ; Check if already in dictionary
        If $oDict.Exists($vUnique) Then
            ; Add whole element to existing list
            $oDict($vUnique) = $oDict($vUnique) & "#" & $vElem ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        Else
            ; Add whole element to dictionary
            $oDict.Item($vUnique) = $vElem
        EndIf
    Next

    ; Create return array large enough for all elements
    Local $aRet[UBound($aArray) + 1], $iIndex = 0

    ; Loop through dictionary and transfer multiple elements to return array
    For $vKey in $oDict
        $vValue = $oDict($vKey)
        ; Check if more than one delimiter
        StringReplace($vValue, "#", "&")  ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        If @extended Then
            $aSplit = StringSplit($vValue, "#")  ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            For $i = 1 To $aSplit[0]
                $iIndex += 1
                $aRet[$iIndex] = $aSplit[$i]
            Next
        EndIf
    Next
    ; Add count to [0]element
    $aRet[0] = $iIndex
    ; Remove ampty elements of return array
    ReDim $aRet[$iIndex + 1]
    Return $aRet

EndFunc
If the # character could appear in the array, change the 3 lines marked with <<<<<<<<<<<<<<< to use another character which does not. ;)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

Just for illustration of my statement, I have an SQLite database table of zip codes from several contries, totalling 75372 entries.

The following query takes 220ms on average to complete and populate the display with 40964 rows under SQLite Expert:

select paysiso, id, code, ville from codespostaux where code in (select code from codespostaux group by code having paysiso = 'FR' and count(code) > 1)

Even if the result set is likely to take some more time due to AutoIt being slightly slower, this non-trivial example shows that you're more comfortable and efficient with the right tool.

A similar query on a larger table (848207 rows) select 444793 rows in circa 2.7 s on my slow PC. The DBs are disk-based and not on SSD.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#19 ·  Posted (edited)

here a way that transforms the array into an SQL temporary database,

then use the SQL query provided by jchd to extract only duplicates (or more than duplicate) from the sql table (created on the fly from the array)

and then returns the extracted data into an array (only records that are in the list at least > 1 times)

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
Global $aResult, $iRows, $iColumns, $iRval

; ---------------------------------------------------------------------------------------------------------------------------
Local $Array[20]= [4,6,4,7,1,1,1,99,1,3,4,6,6,8,8,0,8,5,99,88]
; ---------------------------------------------------------------------------------------------------------------------------
_SQLite_Startup()
_SQLite_Open()

_ArrayToSqlTable($Array) ; clone array to a temporary sql db

$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM array;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    ; _SQLite_Display2DResult($aResult)
    _ArrayDisplay($aResult, "Before SQL query")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf
; --- use sql to manage the array ------------------------------------------------------------------------
$iRval = _SQLite_GetTable2d(-1, "SELECT field0 FROM array WHERE field0 IN (SELECT field0 FROM array GROUP BY field0 HAVING COUNT(field0) > 1);", $aResult, $iRows, $iColumns)
; --------------------------------------------------------------------------------------------------------

If $iRval = $SQLITE_OK Then
    ; _SQLite_Display2DResult($aResult)
    _ArrayDisplay($aResult, "After SQL query")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf

_SQLite_Close()
_SQLite_Shutdown()
; -- the end --

Func _ArrayToSqlTable($Array) ; Pass an array to a temporary SQL table
    If Not IsArray($Array) Then Return SetError(1, 0, 0)
    Local $Dimensions = UBound($Array, 0)
    If $Dimensions > 2 Then Return  SetError(2, 0, 0)
    Local $DBfields = ""
    Local $DBvalues = ""
    Local $records = UBound($Array, 1) - 1
    Local $fields = 0
    If $Dimensions = 2 Then
        $fields = UBound($Array, 2) - 1
    EndIf
    For $x = 0 To $fields
        $DBfields &= "field" & String($x) & ","
    Next
    $DBfields = StringTrimRight($DBfields, 1) ; remove the last comma

    If Not _SQLite_Exec(-1, "CREATE TEMP TABLE array (" & $DBfields & ");") = $SQLITE_OK Then
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
    Else
        For $x = 0 To $records
            For $y = 0 To $fields
                Switch $fields
                    Case 0 ; just 1 field (1D Array)
                        If IsNumber($Array[$x]) Then
                            $DBvalues &= $Array[$x] & ","
                        Else
                            $DBvalues &= _SQLite_FastEscape($Array[$x]) & ","
                        EndIf
                    Case Else ; multi fields (2D Array)
                        If IsNumber($Array[$x][$y]) Then
                            $DBvalues &= $Array[$x][$y] & ","
                        Else
                            $DBvalues &= _SQLite_FastEscape($Array[$x][$y]) & ","
                        EndIf
                EndSwitch
            Next
            $DBvalues = StringTrimRight($DBvalues, 1)
            ; insert row to table
            If Not _SQLite_Exec(-1, "INSERT INTO array VALUES (" & $DBvalues & ");") = $SQLITE_OK Then MsgBox(16, "SQLite insert Error", _SQLite_ErrMsg())
            $DBvalues = ""
        Next
    EndIf
EndFunc   ;==>_ArrayToSqlTable

EDIT:

it works with 2D array as well

just adapt the sql query with the right field name, (filed0 is first field .... and so on)

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
Global $aResult, $iRows, $iColumns, $iRval

; ---------------------------------------------------------------------------------------------------------------------------
; Local $Array[22] = [4, 6, 4, 7, "pippo", 1, 1, 1, 99, 1, 3, 4, 6, 6, 8, 8, "pippo", 0, 8, 5, 99, 88]
; ---------------------------------------------------------------------------------------------------------------------------
Local $Array[4][3] = [["Melba23", "SURNAME", "1"],["jchd", "SURNAME", "3"],["MyEarth", "SURNAME", "1"],["jchd", "SURNAME", "4"]]
_SQLite_Startup()
_SQLite_Open()

_ArrayToSqlTable($Array) ; clone array to a temporary sql db

$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM array;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    ; _SQLite_Display2DResult($aResult)
    _ArrayDisplay($aResult, "Before SQL query")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf
; --- use sql to manage the array ------------------------------------------------------------------------
; $iRval = _SQLite_GetTable2d(-1, "SELECT * FROM array WHERE field0 IN (SELECT field0 FROM array GROUP BY field0 HAVING COUNT(field0) > 1);", $aResult, $iRows, $iColumns)
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM array WHERE field2 IN (SELECT field2 FROM array GROUP BY field2 HAVING COUNT(field2) > 1);", $aResult, $iRows, $iColumns)
; --------------------------------------------------------------------------------------------------------

If $iRval = $SQLITE_OK Then
    ; _SQLite_Display2DResult($aResult)
    _ArrayDisplay($aResult, "After SQL query")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf

_SQLite_Close()
_SQLite_Shutdown()
; -- the end --

Func _ArrayToSqlTable($Array) ; Pass an array to a temporary SQL table
    If Not IsArray($Array) Then Return SetError(1, 0, 0)
    Local $Dimensions = UBound($Array, 0)
    If $Dimensions > 2 Then Return SetError(2, 0, 0)
    Local $DBfields = ""
    Local $DBvalues = ""
    Local $records = UBound($Array, 1) - 1
    Local $fields = 0
    If $Dimensions = 2 Then
        $fields = UBound($Array, 2) - 1
    EndIf
    For $x = 0 To $fields
        $DBfields &= "field" & String($x) & ","
    Next
    $DBfields = StringTrimRight($DBfields, 1) ; remove the last comma

    If Not _SQLite_Exec(-1, "CREATE TEMP TABLE array (" & $DBfields & ");") = $SQLITE_OK Then
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
    Else
        For $x = 0 To $records
            For $y = 0 To $fields
                Switch $fields
                    Case 0 ; just 1 field (1D Array)
                        If IsNumber($Array[$x]) Then
                            $DBvalues &= $Array[$x] & ","
                        Else
                            $DBvalues &= _SQLite_FastEscape($Array[$x]) & ","
                        EndIf
                    Case Else ; multi fields (2D Array)
                        If IsNumber($Array[$x][$y]) Then
                            $DBvalues &= $Array[$x][$y] & ","
                        Else
                            $DBvalues &= _SQLite_FastEscape($Array[$x][$y]) & ","
                        EndIf
                EndSwitch
            Next
            $DBvalues = StringTrimRight($DBvalues, 1)
            ; insert row to table
            If Not _SQLite_Exec(-1, "INSERT INTO array VALUES (" & $DBvalues & ");") = $SQLITE_OK Then MsgBox(16, "SQLite insert Error", _SQLite_ErrMsg())
            $DBvalues = ""
        Next
    EndIf
EndFunc   ;==>_ArrayToSqlTable
Edited by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

Given this array...

Local $aTest[4] = ["Melba23|SURNAME|1", "jchd|SURNAME|3", "MyEarth|SURNAME|1", "jchd|SURNAME|4"]

What exactly should be reported?


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

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