Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

CSV Quick Filter (GUI) show results in listview as you type


  • Please log in to reply
34 replies to this topic
SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
CSV Quick Filter v0.39r
Download:
https://ahknet.autoh...ilter/csvqf.zip
(consists of one AHK file + modified CSV lib (with bug fixes) + Attach lib by majkinetor)

This script 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.

Note: an entire row of the CSV is searched at once and not
on a cell by cell basis to provide faster search results.
For large files it will be fairly slow, it will also depend on your PC specs how fast it will display all the results.

Updated (0.38i) Command line options:

Command line options:
CSVQF file ["delimiter"] ["header"] ["Columns to use in CSV"]
Example, opening a | delimited file:
CSVQF data.csv "|"

Use the first row as header for listview: (header = 1,Y,Yes,T,True)
CSVQF data.csv "," "1"

Use \t for a tab delimited file
CSVQF tabdata.txt "\t"

Only use specific columns (1 & 5):
CSVQF data.csv "," "0" "1,5"

or run CSVQF.ahk, select a file and select your preferences.

csvqf-inaction.gif

SoLong&Thx4AllTheFish
  • Members
  • 4999 posts
  • Last active:
  • Joined: 27 May 2007
Update:
- much nicer startup gui with fileselect, default delimiter options
- you can define which columns and the order you wish to display them*
- should be a bit faster for large® datasets

* uses code by jsherk <!-- m -->http://www.autohotke...pic.php?t=73246<!-- m --> to filter input, very nice idea - thx

xsm121
  • Members
  • 4 posts
  • Last active: Jul 02 2011 06:29 PM
  • Joined: 28 May 2009
