Sign in to follow this  
Followers 0
m1975michael

Array csv File Search

5 posts in this topic

I have a csv file that has 4 columns and about 550 rows. The rows will likely increase over time.   The csv file is delimited by a comma.  I need a script that I can load the csv file and then be able to search one of the columns and then return data from another column in the same row.  I believe I need an Array to do this but I am not sure where to start.  I am new to autoIT and any assistance would be greatly appreciated.  Thank you. :)

Share this post


Link to post
Share on other sites



Hi,

Take a look at:

_FileReadToArray

StringSplit

_ArraySearch

Share this post


Link to post
Share on other sites

The convention method is to read the CSV file into a 2D array.

Then use _ArraySearch to find the array's row index.
       e.g. Local $iIndex = _ArraySearch($The2DArray, $sSearch, 0, 0, 0, 0, 1, 1)

Then, from the 2D array get the value in $The2DArray[$iIndex][Required column index to be returned].


This example makes use of StringRegExpReplace. Once the regular expression pattern is created, the desired row-column value is returned.
The time taken to return the correct value is about 1 millisecond.

;---- Create Test Data -------
Local $File = ""
For $i = 1 To 600
    $File &= "Row" & $i & "Column1,R" & $i & "C2,R" & $i & "C3,R" & $i & "C4" & @CRLF
Next
$File &= StringTrimRight($File, 2)
;----> End of Create Test Data -------
;Or
;$File = FileRead("FullPathName.csv")

Local $hTimer = TimerInit()
Local $sFind = "r580C2"
Local $iSearchColNum = 2
Local $iReturnColNum = 4

ConsoleWrite('Search for "' & $sFind & _
        '" in column #' & $iSearchColNum & "." & @CRLF & _
        "Column #" & $iReturnColNum & _
        ', on same line/row, contains "' & _CSV_VLookUp($sFind, $File, $iSearchColNum, $iReturnColNum, 0, ",") & '".' & @LF)
ConsoleWrite("Time taken: " & Round(TimerDiff($hTimer) / 1000, 3) & " secs" & @LF)
ConsoleWrite("----------------------------" & @LF)
;ConsoleWrite($File & @LF)


; Description:    Within tne CSV (Coma Separated Values) formated string, $sFile, search vertically down the column $iSearchColNum
;                 for $sFind.  Then, on the same line or row, returns the contents in the specified column in $iReturnColNum.
; $CaseSensitive: For search case sensitivity, default is "1" for True, case-sensitive; Use "0" for false, case-insensitive.
; Success:        Returns value of appropiate column of the row in which the search value was found.
; Failure:        If the search value is not found in the search column of the CSV string then "Search Not Found" is returned.
;
Func _CSV_VLookUp($sFind, $sFile, $iSearchColNum, $iReturnColNum, $CaseSensitive = 1, $sDelimiter = ",")
    ;---- Create RE Pattern -------
    StringReplace(StringRegExpReplace($sFile, "(?s)^(\V+).*$", "\1"), $sDelimiter, "") ; To find number of comas in first line of csv file.
    Local $iNumOfCols = @extended + 1 ; Number of columns in first row (line).
    ;ConsoleWrite("$iNumOfCols  " &  $iNumOfCols & @LF)
    Local $sPattern = '(?ism)\A.*?^'
    If $CaseSensitive Then $sPattern = '(?sm)\A.*?^'
    For $i = 1 To $iNumOfCols
        If $iSearchColNum = $i Then
            $sPattern &= "(" & $sFind & ")" ; $sFind must be the entire column value being searched for. If partial column value then
            ;$sPattern &= "([^" & $sDelimiter & "]*" & $sFind & "[^" & $sDelimiter & "]*)" ; this command line should be used instead.
        ElseIf $i = $iNumOfCols Or $i = 1 Then
            $sPattern &= "([^" & $sDelimiter & "\v]*)"
        Else
            $sPattern &= "([^" & $sDelimiter & "]*)"
        EndIf
        If $i <> $iNumOfCols Then $sPattern &= $sDelimiter
    Next
    $sPattern &= '$.*\z'
    ;ConsoleWrite($sPattern & @LF)
    ;----> End of Create RE Pattern -------

    Local $Ret = StringRegExpReplace($sFile, $sPattern, "$" & $iReturnColNum)
    If StringInStr($Ret, $sDelimiter) = 0 Then
        Return $Ret
    Else
        Return "Search Not Found"
    EndIf
EndFunc   ;==>_CSV_VLookUp

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Thank you everyone for your replies.  I will be working on this.  I am very much a novice especially with arrays.

Edited by m1975michael

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