help with 2d array please Topic is solved
help with 2d array please
got a excel file like this:
a 1
b 2
c 3
how to convert it into a txt file,
and search the given content ?
for example, give the "c" could got the "3"
thanks in advance
a 1
b 2
c 3
how to convert it into a txt file,
and search the given content ?
for example, give the "c" could got the "3"
thanks in advance
Re: help with 2d array please
This page has a handy search form. I used it and found a few ideas:
viewtopic.php?p=448398#p448398
viewtopic.php?p=481787#p481787
viewtopic.php?p=135359#p135359
viewtopic.php?p=448398#p448398
viewtopic.php?p=481787#p481787
viewtopic.php?p=135359#p135359
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: help with 2d array please Topic is solved
To convert a range of cells to a text file you can use:
to search for "c" in the 1st column in the text file you just created:
But you do not have to create a text file in order to do this kind of search. You can use:
Code: Select all
xl := ComObjActive("excel.application")
xl.activesheet.usedrange.copy
filedelete, xl2clip.txt
fileappend, %clipboard%, xl2clip.txt
Code: Select all
fileread, var, xl2clip.txt
for x,y in strsplit(var,"`n","`r")
lst .= strsplit(y,a_tab).1 = "c" ? strsplit(y,a_tab).2 : "" ; search in column 1, result in column 2
msgbox % lst
Code: Select all
xl := ComObjActive("excel.application")
msgbox % xl.activesheet.usedrange.columns(1).Find("c",,,1).offset(0,1).text
14.3 & 1.3.7
Re: help with 2d array please
right, dear mr.mikeyww, with your prompting, got some code like this:mikeyww wrote: ↑24 Mar 2023, 06:01This page has a handy search form. I used it and found a few ideas:
viewtopic.php?p=448398#p448398
viewtopic.php?p=481787#p481787
viewtopic.php?p=135359#p135359
Code: Select all
get_unit() {
txt_path:=A_ScriptDir "\unit.txt"
excel:=comobjactive("excel.application")
excel.ActiveSheet.SaveAs(txt_path, 42)
}
write_unit() {
FileRead, txt_content, % A_ScriptDir "\unit.txt"
temp_array:= StrSplit(txt_content, [A_Tab, "`n"])
excel:=comobjactive("excel.application")
loop {
y:=excel.ActiveCell.Row
x:=excel.ActiveCell.Column
item:=excel.Cells(y,x).Text
if (item="") {
break
} else {
is_item_exist:=HasVal(temp_array, item)
if (is_item_exist!=0) {
excel.Cells(y,x+2).value:=temp_array[is_item_exist+1]
} else if (is_item_exist=0) {
excel.Cells(y,x+2).value:="unknown item"
}
}
excel.Cells(y+1,x).Activate
}
}
HasVal(haystack, needle) {
for index, value in haystack
if (value = needle)
return index
if !(IsObject(haystack))
throw Exception("Bad haystack!", -1, haystack)
return 0
}
Re: help with 2d array please
@flyingDman
thank you sir, you are always so kind
i rewrite my code with your teaching, and
the original data is like this:
a 1
b 2
c 3
but i got this after running search:
c 3
b 32
a 321
could you please tell me whats wrong did i make?
thank you sir, you are always so kind
i rewrite my code with your teaching, and
the original data is like this:
a 1
b 2
c 3
but i got this after running search:
c 3
b 32
a 321
could you please tell me whats wrong did i make?
Code: Select all
get_unit() {
excel:=comobjactive("excel.application")
excel.activesheet.usedrange.copy
filedelete, unit.txt
fileappend, %clipboard%, unit.txt
}
write_unit() {
FileRead, txt_content, % A_ScriptDir "\unit.txt"
excel:=comobjactive("excel.application")
loop {
y:=excel.ActiveCell.Row
x:=excel.ActiveCell.Column
item:=excel.Cells(y,x).Text
if (item="") {
break
} else {
for a,b in strsplit(txt_content,"`n","`r")
unit .= strsplit(b,a_tab).1 = item ? strsplit(b,a_tab).2 : ""
if (unit!="") {
excel.Cells(y,x+2).value:=unit
} else if (unit="") {
excel.Cells(y,x+2).value:="[unknown]"
}
excel.Cells(y+1,x).Activate
}
}
Last edited by wer on 25 Mar 2023, 12:02, edited 1 time in total.
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: help with 2d array please
Put unit := "" at the top of the loop
but you might want to consider something like this:
but you might want to consider something like this:
Code: Select all
xl := ComObjActive("excel.application")
if !xl.activecell.value
return
FileRead, txt_content, % A_ScriptDir "\xl2clip.txt"
arr := {}
for x,y in strsplit(txt_content,"`n","`r")
arr[strsplit(y,a_tab).1] := strsplit(y,a_tab).2
for c in xl.range(xl.activecell,xl.activecell.end(-4121)).cells
c.offset(0,2).value := arr[c.value]
14.3 & 1.3.7
Re: help with 2d array please
thank you so much dear sirflyingDman wrote: ↑24 Mar 2023, 22:42Put unit := "" at the top of the loop
but you might want to consider something like this:Code: Select all
xl := ComObjActive("excel.application") if !xl.activecell.value return FileRead, txt_content, % A_ScriptDir "\xl2clip.txt" arr := {} for x,y in strsplit(txt_content,"`n","`r") arr[strsplit(y,a_tab).1] := strsplit(y,a_tab).2 for c in xl.range(xl.activecell,xl.activecell.end(-4121)).cells c.offset(0,2).value := arr[c.value]
concise code, i will figure it out
Re: help with 2d array please
@flyingDman
got one more question sir,
how to deal with the duplicate item in original data?
if original data like this:
a 1
b 2
c 3
b 4
a 5
the match result will be like:
a 15
b 24
c 3
b 24
a 15
and the script seems stuck in the loop...
got one more question sir,
how to deal with the duplicate item in original data?
if original data like this:
a 1
b 2
c 3
b 4
a 5
the match result will be like:
a 15
b 24
c 3
b 24
a 15
and the script seems stuck in the loop...
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01