Jump to content

Recommended Posts

Posted

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

  • Moderators
Posted

cheesestain,

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

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see 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

 

Posted

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
Posted

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

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...