Jump to content

compare two arrays and remove the duplicates from one array with _ArrayDelete

charon

By charon,
in AutoIt General Help and Support

 Share

Recommended Posts

charon Seeker

charon

Posted
Posted

Trying to compare two arrays and remove the duplicates from one array with _Arraydelete function and enter results into MySQL database:

Problem: I am still getting duplicates even though its only 1 or 2 duplicates

Program flow: Loads array from csv file and loads 2nd array from database. Runs a loop to compare the data between 2 arrays and deletes duplicates in filearray. Inserts remaing data into database. End

Array names:

database array: dbarray[rows in db][4]   only need to compare columns 1,2,3 and all rows
file array: filearray[line#s][5]   only need to compare columns 2,3,4 and all rows  (Want to remove duplicates from this array)

Sample data in array:

DB array:
[0]|id|name|location|version
[1]|1| Adobe AIR|C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall|2.6.0.19120
[2]|2| Cake Mania|"C:Program Files (x86)WildTangentDell GamesCake ManiaUninstall.exe"|2.2.0.95[09.13. 16
[3]|3| FATE|"C:Program Files (x86)WildTangentDell GamesFATEUninstall.exe"|2.2.0.95
[4]|4| Polar Golfer|"C:Program Files (x86)WildTangentDell GamesPolar GolferUninstall.exe"|2.2.0.95
[5]|5| Cisco Connect|"C:Program Files (x86)Cisco SystemsCisco ConnectCisco Connect.exe" -uninstall|1.4.11299.0

File array
[0]|||||
[1]||| Adobe AIR|C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall|2.6.0.19120
[2]||| Cake Mania|"C:Program Files (x86)WildTangentDell GamesCake ManiaUninstall.exe"|2.2.0.95[09.13. 16:23:40]
[3]||| FATE|"C:Program Files (x86)WildTangentDell GamesFATEUninstall.exe"|2.2.0.95
[4]||| Polar Golfer|"C:Program Files (x86)WildTangentDell GamesPolar GolferUninstall.exe"|2.2.0.95
[5]||| Cisco Connect|"C:Program Files (x86)Cisco SystemsCisco ConnectCisco Connect.exe" -uninstall|1.4.11299.0

After loop that deletes duplicates: filearray now shows 1 entry which is a duplicate. Should show nothing.
[0]|||||
[1]||| Cake Mania|"C:Program Files (x86)WildTangentDell GamesCake ManiaUninstall.exe"|2.2.0.95[09.13. 16:23:40]

Here is the main loop where it is not deleting all the duplicates for some reason:

Here is the main loop:

For $fileloop = UBound($Filearray)- 1 to 0 step -1
   For $dbloop = UBound($dbarray) - 1 to 0 step -1
    If $dbarray[$dbloop][1] == $Filearray[$fileloop][2] AND $dbarray[$dbloop][2] == $Filearray[$fileloop][3] AND $dbarray[$dbloop][3] == $Filearray[$fileloop][4] Then

        MsgBox(0, "Found", "File loop: " & $fileloop & " dbloop: " & $dbloop & " Column: " & $column & @CR & "filename: " & $Filearray[$fileloop][2] & @CR & "dbname: " & $dbarray[$dbloop][1] & @CR & "fileloop: " & $fileloop & " dbloop: " & $dbloop)

        _ArrayDelete($Filearray, $fileloop)
        _ArrayDisplay($Filearray, "After delete in loop")
        ExitLoop 1
    EndIf
   Next
Next

After main loop: Code is executed to add the remaining data into MySQL database. I left out all the MySQL code which seems to be working fine. Let me know if more code is needed from my program
 

Link to comment
Share on other sites
UEZ Universalist

UEZ

Posted
Posted (edited)

Here a shorter variant:

#include <Array.au3>

$aDB = StringSplit(StringStripCR(FileRead(@ScriptDir & "\DB_Array.csv")), @LF, 2)
$aFile = StringSplit(StringStripCR(FileRead(@ScriptDir & "\File_Array.csv")), @LF, 2)
For $i = UBound($aFile) - 1 To 1 Step - 1
    If StringMid($aFile[$i], 8) = StringMid($aDB[$i], 8) Then _ArrayDelete($aFile, $i)
Next
_ArrayDisplay($aDB)
_ArrayDisplay($aFile)

I don't know whether it is a copy/paste issue but Cake Mania line is really different

db:
[2]|2| Cake Mania|"C:\Program Files (x86)\WildTangent\Dell Games\Cake Mania\Uninstall.exe"|2.2.0.95[09.13. 16
file:
[2]||| Cake Mania|"C:\Program Files (x86)\WildTangent\Dell Games\Cake Mania\Uninstall.exe"|2.2.0.95[09.13. 16:23:40]

As you can see the time is cut at the end and thus both lines are not equal.

 

Br,

UEZ

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best! ✌
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted (edited)

I'd rather use SQL features to do the job all in one go and without having to read the existing table first, using INSERT OR REPLACE or INSERT ... ON CONFLICT ... clause. The best suited SQL is dependent on the actual schema.

After all, SQL was precisely designed to handle this (and much more) robustly and efficiently.

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)

Link to comment
Share on other sites
charon Seeker

charon

Posted
  • Author
Posted

Here a shorter variant:

#include <Array.au3>

$aDB = StringSplit(StringStripCR(FileRead(@ScriptDir & "\DB_Array.csv")), @LF, 2)
$aFile = StringSplit(StringStripCR(FileRead(@ScriptDir & "\File_Array.csv")), @LF, 2)
For $i = UBound($aFile) - 1 To 1 Step - 1
    If StringMid($aFile[$i], 8) = StringMid($aDB[$i], 8) Then _ArrayDelete($aFile, $i)
Next
_ArrayDisplay($aDB)
_ArrayDisplay($aFile)

I don't know whether it is a copy/paste issue but Cake Mania line is really different

db:
[2]|2| Cake Mania|"C:\Program Files (x86)\WildTangent\Dell Games\Cake Mania\Uninstall.exe"|2.2.0.95[09.13. 16
file:
[2]||| Cake Mania|"C:\Program Files (x86)\WildTangent\Dell Games\Cake Mania\Uninstall.exe"|2.2.0.95[09.13. 16:23:40]

As you can see the time is cut at the end and thus both lines are not equal.

 

Br,

UEZ

You are right my sample data was wrong. I corrected it and it still doesn't work. I tried the sample script u provided but I am getting subscript error.

$aDB = StringSplit(StringStripCR(FileRead(@ScriptDir & "\db.txt")), @LF, 2)
$aFile = StringSplit(StringStripCR(FileRead(@ScriptDir & "\fd.txt")), @LF, 2)
_ArrayDisplay($aDB)
_ArrayDisplay($aFile)
For $i = UBound($aFile) - 1 To 1 Step - 1
    If StringMid($aFile[$i], 19) = StringMid($aDB[$i], 19) Then 
        
         _ArrayDelete($aFile, $i)
   EndIf
Next
_ArrayDisplay($aDB)
_ArrayDisplay($aFile, "File array")

(18) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
If StringMid($aFile[$i], 19) = StringMid($aDB[$i], 19) Then
If StringMid($aFile[$i], 19) = StringMid(^ ERROR

Sample data: db file:

[09.13. 16:23:40], Adobe AIR,C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall,2.6.0.19120

[09.13. 16:23:40], Cake Mania,"C:Program Files (x86)WildTangentDell GamesCake ManiaUninstall.exe",2.2.0.95

Filedata file:

[09.13. 16:23:40], Adobe AIR,C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall,2.6.0.19120

[09.13. 16:23:40], Cake Mania,"C:Program Files (x86)WildTangentDell GamesCake ManiaUninstall.exe",2.2.0.95

[09.13. 16:23:40], Windows Live Essentials,C:Program Files (x86)Windows LiveInstallerwlarp.exe,15.4.3508.1109

[09.13. 16:23:40], FATE,"C:Program Files (x86)WildTangentDell GamesFATEUninstall.exe",2.2.0.95

[09.13. 16:23:40], Polar Golfer,"C:Program Files (x86)WildTangentDell GamesPolar GolferUninstall.exe",2.2.0.95

Link to comment
Share on other sites
charon Seeker

charon

Posted
  • Author
Posted

I'd rather use SQL features to do the job all in one go and without having to read the existing table first, using INSERT OR REPLACE or INSERT ... ON CONFLICT ... clause. The best suited SQL is dependent on the actual schema.

After all, SQL was precisely designed to handle this (and much more) robustly and efficiently.

Can u give me more info or sample query?

Link to comment
Share on other sites
sahsanu Universalist

sahsanu

Posted
Posted

Sample data: db file:

[09.13. 16:23:40], Adobe AIR,C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall,2.6.0.19120

[09.13. 16:23:40], Cake Mania,"C:Program Files (x86)WildTangentDell GamesCake ManiaUninstall.exe",2.2.0.95

Filedata file:

[09.13. 16:23:40], Adobe AIR,C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall,2.6.0.19120

[09.13. 16:23:40], Cake Mania,"C:Program Files (x86)WildTangentDell GamesCake ManiaUninstall.exe",2.2.0.95

[09.13. 16:23:40], Windows Live Essentials,C:Program Files (x86)Windows LiveInstallerwlarp.exe,15.4.3508.1109

[09.13. 16:23:40], FATE,"C:Program Files (x86)WildTangentDell GamesFATEUninstall.exe",2.2.0.95

[09.13. 16:23:40], Polar Golfer,"C:Program Files (x86)WildTangentDell GamesPolar GolferUninstall.exe",2.2.0.95

 

I don't really know what are the right samples of your data because in the first post you wrote something and now the samples are different (they have the same columns)... anyway, using the last samples you posted and your original script... it is working for me:

#include <array.au3>
Local $dbarray[3][4] = [ _
        ["id", "name", "location", "version"], _
        ["[09.13. 16:23:40]", " Adobe AIR", "C:\Program Files (x86)\Common Files\Adobe AIR\Versions\1.0\Resources\Adobe AIR Updater.exe -arp:uninstall", "2.6.0.19120"], _
        ["[09.13. 16:23:40]", " Cake Mania", "C:\Program Files (x86)\WildTangent\Dell Games\Cake Mania\Uninstall.exe", "2.2.0.95"] _
        ]
Local $Filearray[6][4] = [ _
        ["id", "name", "location", "version"], _
        ["[09.13. 16:23:40]", " Adobe AIR", "C:\Program Files (x86)\Common Files\Adobe AIR\Versions\1.0\Resources\Adobe AIR Updater.exe -arp:uninstall", "2.6.0.19120"], _
        ["[09.13. 16:23:40]", " Cake Mania", "C:\Program Files (x86)\WildTangent\Dell Games\Cake Mania\Uninstall.exe", "2.2.0.95"], _
        ["[09.13. 16:23:40]", " Windows Live Essentials", "C:\Program Files (x86)\Windows Live\Installer\wlarp.exe", "15.4.3508.1109"], _
        ["[09.13. 16:23:40]", " FATE", "C:\Program Files (x86)\WildTangent\Dell Games\FATE\Uninstall.exe", "2.2.0.95"], _
        ["[09.13. 16:23:40]", " Polar Golfer", "C:\Program Files (x86)\WildTangent\Dell Games\Polar Golfer\Uninstall.exe", "2.2.0.95"] _
        ]

Local $fileloop
For $fileloop = UBound($Filearray) - 1 to 1 step -1
    For $dbloop = UBound($dbarray) - 1 to 1 step -1
        If $dbarray[$dbloop][1] == $Filearray[$fileloop][1] AND $dbarray[$dbloop][2] == $Filearray[$fileloop][2] AND $dbarray[$dbloop][3] == $Filearray[$fileloop][3] Then
            MsgBox(0, "Found", "File loop: " & $fileloop & " dbloop: " & $dbloop & @CR & "filename: " & $Filearray[$fileloop][2] & _
                    @CR & "dbname: " & $dbarray[$dbloop][1] & @CR & "fileloop: " & $fileloop & " dbloop: " & $dbloop)
            _ArrayDelete($Filearray, $fileloop)
            _ArrayDisplay($Filearray, "After delete in loop")
            ExitLoop 1
        EndIf
    Next
Next

In this example both arrays have the same amount of columns so I've compared the same columns for both arrays instead of (1,2,3 and 2,3,4). I think your real arrays have a different amount of columns so please, post a valid sample of both arrays just like I did in the above script, it would be easier for us to check the script and try to help you to solve the problem. ;)

Cheers,

sahsanu

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

Refer to MySQL documentation or tutorials, I'm not using this engine myself and, more importantly, I don't know what your DB schema looks like.

The general idea is: instead of extracting data already loaded in the DB and comparing with external input data to determine what's new, just insert all external data and use SQL machinery to ignore entries already present in the DB.

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)

Link to comment
Share on other sites
charon Seeker

charon

Posted
  • Author
Posted

I don't really know what are the right samples of your data because in the first post you wrote something and now the samples are different (they have the same columns)... anyway, using the last samples you posted and your original script... it is working for me:

#include <array.au3>
Local $dbarray[3][4] = [ _
        ["id", "name", "location", "version"], _
        ["[09.13. 16:23:40]", " Adobe AIR", "C:\Program Files (x86)\Common Files\Adobe AIR\Versions\1.0\Resources\Adobe AIR Updater.exe -arp:uninstall", "2.6.0.19120"], _
        ["[09.13. 16:23:40]", " Cake Mania", "C:\Program Files (x86)\WildTangent\Dell Games\Cake Mania\Uninstall.exe", "2.2.0.95"] _
        ]
Local $Filearray[6][4] = [ _
        ["id", "name", "location", "version"], _
        ["[09.13. 16:23:40]", " Adobe AIR", "C:\Program Files (x86)\Common Files\Adobe AIR\Versions\1.0\Resources\Adobe AIR Updater.exe -arp:uninstall", "2.6.0.19120"], _
        ["[09.13. 16:23:40]", " Cake Mania", "C:\Program Files (x86)\WildTangent\Dell Games\Cake Mania\Uninstall.exe", "2.2.0.95"], _
        ["[09.13. 16:23:40]", " Windows Live Essentials", "C:\Program Files (x86)\Windows Live\Installer\wlarp.exe", "15.4.3508.1109"], _
        ["[09.13. 16:23:40]", " FATE", "C:\Program Files (x86)\WildTangent\Dell Games\FATE\Uninstall.exe", "2.2.0.95"], _
        ["[09.13. 16:23:40]", " Polar Golfer", "C:\Program Files (x86)\WildTangent\Dell Games\Polar Golfer\Uninstall.exe", "2.2.0.95"] _
        ]

Local $fileloop
For $fileloop = UBound($Filearray) - 1 to 1 step -1
    For $dbloop = UBound($dbarray) - 1 to 1 step -1
        If $dbarray[$dbloop][1] == $Filearray[$fileloop][1] AND $dbarray[$dbloop][2] == $Filearray[$fileloop][2] AND $dbarray[$dbloop][3] == $Filearray[$fileloop][3] Then
            MsgBox(0, "Found", "File loop: " & $fileloop & " dbloop: " & $dbloop & @CR & "filename: " & $Filearray[$fileloop][2] & _
                    @CR & "dbname: " & $dbarray[$dbloop][1] & @CR & "fileloop: " & $fileloop & " dbloop: " & $dbloop)
            _ArrayDelete($Filearray, $fileloop)
            _ArrayDisplay($Filearray, "After delete in loop")
            ExitLoop 1
        EndIf
    Next
Next

In this example both arrays have the same amount of columns so I've compared the same columns for both arrays instead of (1,2,3 and 2,3,4). I think your real arrays have a different amount of columns so please, post a valid sample of both arrays just like I did in the above script, it would be easier for us to check the script and try to help you to solve the problem. ;)

Cheers,

sahsanu

 

The db array would be:

Local $dbarray[3][4] = [ _

        ["id", "name", "location", "version"], _

        ["1", " Adobe AIR", "C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall", "2.6.0.19120"], _

        ["2", " Cake Mania", "C:Program Files (x86)WildTangentDell GamesCake ManiaUninstall.exe", "2.2.0.95"] _

Local $Filearray[6][4] = [ _

        ["not used", "not used", "name", "location","version"], _

        ["", "", " Adobe AIR", "C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall", "2.6.0.19120"], _

 I noticed I have empty lines in the text file when I view it with notepadd++ instead of just notepad so I added StringStripCR to the code. Sample data. Yes the data changes but the columns are always 4: date I do not use, name I use, location used, and version is used for comparison.

[09.13. 08:23:12], AutoIt v3.3.8.1,C:AutoITAutoIt3Uninstall.exe,0

[09.13. 08:23:12], FileZilla Client 3.7.3,C:Program Files (x86)FileZilla FTP Clientuninstall.exe,3.7.3

[09.13. 08:23:12], Foxit Reader,"C:Program Files (x86)Foxit SoftwareFoxit Readerunins000.exe",5.4.5.124

Most important parts of code: 

Here is the file array which is pulled from text file:

$res = _FileReadToArray($CSVFILE, $arrContent)
$rowx = $arrContent[0] + 1

Dim $Filearray[$rowx][5]   ;Rows depend on how many lines are in the text file which will vary and columns are constant 5 
;ex data: [09.13. 08:23:12], AutoIt v3.3.8.1,C:\AutoIT\AutoIt3\Uninstall.exe,0   ;4 columns

If $res = 1 Then 
    For $row = 1 To $arrContent[0]   
        $arrLine = StringSplit(StringStripCR($arrContent[$row]), $DELIM)  ;just added StringStripCR
        For $Fcolumn = 2 To 4    ;Fcolumn represents fields in each line seperated by commas
            $Filearray[$row][$Fcolumn] = $arrLine[$Fcolumn]
        Next
    Next
Else
    MsgBox(48, "", "Error opening file!")
EndIf
 ;_ArrayDisplay($arr)
 
 
 ;Here is SQL data that loads the array
 $query = "SELECT * FROM apps_table" ; WHERE name = '" & _MySQL_Real_Escape_String($MysqlConn,$name[$x]) & "' AND location='" 
  $mysql_bool = _MySQL_Real_Query($MysqlConn, $query)

 ;MsgBox(0, '', "Access method 3 - read whole result in 2D-Array")
 $res = _MySQL_Store_Result($MysqlConn)
$dbarray = _MySQL_Fetch_Result_StringArray($res)

_ArrayDisplay($dbarray, "Database array:")

;sample database data:  
;~ [0]|id|name|location|version
;~ [1]|1| AutoIt v3.3.8.1|C:\AutoIT\AutoIt3\Uninstall.exe|0
;~ [2]|2| FileZilla Client 3.7.3|C:\Program Files (x86)\FileZilla FTP Client\uninstall.exe|3.7.3
;~ [3]|3| Foxit Reader|"C:\Program Files (x86)\Foxit Software\Foxit Reader\unins000.exe"|5.4.5.124

_ArrayDisplay($Filearray, "File array")
;~ [0]|||||
;~ [1]||| AutoIt v3.3.8.1|C:\AutoIT\AutoIt3\Uninstall.exe|0
;~ [2]||| FileZilla Client 3.7.3|C:\Program Files (x86)\FileZilla FTP Client\uninstall.exe|3.7.3
;~ [3]||| Foxit Reader|"C:\Program Files (x86)\Foxit Software\Foxit Reader\unins000.exe"|5.4.5.124
;~ [4]||| Mozilla Firefox 23.0.1 (x86 en-US)|"C:\Program Files (x86)\Mozilla Firefox\uninstall\helper.exe"|23.0.1
;~ [5]||| Mozilla Maintenance Service|"C:\Program Files (x86)\Mozilla Maintenance Service\uninstall.exe"|23.0.1
;~ [6]||| Notepad++|C:\Program Files (x86)\Notepad++\uninstall.exe|6.3.2

For $fileloop = UBound($Filearray)- 1 to 0 step -1
   For $dbloop = UBound($dbarray) - 1 to 0 step -1

 If $dbarray[$dbloop][1] == $Filearray[$fileloop][2] AND $dbarray[$dbloop][2] == $Filearray[$fileloop][3] AND $dbarray[$dbloop][3] ==        $Filearray[$fileloop][4] Then

_ArrayDelete($Filearray, $fileloop)
         ExitLoop 1
EndIf
   Next
Next

;insert data that is not already in the database(no duplicates)
$Fsize = UBound($Filearray)
If $Fsize > 1 Then
   For $i = 1 To UBound($Filearray) -1  ;$arrContent[0]
      $values = "INSERT INTO apps_table (id, name, location, version) VALUES("
      $querydata = $values & "''," & "'" & _MySQL_Real_Escape_String($MysqlConn,$Filearray[$i][2]) & "','" & _MySQL_Real_Escape_String($MysqlConn,$Filearray[$i][3]) & "','" & _MySQL_Real_Escape_String($MysqlConn,$Filearray[$i][4]) & "')"
      _MySQL_Real_Query($MysqlConn, $querydata)
      $errno = _MySQL_errno($MysqlConn)
   Next
Else
   msgbox(0, "", "No data")
EndIf

_MySQL_Free_Result($res)  ; free result
_MySQL_Close($MysqlConn)  ; Close connection
_MySQL_EndLibrary()  ; exit MYSQL
Link to comment
Share on other sites
charon Seeker

charon

Posted
  • Author
Posted

 

The db array would be:

Local $dbarray[3][4] = [ _

        ["id", "name", "location", "version"], _

        ["1", " Adobe AIR", "C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall", "2.6.0.19120"], _

        ["2", " Cake Mania", "C:Program Files (x86)WildTangentDell GamesCake ManiaUninstall.exe", "2.2.0.95"] _

Local $Filearray[6][4] = [ _

        ["not used", "not used", "name", "location","version"], _

        ["", "", " Adobe AIR", "C:Program Files (x86)Common FilesAdobe AIRVersions1.0ResourcesAdobe AIR Updater.exe -arp:uninstall", "2.6.0.19120"], _

 I noticed I have empty lines in the text file when I view it with notepadd++ instead of just notepad so I added StringStripCR to the code. Sample data. Yes the data changes but the columns are always 4: date I do not use, name I use, location used, and version is used for comparison.

[09.13. 08:23:12], AutoIt v3.3.8.1,C:AutoITAutoIt3Uninstall.exe,0

[09.13. 08:23:12], FileZilla Client 3.7.3,C:Program Files (x86)FileZilla FTP Clientuninstall.exe,3.7.3

[09.13. 08:23:12], Foxit Reader,"C:Program Files (x86)Foxit SoftwareFoxit Readerunins000.exe",5.4.5.124

Most important parts of code: 

Here is the file array which is pulled from text file:

$res = _FileReadToArray($CSVFILE, $arrContent)
$rowx = $arrContent[0] + 1

Dim $Filearray[$rowx][5]   ;Rows depend on how many lines are in the text file which will vary and columns are constant 5 
;ex data: [09.13. 08:23:12], AutoIt v3.3.8.1,C:\AutoIT\AutoIt3\Uninstall.exe,0   ;4 columns

If $res = 1 Then 
    For $row = 1 To $arrContent[0]   
        $arrLine = StringSplit(StringStripCR($arrContent[$row]), $DELIM)  ;just added StringStripCR
        For $Fcolumn = 2 To 4    ;Fcolumn represents fields in each line seperated by commas
            $Filearray[$row][$Fcolumn] = $arrLine[$Fcolumn]
        Next
    Next
Else
    MsgBox(48, "", "Error opening file!")
EndIf
 ;_ArrayDisplay($arr)
 
 
 ;Here is SQL data that loads the array
 $query = "SELECT * FROM apps_table" ; WHERE name = '" & _MySQL_Real_Escape_String($MysqlConn,$name[$x]) & "' AND location='" 
  $mysql_bool = _MySQL_Real_Query($MysqlConn, $query)

 ;MsgBox(0, '', "Access method 3 - read whole result in 2D-Array")
 $res = _MySQL_Store_Result($MysqlConn)
$dbarray = _MySQL_Fetch_Result_StringArray($res)

_ArrayDisplay($dbarray, "Database array:")

;sample database data:  
;~ [0]|id|name|location|version
;~ [1]|1| AutoIt v3.3.8.1|C:\AutoIT\AutoIt3\Uninstall.exe|0
;~ [2]|2| FileZilla Client 3.7.3|C:\Program Files (x86)\FileZilla FTP Client\uninstall.exe|3.7.3
;~ [3]|3| Foxit Reader|"C:\Program Files (x86)\Foxit Software\Foxit Reader\unins000.exe"|5.4.5.124

_ArrayDisplay($Filearray, "File array")
;~ [0]|||||
;~ [1]||| AutoIt v3.3.8.1|C:\AutoIT\AutoIt3\Uninstall.exe|0
;~ [2]||| FileZilla Client 3.7.3|C:\Program Files (x86)\FileZilla FTP Client\uninstall.exe|3.7.3
;~ [3]||| Foxit Reader|"C:\Program Files (x86)\Foxit Software\Foxit Reader\unins000.exe"|5.4.5.124
;~ [4]||| Mozilla Firefox 23.0.1 (x86 en-US)|"C:\Program Files (x86)\Mozilla Firefox\uninstall\helper.exe"|23.0.1
;~ [5]||| Mozilla Maintenance Service|"C:\Program Files (x86)\Mozilla Maintenance Service\uninstall.exe"|23.0.1
;~ [6]||| Notepad++|C:\Program Files (x86)\Notepad++\uninstall.exe|6.3.2

For $fileloop = UBound($Filearray)- 1 to 0 step -1
   For $dbloop = UBound($dbarray) - 1 to 0 step -1

 If $dbarray[$dbloop][1] == $Filearray[$fileloop][2] AND $dbarray[$dbloop][2] == $Filearray[$fileloop][3] AND $dbarray[$dbloop][3] ==        $Filearray[$fileloop][4] Then

_ArrayDelete($Filearray, $fileloop)
         ExitLoop 1
EndIf
   Next
Next

;insert data that is not already in the database(no duplicates)
$Fsize = UBound($Filearray)
If $Fsize > 1 Then
   For $i = 1 To UBound($Filearray) -1  ;$arrContent[0]
      $values = "INSERT INTO apps_table (id, name, location, version) VALUES("
      $querydata = $values & "''," & "'" & _MySQL_Real_Escape_String($MysqlConn,$Filearray[$i][2]) & "','" & _MySQL_Real_Escape_String($MysqlConn,$Filearray[$i][3]) & "','" & _MySQL_Real_Escape_String($MysqlConn,$Filearray[$i][4]) & "')"
      _MySQL_Real_Query($MysqlConn, $querydata)
      $errno = _MySQL_errno($MysqlConn)
   Next
Else
   msgbox(0, "", "No data")
EndIf

_MySQL_Free_Result($res)  ; free result
_MySQL_Close($MysqlConn)  ; Close connection
_MySQL_EndLibrary()  ; exit MYSQL

I think I found the problem. The database structure field for name was set to varchar(60), and location was varchar(120) and version was varchar(18) which is cutting off the data which is why some data is not matching up. I should have posted the table structure. Thank you everyone

Link to comment
Share on other sites
sahsanu Universalist

sahsanu

Posted
Posted

I think I found the problem. The database structure field for name was set to varchar(60), and location was varchar(120) and version was varchar(18) which is cutting off the data which is why some data is not matching up. I should have posted the table structure. Thank you everyone

 

Glad you've solved the problem ;)

Cheers,

sahsanu

Link to comment
Share on other sites
  • 4 weeks later...
Rogue5099 Universalist

Rogue5099

Posted
Posted

I know this topic is old but I found something that might would help finding duplicates:

; Identify duplicate values in arrays
; Author - gafrost

#include <Array.au3>

Dim $a1[3] = [1, 2, 3], $a2[5] = [5, 6, 7, 8, 9], $a3[2] = [3, 5]
Dim $a_a[4] = [3, $a1, $a2, $a3]
MsgBox(0, "Dupes?", _SearchForDupes($a_a))

Func _SearchForDupes($a_arrays, $i_ReturnType = 0)
    Local $Dupes[1] = [0]
    If $a_arrays[0] < 2 Then Return ''
    For $i = 1 To $a_a[0] - 1
        For $j = $i + 1 To $a_a[0]
            _FindDupe($a_a[$i], $a_a[$j], $Dupes)
        Next
    Next
    If $Dupes[0] = 0 Then Return ''
    _ArraySort($Dupes)
    If Not $i_ReturnType Then
        Local $s_return = ''
        For $x = 1 To $Dupes[0]
            $s_return &= $Dupes[$x] & ','
        Next
        Return StringTrimRight($s_return, 1)
    Else
        Return $Dupes
    EndIf

EndFunc   ;==>_SearchForDupes

Func _FindDupe(ByRef $a_array01, ByRef $a_array02, ByRef $Dupes)
    Local $found = False
    For $i = 0 To UBound($a_array01) - 1
        For $j = 0 To UBound($a_array02) - 1
            If $a_array01[$i] = $a_array02[$j] Then
                $found = False
                For $x = 1 To $Dupes[0]
                    If $Dupes[$x] = $a_array01[$i] Then
                        $found = True
                        ExitLoop
                    EndIf
                Next
                If Not $found Then
                    $Dupes[0] += 1
                    ReDim $Dupes[$Dupes[0] + 1]
                    $Dupes[$Dupes[0]] = $a_array01[$i]
                EndIf
            EndIf
        Next
    Next
EndFunc   ;==>_FindDupe

My projects: Inventory / Mp3 Inventory, Computer Stats

Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...