charon Posted September 21, 2013 Posted September 21, 2013 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
UEZ Posted September 21, 2013 Posted September 21, 2013 (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 September 21, 2013 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!¯\_(ツ)_/¯ ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ
jchd Posted September 21, 2013 Posted September 21, 2013 (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 September 21, 2013 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 hereRegExp tutorial: enough to get startedPCRE 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)
AZJIO Posted September 21, 2013 Posted September 21, 2013 @charon "Compare_strings" - '?do=embed' frameborder='0' data-embedContent>> My other projects or all
charon Posted September 21, 2013 Author Posted September 21, 2013 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
charon Posted September 21, 2013 Author Posted September 21, 2013 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?
sahsanu Posted September 22, 2013 Posted September 22, 2013 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
jchd Posted September 22, 2013 Posted September 22, 2013 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 hereRegExp tutorial: enough to get startedPCRE 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)
charon Posted September 22, 2013 Author Posted September 22, 2013 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 expandcollapse popupMost 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
charon Posted September 22, 2013 Author Posted September 22, 2013 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 expandcollapse popupMost 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
sahsanu Posted September 22, 2013 Posted September 22, 2013 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
Rogue5099 Posted October 18, 2013 Posted October 18, 2013 I know this topic is old but I found something that might would help finding duplicates: expandcollapse popup; 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
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now