Page 1 of 1

CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 22 Jul 2017, 15:28
by ahk7
CSV Quick Filter (CSVQF) - https://github.com/hi5/CSVQF/

This program allows you to load a CSV file (any delimited file) and use various search criteria to filter the listview. You can export the results to a new file.

Screenshot
csvqf-inaction.gif
csvqf-inaction.gif (43.93 KiB) Viewed 5857 times
Code was no longer available due to loss of autohotkey . net website - CSVQF code itself not updated apart from new links to resources
Makes use of CSV (trueski, kdoske) - Attach (majkinetor) - OnChangeMyText (jsherk)

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 22 Jul 2017, 16:56
by Delta Pythagorean
This deems extremely useful for what I have planned for the future! Thank you very much!

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 23 Jul 2017, 06:13
by ahk7
Perhaps one day I will rewrite the script from scratch removing the need to rely on the CSV lib and Attach and reduce the code size (more similar to pAHKlight) - this was a quick hack from the start adding things quickly when I needed it at the time. But it works for me so I don't feel compelled to "fix it"

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 23 Jul 2017, 18:34
by fenchai
AWESOME WORK! is there a way to filter results by using wildcards? regex is too complex for fast searches...

Lke when I am searching for a car model "TOYOTA CAMRY ENGINE 03 2013"

I can simply search on excel by typing something like "toy*03*13" and it should filter to it.

Is there a way I can do this with your program? because it is very fast and I LIKE IT! just missing this feature...

Also how about adding the ENTER KEY to paste first column value to whatever program is after closing the GUI?

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 24 Jul 2017, 06:59
by ahk7-nli
fenchai wrote:... I can simply search on excel by typing something like "toy*03*13" and it should filter to it.
You already can, just don't type * but a space, so "toy 03 13" will find your toyota. You can also type them in any order so "2013 toy" works, as will "03 to ca 201" for example.
fenchai wrote:Also how about adding the ENTER KEY to paste first column value to whatever program is after closing the GUI?
you can experiment with it, Replace or comment Lines 450-478 with this code

Code: Select all

