help with 2d array please Topic is solved

[wer]

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

[mikeyww]

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

[flyingDman]

To convert a range of cells to a text file you can use:

Code: Select all

xl := ComObjActive("excel.application")
xl.activesheet.usedrange.copy
filedelete, xl2clip.txt
fileappend, %clipboard%, xl2clip.txt

to search for "c" in the 1st column in the text file you just created:

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

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")
msgbox % xl.activesheet.usedrange.columns(1).Find("c",,,1).offset(0,1).text

[wer]

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

right, dear mr.mikeyww, with your prompting, got some code like this:

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
}

[wer]

@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?

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
  }
}

[flyingDman]

Put 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]

[wer]

Put 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]

thank you so much dear sir
concise code, i will figure it out

[wer]

@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...

[flyingDman]

Duplicates should not be an issue w/ my last script.