Is it possible to search a CSV for a word then report back the Cell location? Topic is solved

[Reynbow]

On top of that, could I also search for what is in a cell and have it report back what's in a cell?

Fairly straight forward request to understand but if I can elaborate I will do my best.

[BoBo]

Code: Select all

#SingleInstance, Force

If !FileExist("my.csv")
	FileAppend, 
	(LTrim
	test,test,Gold,test
	test,Silver,test,Gold
	test,test,Gold,Bronze
	), my.csv
	
MsgBox % ClipBoard := 2find("Gold")
MsgBox % ClipBoard := 2find("Silver")
MsgBox % ClipBoard := 2find("Bronze")
MsgBox % ClipBoard := 2get("A3")
MsgBox % ClipBoard := 2get("B2")
MsgBox % ClipBoard := 2get("C4")

2find(cellContent) {
	Loop, Read, my.csv
	
	{
		array := StrSplit(A_LoopReadLine,",")
 		lNo := A_Index
		Loop % array.Length() {
			If (array[A_Index] = cellContent)
				Return Chr(lNo+64) ":" A_Index
			}
		}	
	}

2get(cell) {
	rowNo := Asc(SubStr(cell,1,1))-64
	colNo := SubStr(cell,2)
	Loop, Read, my.csv
	{
		If (A_Index = rowNo) {
			array := StrSplit(A_LoopReadLine,",")
			Return array[colNo]
			}
	}	
}

F10::Reload

There will be, of course, a more sophisticated COM approach showing up soon

[edit] Uses alphabetical characters for the rows instead of the columns what's obviously wrong/not standard. JFTR

[Guest]

There is a CSV lib https://autohotkey.com/boards/viewtopic.php?f=6&t=34853 with various search options (search, row, column)

[Reynbow]

@BoBo
Would you be able to explain how this works at all? I'm not able to get it to work.

There is a CSV lib https://autohotkey.com/boards/viewtopic.php?f=6&t=34853 with various search options (search, row, column)

I don't even know where I'd begin to use this.

[Reynbow]

So I've kinda understood how this is working now, but it keeps returning values that don't make much sense.

For example. If the term is in cell B10, it'll say it's in J2. It thinks the vertical cells are letters and the horizontal cells are numeric. How can I fix this?

[BoBo]

Unchanged but commented code from above.

Spoiler

Code: Select all

#SingleInstance, Force
; SetWorkingDir, A_ScriptDir

If !FileExist("my.csv")								; create test file if it doesn't already exist
	FileAppend, 
	(LTrim
	test,test,Gold,test
	test,Silver,test,Gold
	test,test,Gold,Bronze
	), my.csv
	
MsgBox % ClipBoard := 2find("Gold")					; request the position of the first cell that contains the word "Gold"
MsgBox % ClipBoard := 2find("Silver")
MsgBox % ClipBoard := 2find("Bronze")
MsgBox % ClipBoard := 2get("A3")					; request the content from a position/cell eg. A:3 (1st row/3rd column)
MsgBox % ClipBoard := 2get("B2")
MsgBox % ClipBoard := 2get("C4")

2find(cellContent) {
	Loop, Read, my.csv								; parse input file line by line
	{
		array := StrSplit(A_LoopReadLine,",")		; push the content of each line to an array
 		lNo := A_Index								; keep the current line number
		Loop % array.Length() {						; loop through all elements of the array
			If (array[A_Index] = cellContent)		; if an array element matches the expected search pattern (eg. "Gold") ...
				Return Chr(lNo+64) ":" A_Index		; convert the line number to an ASCII-character/row indicator and return the result/cell position eg. D:4
			}
		}
	}

2get(cell) {
	rowNo := Asc(SubStr(cell,1,1))-64				; extract the row number with converting a characters position in the ASCII-table to a number (A = ASCII 65) - 64 = 1 => 1st row
	colNo := SubStr(cell,2)							; extract the column number from the cell position pattern
	Loop, Read, my.csv								; parse the input file line by line
	{
		If (A_Index = rowNo) {						; if the expected line is found
			array := StrSplit(A_LoopReadLine,",")	; push the content of that line to an array 
			Return array[colNo]						; take the element/cell content from the requested position within the array/row
			}
		}	
	}

F10::Reload

You're damn right, the code uses alphabetical characters to label the rows instead of the columns - what's obviously wrong
I didn't correct it bc ...

... you should go for the CSV library linked above!! To call its functions (see below) isn't that tricky. You won't have to know anything more for now (except its parameters) and it contains everything one would need if working with INIs

