CSV search... left lookup?
CSV search... left lookup?
1,a,b,c
2,d,e,f
3,g,h,j
Is there a way to search a CSV file and return the left values?
ie...search for "c" and if found return 1,a,b ? or even b,a,1?
bonus question!....a search for "g" ... is it possible to return 3,h,j ?
Thanks for your help!
2,d,e,f
3,g,h,j
Is there a way to search a CSV file and return the left values?
ie...search for "c" and if found return 1,a,b ? or even b,a,1?
bonus question!....a search for "g" ... is it possible to return 3,h,j ?
Thanks for your help!
Re: CSV search... left lookup?
Code: Select all
file = %A_ScriptDir%\file.csv
MsgBox, 64, Left from "c", % left(file, "c")
MsgBox, 64, Left from "g", % left(file, "g")
left(file, find) {
Loop, Read, %file%
{ line := [], found := 0
Loop, Parse, A_LoopReadLine, CSV
line.Push(A_LoopField), A_LoopField = find && found := A_Index
If !found
Continue
For each, cell in line
each != found && str .= (str > "" ? "," : "") cell
Return str
}
}
Re: CSV search... left lookup?
@mikeyww Thank you for this!
A follow-up question?.... Can the return be put into individual item variables?
search "c" ... and return...
var1 = %item1%
var2 = %item2%
var3 = %item3%
Or do I StringSplit the return?
Thank you!
A follow-up question?.... Can the return be put into individual item variables?
search "c" ... and return...
var1 = %item1%
var2 = %item2%
var3 = %item3%
Or do I StringSplit the return?
Thank you!
Re: CSV search... left lookup?
I'm not sure what you mean by that. Loop, Read loops through each line. Your post indicates that you would like to search for a field's contents regardless of its position in the line.
Re: CSV search... left lookup?
You can have the function return the result as an array so then you can address individual elements of the result:
Code: Select all
file = %A_ScriptDir%\file.csv
resultArr := left(file, "c")
for k, v in resultArr
MsgBox, % "Item " k " left from 'c': " v
Return
left(file, find) {
Loop, Read, %file%
{ line := [], found := 0
Loop, Parse, A_LoopReadLine, CSV
line.Push(A_LoopField), A_LoopField = find && found := A_Index
If !found
Continue
For each, cell in line
each != found && str .= (str > "" ? "," : "") cell
Return Strsplit(str, ",")
}
}
Here it is as a stand-alone demo that uses a string as input instead of reading it from a file:
Code: Select all
text =
(
1,a,b,d,c
2,d,e,f
3,g,h,j
)
resultArr := left(text, "c")
for k, v in resultArr
MsgBox, % "Item " k " left from 'c': " v
Return
left(text, find) {
Loop, Parse, text, `n
{ line := [], found := 0
Loop, Parse, A_LoopField, CSV
line.Push(A_LoopField), A_LoopField = find && found := A_Index
If !found
Continue
For each, cell in line
each != found && str .= (str > "" ? "," : "") cell
Return Strsplit(str, ",")
}
}
Code: Select all
FileRead, text, % A_ScriptDir "\file.csv"
Re: CSV search... left lookup?
Code: Select all
#SingleInstance, Force
FileRead, content, file.csv
toFind :="c"
toShow := [3,2,1] ; show returned items in this order
content := " ; remove this block after testing so the 'FileRead' output is used instead.
(
1,a,b,c
2,d,e,f
3,c,h,i
4,a,t,c
)"
Loop, Parse, content, `n ; parse content using line breaks
InStr(A_LoopField, tofind) ? i:= StrSplit(A_LoopField,",") : "" ; push last line (in case there are multiple occurences) that contains the search pattern) into an array
Loop % i.Count()
res .= i[toShow[A_Index]] ; create output in order that is specified in 'toShow'
MsgBox % res
-
- Posts: 4344
- Joined: 29 Mar 2015, 09:41
- Contact:
Re: CSV search... left lookup?
Or like this:
Code: Select all
csv =
(
1,a,b,c
2,d,e,f
3,g,h,j
)
arr := Left(csv, "g")
MsgBox, % arr[1] . " " . arr[2] . " " . arr[3]
arr := Left(csv, "c")
MsgBox, % arr[1] . " " . arr[2] . " " . arr[3]
Left(csv, item) {
RegExMatch(csv, "m`a)^(?=\V*\Q" . item . "\E)\V*$", m)
l := RegExReplace(m, "(,)?\Q" . item . "\E(?(1)|,)")
Return StrSplit(l, ",")
}
-
- Posts: 4344
- Joined: 29 Mar 2015, 09:41
- Contact:
Re: CSV search... left lookup?
What if you don't know which place toFind takes?
Re: CSV search... left lookup?
If you parse your csv into an object; https://biga-ahk.github.io/biga.ahk/#/?id=find can find it; easiest if you know what column you are searching in.
Code: Select all
A := new biga() ; requires https://www.npmjs.com/package/biga.ahk
csv := [["a", "b", "c"]
,["d", "e", "f"]
,["g", "h", "i"]]
; find a row that has a "c" in the third collumn
wholerow := A.find(csv, {3: "c"})
; => ["a", "b", "c"]
; find a row that has an "e" in the second collumn
wholerow := A.find(csv, {2: "e"})
; => ["d", "e", "f"]
Re: CSV search... left lookup?
Thank you @mikeyww & also @boiler for the Strsplit.
Here's what I pieced together....
Thanks everyone for the posts and ideas. I love this stuff!
Here's what I pieced together....
Code: Select all
find := "c"
Loop, Read, file = %A_ScriptDir%\file.csv
{ line := [], found := 0
Loop, Parse, A_LoopReadLine, CSV
line.Push(A_LoopField), A_LoopField = find && found := A_Index
If !found
Continue
For each, cell in line
each != found && str .= (str > "" ? "," : "") cell
found := Strsplit(str, ",")
var1:= found[1]
var2:= found[2]
var3:= found[3]
}
MsgBox % var1 " " var2 " " var3
Thanks everyone for the posts and ideas. I love this stuff!
Re: CSV search... left lookup?
Is there a particular reason you structured it without using the function left() as mikeyww originally set it up? The function allows you to perform repeated searches without having to repeat the code. You just make a new function call with the new search info. Notice how in mikeyww's original post, he performed two different searches by calling the function twice. You can't do that with the way you've changed it.
Re: CSV search... left lookup?
Runs faster without passing the variables. I'm joking of course . For this help I realized after I saw mikeyww's solution that I only need to run it once. Thanks for your help too boiler. And all the other posts. I really get to learn from seeing all the different solutions.