Jump to content
Sign in to follow this  

Array csv File Search

Recommended Posts


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


Take a look at:




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
$File &= StringTrimRight($File, 2)
;----> End of Create Test Data -------
;$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]*)"
            $sPattern &= "([^" & $sDelimiter & "]*)"
        If $i <> $iNumOfCols Then $sPattern &= $sDelimiter
    $sPattern &= '$.*\z'
    ;ConsoleWrite($sPattern & @LF)
    ;----> End of Create RE Pattern -------

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

Share this post

Link to post
Share on other sites

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  


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.