m1975michael Posted January 9, 2014 Share Posted January 9, 2014 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. Link to comment Share on other sites More sharing options...
michaelslamet Posted January 9, 2014 Share Posted January 9, 2014 Hi, Take a look at: _FileReadToArray StringSplit _ArraySearch Link to comment Share on other sites More sharing options...
AdamUL Posted January 9, 2014 Share Posted January 9, 2014 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 Link to comment Share on other sites More sharing options...
Malkey Posted January 10, 2014 Share Posted January 10, 2014 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 Link to comment Share on other sites More sharing options...
m1975michael Posted January 10, 2014 Author Share Posted January 10, 2014 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now