Array csv File Search

[m1975michael]

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.

[michaelslamet]

Hi,

Take a look at:

_FileReadToArray

StringSplit

_ArraySearch

[AdamUL]

Here are some links to some CSV UDFs that can read the file into a 2D array and write it back to a file.  

'?do=embed' frameborder='0' data-embedContent>>

 

Adam

[Malkey]

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.

expandcollapse popup

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

[m1975michael]

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

Edited January 10, 2014 by m1975michael