Sign in to follow this  
Followers 0
cheesestain

Table lookup

6 posts in this topic

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

Share this post


Link to post
Share on other sites



cheesestain,

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

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by DicatoroftheUSA

Share this post


Link to post
Share on other sites

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.

; ------- 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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

cheesestain,

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

; *** 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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
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
Sign in to follow this  
Followers 0