Enter::
ControlGetFocus, CurrCtrl, %AppWindow%
IfEqual, CurrCtrl, Edit1
	{
	 Gui, Submit, NoHide
	 SelItem := LV_GetNext()
	 If (SelItem = 0)
		SelItem = 1
	 LV_GetText(CellData, SelItem, 1)
	 clipboard:=celldata
	 ExitApp
Return
Now pressing enter would save cell 1,1 in the listview to the clipboard.
If you do a Gui, Destroy + Sleep + and Send ^v just before the ExitApp it should paste it.

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 24 Jul 2017, 13:13
by fenchai
ahk7-nli wrote:
fenchai wrote:... I can simply search on excel by typing something like "toy*03*13" and it should filter to it.
You already can, just don't type * but a space, so "toy 03 13" will find your toyota. You can also type them in any order so "2013 toy" works, as will "03 to ca 201" for example.
fenchai wrote:Also how about adding the ENTER KEY to paste first column value to whatever program is after closing the GUI?
you can experiment with it, Replace or comment Lines 450-478 with this code

Code: Select all

Enter::
ControlGetFocus, CurrCtrl, %AppWindow%
IfEqual, CurrCtrl, Edit1
	{
	 Gui, Submit, NoHide
	 SelItem := LV_GetNext()
	 If (SelItem = 0)
		SelItem = 1
	 LV_GetText(CellData, SelItem, 1)
	 clipboard:=celldata
	 ExitApp
Return
Now pressing enter would save cell 1,1 in the listview to the clipboard.
If you do a Gui, Destroy + Sleep + and Send ^v just before the ExitApp it should paste it.
I see but I still think this won't do it :/

I have a pretty large data of 12 columns and a lot of rows of data. When I search a code like "22020" it shows me all other rows that do not contain "22020" in the row until I press Space it works! which is a bit annoying but it's not deal breaker for me but my uncle won't remember it and that worries me a lot if he can't find the data because he did not press the space lol.

what is mute check/uncheck for?

Also when searching and multiple rows show up I try to select 1 from the many but it only copies cell 1,1 which makes it a bit useless for my use. I am pretty sure you know how to do make it work to select from the 1st column :D

Also, can I increase font size? I am pretty I can mess with the code later but just asking here to see :)

btw how about directly opening the .csv file instead of selecting it all the time? I think I can also mess with that in the code but just to make sure.

thank you for your work!

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 24 Jul 2017, 13:56
by ahk7
fenchai wrote:When I search a code like "22020" it shows me all other rows that do not contain "22020" in the row until I press Space it works!
I don't think so, when I do that, typing the last 0 already shows me the results with only 22020 it, unless you type very fast. It updates the listview with each character you type no need to press space to activate the search. If it happens often: there is a line "If (A_TickCount - StartTime > 250)" reduce the 250 to 100 or 50 and see if that helps.
fenchai wrote:what is mute check/uncheck for?
I have some very large CSV files to search, searching can take a few seconds and when it is actually ready it plays a sound. If you don't like the sound you can turn it off by ticking the mute check box.
fenchai wrote:Also when searching and multiple rows show up I try to select 1 from the many but it only copies cell 1,1 which makes it a bit useless for my use. I am pretty sure you know how to do make it work to select from the 1st column :D
Do you mean with the above example code for Enter I gave? It works here but you must use the keyboard so use the UP/DOWN arrows to highlight a row, press enter and it should get the info from that row (column 1) as it uses "SelItem := LV_GetNext()", the "1" in the LV_GetText line below it means Column 1. So it should get 3,1 if you highlighted Row 3. Nothing happens if you select a line with the mouse first and then press Enter because of the "IfEqual, CurrCtrl, Edit1" - the focus needs to be on the "find text" edit control (where you type) - you can try to remove that if you don't like it.
fenchai wrote:Also, can I increase font size? I am pretty I can mess with the code later but just asking here to see :)
Above the Gui, Add, Listview insert a line like "Gui, Font, s15, Arial" to increase the font size of the listview.
fenchai wrote:btw how about directly opening the .csv file instead of selecting it all the time?
You can use the command line options, create a batch file with "csv.ahk myfile.csv" for example and start it. What you can also do (which is what I've done) is associate CSV files with CSVQF.AHK (or you could give the files you want to use with CSVQF another extension, myfile.CQF for example, if you double click it windows will ask which program, select CSVQF.AHK and from then on it will launch it.

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 24 Jul 2017, 14:32
by burque505
This will certainly come in handy for my purposes, thank you. It loaded roughly 6000 simple address records with 10 columns per row in less than a second on my Win7 64-bit machine. Filtering is fast.

burque505

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 24 Jul 2017, 18:31
by fenchai
ahk7 wrote:I don't think so, when I do that, typing the last 0 already shows me the results with only 22020 it, unless you type very fast. It updates the listview with each character you type no need to press space to activate the search. If it happens often: there is a line "If (A_TickCount - StartTime > 250)" reduce the 250 to 100 or 50 and see if that helps.
AWESOME that was it! thanks for the reminder, I did not know that it worked that way, I always type very fast, I tried reducing to 10 - 50 and it still did not register. lol but I now know how it works so I just need to slow down a bit. It's OK! works very well.
ahk7 wrote:I have some very large CSV files to search, searching can take a few seconds and when it is actually ready it plays a sound. If you don't like the sound you can turn it off by ticking the mute check box.
I seem I know now.
ahk7 wrote: Do you mean with the above example code for Enter I gave? It works here but you must use the keyboard so use the UP/DOWN arrows to highlight a row, press enter and it should get the info from that row (column 1) as it uses "SelItem := LV_GetNext()", the "1" in the LV_GetText line below it means Column 1. So it should get 3,1 if you highlighted Row 3. Nothing happens if you select a line with the mouse first and then press Enter because of the "IfEqual, CurrCtrl, Edit1" - the focus needs to be on the "find text" edit control (where you type) - you can try to remove that if you don't like it.
Nice! I removed it and now it works both ways! thank you.
ahk7 wrote:Above the Gui, Add, Listview insert a line like "Gui, Font, s15, Arial" to increase the font size of the listview.
thanks!
ahk7 wrote:You can use the command line options, create a batch file with "csv.ahk myfile.csv" for example and start it. What you can also do (which is what I've done) is associate CSV files with CSVQF.AHK (or you could give the files you want to use with CSVQF another extension, myfile.CQF for example, if you double click it windows will ask which program, select CSVQF.AHK and from then on it will launch it.
Awesome! I use my own GUI searcher and when I type the code, I hit enter and I will make it launch CSVQF and type the code to filter... but know that I see how it works, pasting would not work, right? as it does not type letter by letter. Also is there a way to quickly jump into CSVQF and have the code filtered already? so I do not need to use another script to type it in.

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 25 Jul 2017, 13:11
by ahk7
fenchai wrote:Also is there a way to quickly jump into CSVQF and have the code filtered already?

If we add another command line parameter it should be possible, like so:

Line 68 https://github.com/hi5/CSVQF/blob/master/CSVQF.ahk#L68 add/insert

Code: Select all

Param5=%5%
after the (ParamCount = 4) block https://github.com/hi5/CSVQF/blob/master/CSVQF.ahk#L144

Code: Select all

Else If (ParamCount = 5)
	{
	 File:=Param1
	 Delimiter:=Param2
	 If (Param2 = "\t") or (Param2 = "/t")
		Delimiter:=A_Tab
	 If (Param2 = "\s") or (Param2 = "/s") or (Param2 = "")
		Delimiter:=A_Space
	 If Param3 in 1,Y,Yes,T,True
		FirstRow:=1
	 ColOrder:=Param4
	 StartSearch:=Param5
	}
change line https://github.com/hi5/CSVQF/blob/master/CSVQF.ahk#L208 to

Code: Select all

Gui, Add, Edit,xp+30 yp-3 w200 h20 vCurrText gGetText, %StartSearch%
Now you can use the commandline to search with a query

Code: Select all

CSVQF.ahk file.csv "," "0" "1,2,3,4,5,6,7" "searchtext"
(this is an example of why I'd like to rewrite it, as you can see there is a lot of duplicate code adding stuff quickly rather then doing it the smart(er) way.

A second option would be to pre-process the CSV using TF_Save(TF_Find(...)) to create a new CSV file and just launch CSVQF.ahk filtered-file.csv

TF is here https://github.com/hi5/TF (TF_Find uses regex by default)

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 26 Jul 2017, 23:21
by fenchai
ahk7 wrote: If we add another command line parameter it should be possible, like so:
How do I run the command line with ahk? I dont even know how to do it with cmd lol

Code: Select all

run, %comspec% /k/ C:\Users\FERNANDO\Desktop\CSVQF-master\CSVQF-master\CSVQF.ahk C:\Users\FERNANDO\Google Drive\fromPhone\inventarioHL.csv "," "1" "1,2,3,4,5,6,7,8,9,10" "21050"
should it be like that?

I am really not an expert with cmd.

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 27 Jul 2017, 15:47
by ahk7
This should probably do it, escape the commas like so `,

Code: Select all

run %A_Desktop%\CSVQF-master\CSVQF-master\CSVQF.ahk "C:\Users\FERNANDO\Google Drive\fromPhone\inventarioHL.csv" "`," "1" "1`,2`,3`,4`,5`,6`,7`,8`,9`,10" "21050"

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 27 Jul 2017, 18:41
by fenchai
ahk7 wrote:This should probably do it, escape the commas like so `,

Code: Select all

run %A_Desktop%\CSVQF-master\CSVQF-master\CSVQF.ahk "C:\Users\FERNANDO\Google Drive\fromPhone\inventarioHL.csv" "`," "1" "1`,2`,3`,4`,5`,6`,7`,8`,9`,10" "21050"
SUPER AWESOME thank you! I will be looking forward to your next revamp project!

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 29 Aug 2023, 07:41
by MusoCity
If I use 3|34 in the filter to find all entries with 34 in column 3
I get 134, 234,34,347....
how do I just get the entries with 34 only ?

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 29 Aug 2023, 11:59
by ahk7
MusoCity wrote:
29 Aug 2023, 07:41
how do I just get the entries with 34 only ?
I don't think you can at the moment, mostly because the column search can't use regex, otherwise it should be possible using \b34\b but doesn't work by the looks of it.

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 29 Aug 2023, 14:48
by MusoCity
Can I modify it to work ?

Re: CSV Quick Filter (CSVQF) - filter results in listview as you type

Posted: 29 Aug 2023, 16:13
by ahk7
Why not, but the entire logic from https://github.com/hi5/CSVQF/blob/master/CSVQF.ahk#L306 to https://github.com/hi5/CSVQF/blob/master/CSVQF.ahk#L353 needs a rewrite (306 to 311 needs to be commented I think, the regex search is reset and doesn't actually work anymore I see)

(this is legacy AutoHotkey code)