this is for me
CSV_Load(FileName, CSV_Identifier="", Delimiter="`,")
{
  Local Row
  Local Col
  temp :=  %CSV_Identifier%CSVFile
  FileRead, temp, %FileName%
  StringReplace, temp, temp, `r`n`r`n, `r`n, all   ;Remove all blank lines from the CSV file
  Loop, Parse, temp, `n, `r
  {
	;Exclude space
[color=red]    If A_LoopField is space
      Continue[/color]

    Col := ReturnDSVArray(A_LoopField, CSV_Identifier . "CSV_Row" . A_Index . "_Col", Delimiter)

	;MAX hold
[color=red]    If (col < O_col)
      col := O_col
    Else O_col := col
[/color]
    Row := A_Index
   Loop, Parse, A_LoopReadLine, %Delimiter%
    {
      Col := A_Index
      %CSV_Identifier%CSV_Row%Row%_Col%Col% := A_LoopField
    }
  }
  %CSV_Identifier%CSV_TotalRows := Row
  %CSV_Identifier%CSV_TotalCols := Col
  %CSV_Identifier%CSV_Delimiter := Delimiter
  SplitPath, FileName, %CSV_Identifier%CSV_FileName, %CSV_Identifier%CSV_Path
  IfNotInString, FileName, `\
  {
    %CSV_Identifier%CSV_FileName := FileName
    %CSV_Identifier%CSV_Path := A_ScriptDir
  }
  %CSV_Identifier%CSV_FileNamePath := %CSV_Identifier%CSV_Path . "\" . %CSV_Identifier%CSV_FileName
}


Nutts
  • Guests
  • Last active:
  • Joined: --
Is it possible to change the Column headers to be actual Column headers from the CSV file. I've been trying to modify your code to, but cant seem to get it right.

  • Guests
  • Last active:
  • Joined: --
@Nutss: yes, that should be possible I'll have a stab at it over the weekend.

Nutts
  • Guests
  • Last active:
  • Joined: --
Thz, i was actually able to find where the header labeling was being generated and just hardcoded it in, since my list wouldnt be changing.

I did have another question about the listview. As you filter through your search ive noticed that though the list isnt showing the csv data for rows not relevant, it does still have the blank rows present. And when you sort you current list the blank rows will take priority. Is it possible to get rid of those blank rows?

Thanks again.

  • Guests
  • Last active:
  • Joined: --
Aug 26, 2011 - Update:
* CHANGE: new cmdline paramater for header row, and order has changed see HELP (see 2nd point below)
+ Option to use (first) header row as listview headers (via start gui + cmdline)
+ Gui can now be resized. Uses Attach by majkinetor <!-- m -->http://www.autohotke... ... ight=forms<!-- m -->
+ Gui starts maximized
+ File history in startup Gui. Data saved in file CSVQF.history (you can delete it if you wish)
+ Alt-f sets focus back to filter control
+ No more empty rows in search results, helps with sorting
+ FAST negative results: simply look in entire file before processing all rows :-) (does double memory usage, but who cares)

See links in first post

Nutts
  • Guests
  • Last active:
  • Joined: --
Thank you very much. I really appreciate your work!

tomoe_uehara
  • Members
  • 2166 posts
  • Last active: Jun 11 2015 05:33 PM
  • Joined: 05 Sep 2009
Thank you Hugov, bookmarked! :D

  • Guests
  • Last active:
  • Joined: --
Glad you like it :D

Sep 07, 2011 Update:
- Removed spinning "Indicator" -> Dramatic speed improvement for larger CSV files.

ScottMattes_Offline
  • Guests
  • Last active:
  • Joined: --
I am using 1.0.48.05 and at line 518, for v in files, I get the error "Error: this line does not contain a recognized action".

Do I need ahk-l? :?:

  • Guests
  • Last active:
  • Joined: --
Yes, I'm using ahk_l array there, was so much easier compared to a psuedo array. You'll loose the history though.
You could comment lines 516-530, 659-663, 668-677 or simply download the ZIP version of ahk_l to try it out. (just unzip, copy to csvqf folder and run autohotkey.exe csvqf.ahk)
If you do run basic you have to #include attach.ahk and csv.ahk or move them to your lib folder.

or download a compiled version here <!-- m -->https://ahknet.autoh... ... rtable.zip<!-- m -->


step
  • Members
  • 21 posts
  • Last active: Feb 21 2012 11:04 PM
  • Joined: 23 Jan 2012
Thank you very much, this is a great program!
How could I restrict search to the "last" column only? By "last" I mean the first element from the right, which could correspond to differently-numbered columns. Consider this example, where each data line is a fullpath:
csvqf.exe data.csv "\\" "$"
(I just made up a syntax for last column, "$"); this example would search in filenames only, disregarding the container path no matter how many path elements each container has.

  • Guests
  • Last active:
  • Joined: --
@step: glad you like it. Nice suggestion, I added it, in the search Gui there is now a DDL where you can select ALL or a specific column to search, also some minor tweaks to the startup gui and column headers.

Download version 0.41k here <!-- m -->https://ahknet.autoh... ... /csvqf.zip<!-- m -->

step
  • Members
  • 21 posts
  • Last active: Feb 21 2012 11:04 PM
  • Joined: 23 Jan 2012

@step: glad you like it. Nice suggestion, I added it, in the search Gui there is now a DDL where you can select ALL or a specific column to search

Hi, thank you for the new version, but this isn't really what I had in mind. It's my fault, I didn't explain myself correctly. Let me try again.
First you need a data.csv file holding full pathnames to a bunch of files; with CMD.exe
dir /b /s C:\SOME-FOLDER > C:\data.csv
Ensure that SOME-FOLDER includes other folders because we want to test paths with varying number of components. By component I mean a string between pairs of excluded backslashes, like \dir1\, or the filename component of a path, like (just the bold text) ...\dir\FILENAME.EXT.
Our goal is to match just the filename component. Obviously a fixed column number won't do because a filename occurs in different column numbers depending on the path length. But if we had a way to say, "I want the last column", say that the last column is referenced by the special symbol "$", then we could just say, "Match pattern in column $". That's the gist of my question, could it be done?
Here's a sample data.csv
C:\dir\dir1\file1.txt
C:\dir\dir1\file2.txt
C:\dir\dir1\dir11\file3.txt
Launching csvqf.awk data.csv "\\" "$" and
LOOKING FOR            YIELDS THE FULL ROWS OF
  txt                         file1.txt, file2.txt, file3.txt
  dir                         nothing
We're matching the "last" column only - not columns "4,5" at once.
While launching csvqf.awk data.csv "\\" "4" and
LOOKING FOR            YIELDS THE FULL ROWS OF
  txt                         file1.txt, file2.txt
  dir                         file3.txt (actually dir11)
File3.txt is excluded from "txt" because txt is in column 5. File3.txt is included in "dir" because dir11 is in column 4.
Sorry again for the confusion.