Help with reading csv files

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Tomer
Posts: 314
Joined: 21 Aug 2016, 05:11

Help with reading csv files

26 Feb 2020, 07:53

i have 2 cvs files:

1st file called: "Users_and_OU.csv" and contains the following:

USER1,OU=1
USER2,OU=2
USER3,OU=3
USER4,OU=4
USER5,OU=5


2nd file called: "Users.csv" and contains the following:

USER1
USER2
USER3
USER4
USER5


i want get the OU number from 1st file depending on what I'm looking for in the 2nd file.
exemple code below im trying to get the OU number belong to "USER3", so i should get a msgbox only with: "OU=3"

thanks in advance.

Code: Select all

FileRead, List, Users_and_OU.csv

Loop, Read, Users.csv
{
	User := StrSplit(A_LoopReadLine, ",").1

	
	if User = User3
	{
		StringReplace, List, List, User3,
		MsgBox % list
	}
}
User avatar
boiler
Posts: 5668
Joined: 21 Dec 2014, 02:44

Re: Help with reading csv files

26 Feb 2020, 10:35

Read the OU file into an associative array (and the users file into a simple array), then you can access any item directly by the particular user without having to loop or anything:

Code: Select all

OU := {}
FileRead, Text, Users_and_OU.csv
loop, Parse, Text, "`n", "r"
{
	z := StrSplit(A_LoopField, ",")
	OU[z.1] := z.2
}
FileRead, Text, Users.csv
User := StrSplit(Text, "`n", "`r")

MsgBox, % OU[User.3] ; same as OU["USER3"]
User avatar
flyingDman
Posts: 809
Joined: 29 Sep 2013, 19:01

Re: Help with reading csv files

26 Feb 2020, 11:41

As .csv files can be read by Excel, you can accomplish this using COM as well. You would need to use the Vlookup function:

Code: Select all

needle := "USER3" 

Xl := ComObjCreate("Excel.Application") 				            ;create a handle to a new excel application
wrkbk1:=Xl.Workbooks.Open("C:\Users\xxxx\Scripts\Users_and_OU.csv") 		
wrkbk2:=Xl.Workbooks.Open("C:\Users\xxxx\Scripts\Users.csv") 	
for c in wrkbk2.activesheet.usedrange
	if (c.value = needle)
		msgbox % Xl.WorksheetFunction.VLookup(c.value, wrkbk1.activesheet.usedrange, 2)
xl.quit()
Maybe using Excel is not necessary in your case, but I just wanted to show this is possible.
User avatar
Tomer
Posts: 314
Joined: 21 Aug 2016, 05:11

Re: Help with reading csv files

27 Feb 2020, 02:46

Thank you both.

but the values such as "OU" are just for sample,
original the csv file have other values.

i trying using https://github.com/hi5/TF lib and it seems to wroks good,
if you have better method share it please :)

Code: Select all

#Include tf.ahk

Loop, Read, d:\users.csv
{
	User := StrSplit(A_LoopReadLine, ",").1


	tt := TF_Find("d:\Users_and_OU.csv", "", "", User, 0, 1)
	OU := StrSplit(tt, ",").2
	MsgBox % OU
	
}
User avatar
boiler
Posts: 5668
Joined: 21 Dec 2014, 02:44

Re: Help with reading csv files

27 Feb 2020, 08:33

The approach shown has nothing to do with what the values are. Put anything you want in the files and it will work.

Just because the name of the array is OU doesn’t mean the values have to be OU. Change the name of the array to Value if you want.
User avatar
flyingDman
Posts: 809
Joined: 29 Sep 2013, 19:01

Re: Help with reading csv files

27 Feb 2020, 12:59

I agree with @boiler. Also, although TF has some powerful capabilities, I do not think it is needed here (Excel is not needed either, but I already made that point). Here is a more generic form:

Code: Select all

fileread, oVar1, C:\Users\tjall\Scripts\test1.csv
fileread, oVar2, C:\Users\tjall\Scripts\test2.csv			   ; file containing the needle in column 2	
for a, b in strsplit(oVar2,"`n","`r")
	for x, y in strsplit(oVar1,"`n","`r")
		if (strsplit(b,",").1 = strsplit(y,",").1)
			msgbox % strsplit(y,",").2                               ; shows col 2 value(s)
User avatar
Tomer
Posts: 314
Joined: 21 Aug 2016, 05:11

Re: Help with reading csv files

29 Feb 2020, 20:02

boiler wrote:
27 Feb 2020, 08:33
The approach shown has nothing to do with what the values are. Put anything you want in the files and it will work.

Just because the name of the array is OU doesn’t mean the values have to be OU. Change the name of the array to Value if you want.
True!
Tested it and it works like a charm! Thanks!
User avatar
Tomer
Posts: 314
Joined: 21 Aug 2016, 05:11

Re: Help with reading csv files

29 Feb 2020, 20:03

flyingDman wrote:
27 Feb 2020, 12:59
I agree with @boiler. Also, although TF has some powerful capabilities, I do not think it is needed here (Excel is not needed either, but I already made that point). Here is a more generic form:

Code: Select all

fileread, oVar1, C:\Users\tjall\Scripts\test1.csv
fileread, oVar2, C:\Users\tjall\Scripts\test2.csv			   ; file containing the needle in column 2	
for a, b in strsplit(oVar2,"`n","`r")
	for x, y in strsplit(oVar1,"`n","`r")
		if (strsplit(b,",").1 = strsplit(y,",").1)
			msgbox % strsplit(y,",").2                               ; shows col 2 value(s)
Thanks!!

Return to “Ask For Help”

Who is online

Users browsing this forum: boiler, Google [Bot], pnKed, SilasDeVis and 48 guests