Sort and search question

notta · June 18, 2008

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,....

.

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,....

.

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

weaponx · 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?

notta · 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

weaponx · June 18, 2008

#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

notta · 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.

#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()

weaponx · 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.

weaponx · June 19, 2008

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.

#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

notta · June 20, 2008

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.

#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

weaponx · 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.

#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()

notta · June 20, 2008

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

Sort and search question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members