CSV search... left lookup?

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
ZipZip
Posts: 23
Joined: 08 Nov 2021, 13:21

CSV search... left lookup?

Post by ZipZip » 27 Jan 2022, 06:20

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!

User avatar
mikeyww
Posts: 26592
Joined: 09 Sep 2014, 18:38

Re: CSV search... left lookup?

Post by mikeyww » 27 Jan 2022, 07:36

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

ZipZip
Posts: 23
Joined: 08 Nov 2021, 13:21

Re: CSV search... left lookup?

Post by ZipZip » 27 Jan 2022, 11:21

@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!

User avatar
mikeyww
Posts: 26592
Joined: 09 Sep 2014, 18:38

Re: CSV search... left lookup?

Post by mikeyww » 27 Jan 2022, 11:28

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.

User avatar
boiler
Posts: 16767
Joined: 21 Dec 2014, 02:44

Re: CSV search... left lookup?

Post by boiler » 27 Jan 2022, 12:07

ZipZip wrote: Or do I StringSplit the return?
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, ",")
 }
}
Without using a for-loop, you can address the individual elements as resultArr.1, resultArr.2, etc. The number of items in the result is found by resultArr.Count().


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, ",")
 }
}
You could use this version with a file as well by replacing the assignment to the var text with:

Code: Select all

FileRead, text, % A_ScriptDir "\file.csv"

BoBo
Posts: 6564
Joined: 13 May 2014, 17:15

Re: CSV search... left lookup?

Post by BoBo » 27 Jan 2022, 12:21

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
This will show the result based on the last found occurrence of the search pattern, so there's still room for improvement. Have phun :)

teadrinker
Posts: 4309
Joined: 29 Mar 2015, 09:41
Contact:

Re: CSV search... left lookup?

Post by teadrinker » 27 Jan 2022, 12:25

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, ",")
}

teadrinker
Posts: 4309
Joined: 29 Mar 2015, 09:41
Contact:

Re: CSV search... left lookup?

Post by teadrinker » 27 Jan 2022, 12:30

BoBo wrote:

Code: Select all

toFind	:="c"
toShow := [3,2,1]
What if you don't know which place toFind takes?

User avatar
Chunjee
Posts: 1400
Joined: 18 Apr 2014, 19:05
Contact:

Re: CSV search... left lookup?

Post by Chunjee » 27 Jan 2022, 13:55

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"]

ZipZip
Posts: 23
Joined: 08 Nov 2021, 13:21

Re: CSV search... left lookup?

Post by ZipZip » 27 Jan 2022, 15:45

Thank you @mikeyww & also @boiler for the Strsplit.

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! :D

User avatar
boiler
Posts: 16767
Joined: 21 Dec 2014, 02:44

Re: CSV search... left lookup?

Post by boiler » 27 Jan 2022, 15:53

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.

ZipZip
Posts: 23
Joined: 08 Nov 2021, 13:21

Re: CSV search... left lookup?

Post by ZipZip » 28 Jan 2022, 05:30

Runs faster without passing the variables. :lol: 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.

Post Reply

Return to “Ask for Help (v1)”