notta Posted June 18, 2008 Share Posted June 18, 2008 (edited) What's the most efficient way of sorting an array with 3500 lines, each element with a comma delimited string. Let's say I have an array in the form of: firstname,lastname,serialnumber,room,building,.... firstname,lastname,serialnumber,room,building,.... firstname,lastname,serialnumber,room,building,.... firstname,lastname,serialnumber,room,building,.... . . . I need to sort the array by the 3rd element in the string and do my search on that column. If there is a match I need all the data in that element(string). I thought about just sorting the current array(based on firstname) and then creating a new array with the 1st and 3rd element swapped. serialnumber,lastname,firstname,room,building,.... serialnumber,lastname,firstname,room,building,.... serialnumber,lastname,firstname,room,building,.... serialnumber,lastname,firstname,room,building,.... . . . Then just use _arraysort() again and search for the serialnumber. This is very inefficient, so is there a better way to accomplish this? Thanks. Edited June 18, 2008 by notta Link to comment Share on other sites More sharing options...
weaponx Posted June 18, 2008 Share Posted June 18, 2008 Why does a period separate lastname and serialnumber? Is that how the data really looks or is that just a typing error? Link to comment Share on other sites More sharing options...
notta Posted June 18, 2008 Author Share Posted June 18, 2008 Why does a period separate lastname and serialnumber? Is that how the data really looks or is that just a typing error?Sorry about that. I just typed the example and fat fingered it Link to comment Share on other sites More sharing options...
weaponx Posted June 18, 2008 Share Posted June 18, 2008 (edited) expandcollapse popup#include <sqlite.au3> #include <sqlite.dll.au3> Dim $aResult, $iRows, $iColumns, $iRval Dim $hQuery,$aRow Dim $columns = 'firstname,lastname,serialnumber,room,building' ;Sample data - Replace with your existing array Dim $data[4] $data[0] = "John,Smith,123456,15,North" $data[1] = "Peyton,Manning,223456,4,West" $data[2] = "George,Bush,323456,3,South" $data[3] = "Abraham,Lincoln,423456,1,East" ;Load SQL dll _SQLite_Startup() _SQLite_Open() ;Create table $SQL = StringFormat("CREATE TABLE tblData (%s);",$columns) _SQLite_Exec(-1,$SQL) ;Populate table from array For $X = 0 to Ubound($data)-1 ;Encapsulate all values with quotes $result = StringRegExpReplace($data[$X],"(\w+)", "'$0'") $SQL = StringFormat("INSERT INTO tblData VALUES (%s);",$result) _SQLite_Exec(-1,$SQL) Next ;Retrieve table sorted by serial number descending $SQL = "SELECT * FROM tblData ORDER BY serialnumber DESC" $iRval = _SQLite_GetTable2d (-1, $SQL, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _SQLite_Display2DResult($aResult) Else MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ()) EndIf _SQLite_Close() _SQLite_Shutdown() Edited June 18, 2008 by weaponx Link to comment Share on other sites More sharing options...
notta Posted June 18, 2008 Author Share Posted June 18, 2008 Very nice weapon. This actually fills a need to get rid of my csv file based database system down the road. Learning how to make the data viewable through a web browser is the only reason why I haven't gone that route yet. Just something else to learn. I'm not too sure how I can incorporate this into my current script, but I'll look into it. Thanks again. expandcollapse popup#include <sqlite.au3> #include <sqlite.dll.au3> Dim $aResult, $iRows, $iColumns, $iRval Dim $hQuery,$aRow Dim $columns = 'firstname,lastname,serialnumber,room,building' ;Sample data - Replace with your existing array Dim $data[4] $data[0] = "John,Smith,123456,15,North" $data[1] = "Peyton,Manning,223456,4,West" $data[2] = "George,Bush,323456,3,South" $data[3] = "Abraham,Lincoln,423456,1,East" ;Load SQL dll _SQLite_Startup() _SQLite_Open() ;Create table $SQL = StringFormat("CREATE TABLE tblData (%s);",$columns) _SQLite_Exec(-1,$SQL) ;Populate table from array For $X = 0 to Ubound($data)-1 ;Encapsulate all values with quotes $result = StringRegExpReplace($data[$X],"(\w+)", "'$0'") $SQL = StringFormat("INSERT INTO tblData VALUES (%s);",$result) _SQLite_Exec(-1,$SQL) Next ;Retrieve table sorted by serial number descending $SQL = "SELECT * FROM tblData ORDER BY serialnumber DESC" $iRval = _SQLite_GetTable2d (-1, $SQL, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _SQLite_Display2DResult($aResult) Else MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ()) EndIf _SQLite_Close() _SQLite_Shutdown() Link to comment Share on other sites More sharing options...
weaponx Posted June 18, 2008 Share Posted June 18, 2008 If you need further help you can PM me. I can help you with PHP/MySQL. You can also use AutoIt to query MySQL as well. Link to comment Share on other sites More sharing options...
weaponx Posted June 19, 2008 Share Posted June 19, 2008 (edited) You really should go with a centralized SQL or MySQL server, it would be very easy to import all of your flat files. Querying a SQL database is a million times faster than using flat files. The SQLite in AutoIt can work with database files but the example I created only uses a virtual database in memory. This will support spaces and special characters in your array. expandcollapse popup#include <sqlite.au3> #include <sqlite.dll.au3> Dim $aResult, $iRows, $iColumns, $iRval Dim $hQuery,$aRow Dim $columns = 'firstname,lastname,serialnumber,room,building' ;Sample data - Replace with your existing array Dim $data[4] $data[0] = "John,O'Flannigan,123456,15,North - Main" $data[1] = "Peyton,Manning,223456,4 B,West" $data[2] = "Mary Ann,Margaret,323456,3,South" $data[3] = "Abraham,Lincoln,423456,1,East" ;Load SQL dll _SQLite_Startup() _SQLite_Open() ;Create table $SQL = StringFormat("CREATE TABLE tblData (%s);",$columns) _SQLite_Exec(-1,$SQL) ;Populate table from array For $X = 0 to Ubound($data)-1 ;Escape single quotes $result = StringReplace($data[$X],"'", "''") ;Encapsulate all values with single quotes $result = StringRegExpReplace($result,"[^,]+", "'$0'") $SQL = StringFormat("INSERT INTO tblData VALUES (%s);",$result) _SQLite_Exec(-1,$SQL) Next ;Retrieve table sorted by serial number descending $SQL = "SELECT * FROM tblData ORDER BY serialnumber DESC" $iRval = _SQLite_GetTable2d (-1, $SQL, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _SQLite_Display2DResult($aResult) Else MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ()) EndIf _SQLite_Close() _SQLite_Shutdown() Edited June 19, 2008 by weaponx Link to comment Share on other sites More sharing options...
notta Posted June 20, 2008 Author Share Posted June 20, 2008 (edited) Weapon, I have been fooling around with this and I'm having a problem. It seems that this is failing on inserting the data into the table on lines that have empty cells. The spreadsheet I'm trying to read in has some cells that are empty. I have no control over that. If I view $result after $result = StringRegExpReplace($result,"[^,]+", "'$0'") it has everything encapsulated in ' ' except for the empty values. $results looks like this on the failed lines: 'firstname','lastname','serialnumber','room',,'model','id'.... Here is the error message "--> Error: near ",": syntax error" Once again, this looks like it only fails on the lines that have empty values. You really should go with a centralized SQL or MySQL server, it would be very easy to import all of your flat files. Querying a SQL database is a million times faster than using flat files. The SQLite in AutoIt can work with database files but the example I created only uses a virtual database in memory. This will support spaces and special characters in your array. expandcollapse popup#include <sqlite.au3> #include <sqlite.dll.au3> #include <array,au3> Dim $aResult, $iRows, $iColumns, $iRval Dim $hQuery,$aRow Dim $columns = 'firstname,lastname,serialnumber,room,phone,model,id' _fileReadToArray("list.csv",$data) ;Load SQL dll _SQLite_Startup() _SQLite_Open() ;Create table $SQL = StringFormat("CREATE TABLE tblData (%s);",$columns) _SQLite_Exec(-1,$SQL) ;Populate table from array For $X = 0 to Ubound($data)-1 ;Escape single quotes $result = StringReplace($data[$X],"'", "''") ;Encapsulate all values with single quotes $result = StringRegExpReplace($result,"[^,]+", "'$0'") $SQL = StringFormat("INSERT INTO tblData VALUES (%s);",$result) _SQLite_Exec(-1,$SQL) Next ;Retrieve table sorted by serial number descending $SQL = "SELECT * FROM tblData ORDER BY serialnumber DESC" $iRval = _SQLite_GetTable2d (-1, $SQL, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _SQLite_Display2DResult($aResult) Else MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ()) EndIf _SQLite_Close() _SQLite_Shutdown() Edited June 20, 2008 by notta Link to comment Share on other sites More sharing options...
weaponx Posted June 20, 2008 Share Posted June 20, 2008 Well I was trying to increase speed by using StringRegExp to magically add quotes. Instead we will do it the old fashioned way, with StringSplit + For...Next. expandcollapse popup#include <sqlite.au3> #include <sqlite.dll.au3> Dim $aResult, $iRows, $iColumns, $iRval Dim $hQuery,$aRow Dim $columns = 'firstname,lastname,serialnumber,room,building' ;Sample data - Replace with your existing array Dim $data[4] $data[0] = "John,O'Flannigan,123456,15,North - Main" $data[1] = "Peyton,Manning,223456,4 B,West" $data[2] = "Mary Ann,Margaret,323456,,South" $data[3] = "Abraham,Lincoln,423456,1,East" ;Load SQL dll _SQLite_Startup() _SQLite_Open() ;Create table $SQL = StringFormat("CREATE TABLE tblData (%s);",$columns) _SQLite_Exec(-1,$SQL) ;Populate table from array For $X = 0 to Ubound($data)-1 ;Escape single quotes $data[$X] = StringReplace($data[$X],"'", "''") ;NEW METHOD ;Split on comma $Split = StringSplit($data[$X], ",") $result = "" ;Encapsulate all values with single quotes For $Y = 1 to $Split[0] ConsoleWrite($Split[$Y] & @CRLF) $Split[$Y] = "'" & $Split[$Y] & "'" ;Build value string (basically re-adding the commas that we split on before) $result &= $Split[$Y] & "," Next ;Strip trailing comma $result = StringTrimRight($result,1) ;OLD METHOD ;Encapsulate all values with single quotes ;$result = StringRegExpReplace($result,"[^,]+", "'$0'") $SQL = StringFormat("INSERT INTO tblData VALUES (%s);",$result) _SQLite_Exec(-1,$SQL) Next ;Retrieve table sorted by serial number descending $SQL = "SELECT * FROM tblData ORDER BY serialnumber DESC" $iRval = _SQLite_GetTable2d (-1, $SQL, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _SQLite_Display2DResult($aResult) Else MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ()) EndIf _SQLite_Close() _SQLite_Shutdown() Link to comment Share on other sites More sharing options...
notta Posted June 20, 2008 Author Share Posted June 20, 2008 (edited) Yes, that worked. Thanks. I did see some error messages while it was flying through. It seems some fields have commas in them so the script was complaining that there were 24 columns, but 26 values. I guess I could do a StringInStr check and just parse after the first comma so I don't run into that problem. My original post about was to find a way to search through a spreadsheet to find if a serial number was already in another database not managed by us. We can't connect to their database so we export their large database into a spreadsheet every week. I'm sure this is more involved, but can I create the database with what you just posted and have my script connect from out in the field to search for the serial number? [edit] - hmm, gave it some more thought after I posted. I guess that's where the word Server comes in for SQL Server. I would have to either FileInstall it with my script or connect to a share and bring it down and then read from it locally huh? Edited June 20, 2008 by notta Link to comment Share on other sites More sharing options...
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