CSV_Load(FileName, CSV_Identifier, Delimiter) ; Load CSV file into memory, must complete first.
CSV_TotalRows(CSV_Identifier) ; Return total number of rows
CSV_TotalCols(CSV_Identifier) ; Return total number of columns
CSV_Delimiter(CSV_Identifier) ; Return the delimiter used
CSV_FileName(CSV_Identifier) ; Return the filename
CSV_Path(CSV_Identifier) ; Return the path
CSV_FileNamePath(CSV_Identifier) ; Return the filename with the full path
CSV_Save(FileName, CSV_Identifier, OverWrite?) ; Save CSV file
CSV_DeleteRow(CSV_Identifier, RowNumber) ; Delete a row
CSV_AddRow(CSV_Identifier, "Cell1,Cell2...") ; Add a row
CSV_DeleteColumn(CSV_Identifier, ColNumber) ; Delete a column
CSV_AddColumn(CSV_Identifier, "Cell1,Cell2...") ; Add a column
CSV_ModifyCell(CSV_Identifier, NewValue,Row, Col) ; Modify an existing cell
CSV_ModifyRow(CSV_Identifier, "NewValue1,NewValue2...", RowNumber) ; Modify an existing row
CSV_ModifyColumn(CSV_Identifier, "NewValue1,NewValue2...", ColNumber)) ; Modify an existing column
CSV_Search(CSV_Identifier, SearchText, Instance) ; Search for text within
CSV_SearchRow(CSV_Identifier, SearchText, RowNumber, Instance) ; Search for text within a cell within a specific row
CSV_SearchColumn(CSV_Identifier, SearchText, ColNumber, Instance) ; Search for text within a cell within a specific column
CSV_MatchCell(CSV_Identifier, SearchText, Instance) ; Search for a cell containing exactly the data specified
CSV_MatchCellColumn(CSV_Identifier, SearchText, ColNumber, Instance=1) ; Search for a cell containing exactly the data specified in a specific column
CSV_MatchCellRow(CSV_Identifier, SearchText, RowNumber, Instance=1) ; Search for a cell containing exactly the data specified in a specific row
CSV_MatchRow(CSV_Identifier, "SearchText1,SearchText2", Instance) ; Search for a row containing exactly the data specified
CSV_MatchCol(CSV_Identifier, "SearchText1, SearchText2", Instance) ; Search for a column containing exactly the data specified
CSV_ReadCell(CSV_Identifier, Row, Column) ; Read data from the specified cell
CSV_ReadRow(CSV_Identifier, RowNumber) ; Read data from the specified row
CSV_ReadCol(CSV_Identifier, ColNumber) ; Read data from the specified column
CSV_LVLoad(CSV_Identifier, Gui, x, y, w, h, header, Sort?, RowIdentification?, AutoAdjustCol?) ; Load data into a listview in the specified gui window, listviewname variablewill equal "CSV_Identifier"
CSV_LVSave(FileName, CSV_Identifier, Delimiter, OverWrite?, Gui) ; Save the specified listview as a CSV file, CSV_Identifier is the ListView's associated variable name.

Sample code

Spoiler

Code: Select all

#SingleInstance, Force
SetWorkingDir, A_ScriptDir
#Include csv().ahk         ; download this AHK code library and place it in your scripts (or your default library) directory: https://raw.githubusercontent.com/hi5/CSV/master/csv.ahk

If !FileExist("my.csv")								; create test file if it doesn't already exist
	FileAppend, 
	(LTrim
	test;test;Gold;test
	test;Silver;test;Gold
	test;test;Gold;Bronze
	), my.csv

CSV_Load("my.csv","A",";")         ; Load CSV file into memory, must complete first (where the variable that contains that files content ("the CSV_Identifier") is labeled "A" in this case. The delimiter is a semi-colon)
MsgBox % CSV_MatchCell("A", "Gold",3)         ; Search for a cell containing exactly the data specified (where CSV_Identifier = "A", SearchText = "Gold", Instance = the 3rd occurence of SearchText)
MsgBox % CSV_ReadCell("A", 2, 2)         ; Read data from the specified cell (where we read the content from the cell at the 2nd row/2nd column)

[Reynbow]

So I tried exactly your sample code and it just returns a 0

Which definitely isn't correct.

Code: Select all

#SingleInstance, Force
SetWorkingDir, A_ScriptDir
#Include csv.ahk

Inputbox, name, What's the name?

CSV_Load("C:\Users\account\Documents\accts.csv","A",",")
MsgBox % CSV_MatchCell("A", name,3)
MsgBox % CSV_ReadCell("A", 2, 2)

The csv is 5865 cells tall and 3 cells wide. I've literally just searched for something I know is in the excel and it returns a "0" message.
I also changed the delimiter to a , instead of ; because well... CSV uses ,

Also what do you mean about the cells not being wrong in regards to horizontal cells being lettered. That's standard. I've never seen it numbered before in my life.

EDIT: Figured out the issue. The instance for the MatchCell needs to be 1, not 3. Not that I have any idea what that does or means.

[BoBo]

The instance for the MatchCell needs to be 1, not 3. Not that I have any idea what that does or means

Because you don't read my comments within the provided code?!
If there's only a single instance of the name (your search pattern) within the csv file/"table" it won't make sense to search for a third instance of that name - and therefore that search had to fail.

[Reynbow]

The instance for the MatchCell needs to be 1, not 3. Not that I have any idea what that does or means

Because you don't read my comments within the provided code?!
If there's only a single instance of the name (your search pattern) within the csv file/"table" it won't make sense to search for a third instance of that name - and therefore that search had to fail.

Ahh okay, your description here makes sense.

I couldn't understand what you meant in your comments in the code. I read it, but had no idea what you were trying to say.