Table lookup

[cheesestain]

How would I go about extracting data from a table in a text file instead of using many If statements?

Right now I have a lot of If statements like so:

If $SKU = "N1234" Then
$Model = "Black"
$Version = "117"

It's not very flexible and I'd rather add the data to a table in a text file and not hard code it into the program.

I want to be able to search the table for a string in the SKU column and store the values in a variable from the other columns in the row.

If A exists in SKU column, I want to store B and C in a variable that can be called.

SKU MODEL VERSION

A B C

X Y Z

[Melba23]

cheesestain,

Reading the file into a 2D array should help you a lot - this should get you started.

M23

[DicatoroftheUSA]

Or learn sqlite (or mysql if you plan on simultaneous use/going online in the future), it has a fairly steep learning curve. But, it works very well.

If you don't have tons of data, check out the ini functions. you could have the sku be the section.

Edited December 26, 2012 by DicatoroftheUSA

[Malkey]

This regular expression method finds the line that starts with the search string. Then this method captures and returns :-

First, the data in the second column on the found line which is assigned to $sModel; and,

Second, the data in the third column on the found line which is assigned to $sVersion.

expandcollapse popup

; ------- Create Test Data -------
Local $sDat = "SKU           MODEL VERSION" & @CRLF
For $i = 1 To 10000
$sDat &= StringFormat("Line %04d\t%7s\t%7s\n", $i, $i & "\" & Random(0, 100, 1), Random(0, 100, 1) & "\" & $i)
Next
ConsoleWrite($sDat & @LF)
;Local $sDat = FileRead("tempData.txt")
;---> End of Create Test Data -----

Local $begin = TimerInit()
Local $sModel, $sVersion
Local $sFind = "Line 0100" ; "Line 9800" ;

; The data on each line should be separated by two or more spaces, or, a Tab.
; Searching for the contents of the $sFind variable is case-sensitive.- (There is no "(?i)" in any of the regular expression patterns.)
If StringRegExp($sDat, "(?sm).*^\h*\Q" & $sFind & "\E(?:\h*\t\h*|\h{2,})") Then
$sModel = StringRegExpReplace($sDat, "(?sm).*^\h*\Q" & $sFind & "\E(?:\h*\t\h*|\h{2,})(.+?)(?:\h*\t\h*|\h{2,}|\h*$).*", "\1")
$sVersion = StringRegExpReplace($sDat, "(?sm).*^\h*\Q" & $sFind & "\E(?:(?:\h*\t\h*|\h{2,}).*?){1}(?:\h*\t\h*|\h{2,})(.+?)(?:\h*\t\h*|\h{2,}|\h*$).*", "\1")
MsgBox(0, "Results", _
"Find = " & $sFind & @CRLF & _
"Model = " & $sModel & @CRLF & _
"Version = " & $sVersion & @CRLF & _
"Time taken = " & TimerDiff($begin) & " Millisecs")
Else
MsgBox(0, "Results", '"' & $sFind & '" not found in SKU column')
EndIf

#cs
Description of "(?sm).*^\h*\Q" & $sFind & "\E(?:\h*\t\h*|\h{2,})(.+?)(?:\h*\t\h*|\h{2,}|\h*$).*" :-
(?sm) :- (?s) - "." matches anything including newline; and, (?m) - ^ and $ match newlines within data.
.*   :- Match all lines up to the found line.
^\h*\Q" & $sFind & "\E :- Starting at the line that has the contents of the variable, $sFind.
Match any horizontal white spaces, if present.
"\Q" quote (disable) pattern metacharacters if any in $sFind till \E.
So, match actual characters in $sFind,
(?:\h*\t\h*|\h{2,}) :- and match any combination of two or more spaces within the non-capturing group.
(.+?)(?:\h*\t\h*|\h{2,}|\h*$) :- Capture the group of characters (suitable for back-referencing, "\1") until the next
                                 occurrence of two or more spaces or, possible space/s before the end of the found line.
.*       :- Match all characters and lines to end of string or file.
-------------------------------------------------
#ce

[cheesestain]

Thanks guys! I've used the ini functions suggested as this was the easiest for me to understand.

[kylomas]

cheesestain,

Just in case you're interested here's how this might have been done using SQLite.

expandcollapse popup

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
; *** End added by AutoIt3Wrapper ***

#include <sqlite.au3>

#AutoIt3Wrapper_Add_Constants=n

local $Parts_fl = @scriptdir & '\Parts.txt'
local $Parts_DB = @scriptdir & '\Parts.DB3'

;-------------------------------------------------------------------------------------
; generate test file of 5,000 comma delimited items, 3 entries (cols) per line (row)
;-------------------------------------------------------------------------------------

local $refresh = true

if $refresh then filedelete($Parts_fl)

if not fileexists($Parts_fl) then

    local $hfl = fileopen($Parts_fl,2)
    if $hfl = -1 then
        ConsoleWrite('File open failed' & @LF)
        Exit
    endif

    local $str_out

    for $1 = 1 to 5000
        $str_out &= stringformat('%05i,M%05s,V%05s\n',$1,$1 & '-' & random(1,999,1),$1 & '-' & random(1,999,1))
    Next

    filewrite($hfl,$str_out)
    fileclose($hfl)
    $hfl = 0

endif

;---------------------------------------------------------------------------------------
; initialize SQLite and open Parts DB
;---------------------------------------------------------------------------------------

local $sqlstrt = _SQLite_Startup(), $st = timerinit()

if @error then
    ConsoleWrite('error loading sqlite.dll' & @LF)
    Exit
EndIf

local $hmemDB = _sqlite_open($Parts_DB)

if @error then
    ConsoleWrite('Unable to open DB' & @LF)
    _Exit()
EndIf

if $refresh then _reload()

func _reload()

    if _sqlite_exec(-1,'drop table if exists parts;') <> $sqlite_ok then
        ConsoleWrite('Drop table failed' & @LF)
        _exit()
    Else
        ConsoleWrite('Parts table dropped for refresh' & @LF)
    endif

    if _sqlite_exec(-1,'create table parts (SKU, Model, Version);') <> $sqlite_ok then
        ConsoleWrite('Create Table Failed' & @LF)
        _exit()
    endif

    local $fl_array
    _filereadtoarray($Parts_fl,$fl_array)

    switch @error
        case 1
            ConsoleWrite('Input file failed to open' & @LF)
            _exit()
        case 2
            ConsoleWrite('Unable to split file' & @LF)
            _exit()
    EndSwitch

    local $aLine, $sql

    ProgressOn('Loading Parts Table','Please Wait')

    _SQLite_Exec(-1, "begin immediate;")

    for $1 = 1 to $fl_array[0]
        progressset(($1/$fl_array[0])*100)
        $aLine = stringsplit($fl_array[$1],',')
        $sql = 'insert into parts values ('
        for $2 = 1 to $aLine[0]
            $sql &= '"' & $aLine[$2] & '",'
        next
        $sql = stringtrimright($sql,1)
        $sql &= ');'

        if _sqlite_exec(-1,$sql) <> $sqlite_ok Then
            ConsoleWrite('Table insert failed STMT = ' & $sql & @LF)
            _exit()
        endif
    next

    _SQLite_Exec(-1, "commit;")

    progressoff()

    ConsoleWrite('Table loaded with ' & ubound($fl_array)- 1 & ' records in ' & round(timerdiff($st)/1000,3) & ' seconds' & @LF)

endfunc

;---------------------------------------------------------------------------------------
; display SKU query dialaog
;---------------------------------------------------------------------------------------

local $gui010 = guicreate('SKU Query Mini-APP Using SQLITE',300,170)
local $aSize  = wingetclientsize($gui010)
                guictrlcreatelabel('Enter SKU for Query',40,20,150,20)
                guictrlsetfont(-1,10,600)
                GUICtrlSetColor(-1,0xaa0000)
local $inp010 = guictrlcreateinput('',190,20,40,20)
local $lbl010 = guictrlcreatelabel('',70,60,250,50)
                guictrlsetfont(-1,10,600,-1,'Courier New')
                guictrlsetcolor(-1,0x000099)
local $btn010 = guictrlcreatebutton('Submit Query',10,$aSize[1]-30,$aSize[0]-20,20)
                guictrlsetfont(-1,9,600)
local $dmy010 = GUICtrlCreateDummy()
                guisetstate()

local $aAccelKeys[1][2] = [["{ENTER}", $dmy010]]
GUISetAccelerators($aAccelKeys)

local $aRow, $ret

while 1
    switch guigetmsg()
        case $gui_event_close
            _exit()
        case $btn010, $dmy010
            _disp()
    endswitch
wend

func _disp()

    $ret = _SQLite_QuerySingleRow(-1,'select SKU,Model,Version from parts where SKU = "' & stringformat('%05s',guictrlread($inp010)) & '";', $aRow)
    if $ret = $sqlite_ok then
        guictrlsetdata($lbl010,stringformat('%-10s%5s\n%-10s%5s\n%-10s%5s','SKU',$aRow[0],'Model',$aRow[1],'Version',$aRow[2]))
    Else
        guictrlsetdata($lbl010,guictrlread($inp010) & ' Not Found')
    endif
    guictrlsetstate($inp010,$gui_focus)

endfunc

func _exit()
    _SQLite_Close()
    _SQLite_Shutdown()
    exit
endfunc

kylomas