AutoHotkey Community
It is currently May 27th, 2012, 11:12 am

View unanswered posts | View active topics


Board index » AutoHotkey » Support

All times are UTC [ DST ]



Post new topic Reply to topic  Page 1 of 2
  [ 24 posts ]  Go to page 1, 2  Next
  Print view | E-mail friend Previous topic | Next topic 
Author Message
Gee39
PostPosted: February 1st, 2012, 10:33 am 
Offline

Joined: November 17th, 2011, 12:55 pm
Posts: 41
Short question,

I want to build column D (given A, B and C).
Would you use AHK for this? Or solve this solely in Excel?

A B C D
1 a fgw 1
2 a wgt 1
3 a jh 1
4 a er 1
5 bs jhrjh 2
6 bs err 2
7 vc heth 3
8 vc ehet 3
9 vc j6j6 3
10 vc 545h4 3
11 gg j6j64 4
12 gg he5he 4
13 gg 535h 4


Report this post
Top
 Profile  
Reply with quote  
hd0202
PostPosted: February 1st, 2012, 10:37 am 
Offline

Joined: August 13th, 2006, 6:45 am
Posts: 355
Location: Germany
I would create a csv file in AHK and load it into excel.

Hubert


Report this post
Top
 Profile  
Reply with quote  
Gee39
 Post subject:
PostPosted: February 1st, 2012, 11:01 am 
Offline

Joined: November 17th, 2011, 12:55 pm
Posts: 41
And make a var for D and update this with 1 every time the value in B changes?


Report this post
Top
 Profile  
Reply with quote  
hd0202
 Post subject:
PostPosted: February 1st, 2012, 11:09 am 
Offline

Joined: August 13th, 2006, 6:45 am
Posts: 355
Location: Germany
Gee39 wrote:
And make a var for D and update this with 1 every time the value in B changes?
Yes.

Hubert


Report this post
Top
 Profile  
Reply with quote  
Guest
 Post subject:
PostPosted: February 1st, 2012, 11:53 am 
Some tips http://www.autohotkey.com/wiki/index.php?title=FAQ#CSV


Report this post
Top
  
Reply with quote  
garry
 Post subject:
PostPosted: February 1st, 2012, 12:08 pm 
Offline

Joined: April 19th, 2005, 10:26 am
Posts: 2250
Location: switzerland
to create ( and also modify/edit/sort ) csv files can use Listview
some examples with listview
http://www.autohotkey.com/forum/topic4526.html


Report this post
Top
 Profile  
Reply with quote  
trismarck
 Post subject:
PostPosted: February 1st, 2012, 12:53 pm 
Offline

Joined: December 2nd, 2010, 11:14 pm
Posts: 214
Location: Poland
What do you want to do with the data after inserting the data into an ahk/excel table?


Report this post
Top
 Profile  
Reply with quote  
Gee39
 Post subject:
PostPosted: February 2nd, 2012, 1:16 pm 
Offline

Joined: November 17th, 2011, 12:55 pm
Posts: 41
That's a long story. They are actually a training set of ascii files for a neural network. The number in D states what (and the number of) output nodes (categories). B is the name of an inputascii-file. C is the name of an output-ascii-file.


Report this post
Top
 Profile  
Reply with quote  
Odlanir
 Post subject:
PostPosted: February 2nd, 2012, 4:47 pm 
Offline

Joined: August 7th, 2011, 1:23 pm
Posts: 754
Code:
PathIN := A_ScriptDir "\Test.xls"
PathOU := A_ScriptDir "\TestNew.xls"
FileDelete, %PathOU%
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(PathIN)
Xl.Visible := false
loop {
   if ( (thisCell := XL.Cells(A_index, 2).Value) = "" )
      break
   if ( a_index = 1 )
      oldVal := thisCell, cnt := 1
   if ( oldVal <> thisCell ) {
      oldVal := thisCell, ++cnt
   }
   XL.Cells(A_index, 4).Value := cnt      
}
Xl.ActiveWorkbook.SaveAs(PathOU)
xl.Workbooks.Close
run, %PathOU%
sleep, 100
ExitApp

_________________
Win7 - Firefox 10.0.2 - AHK_L 1.1.07.00
Please bear with me and my English which is so bad at times that even I don't understand myself


Report this post
Top
 Profile  
Reply with quote  
Gee39
 Post subject:
PostPosted: February 3rd, 2012, 1:57 pm 
Offline

Joined: November 17th, 2011, 12:55 pm
Posts: 41
Wow, that is a piece of beautiful code. I'm gonne test it monday.


Report this post
Top
 Profile  
Reply with quote  
G. Sperotto
 Post subject:
PostPosted: February 3rd, 2012, 2:15 pm 
Offline

Joined: December 12th, 2011, 10:25 pm
Posts: 251
Gee39 wrote:
Wow, that is a piece of beautiful code. I'm gonne test it monday.


Indeed. But don't forget to use it on AHK_L.

Basic version doesn't have a native support for COM.


Report this post
Top
 Profile  
Reply with quote  
sinkfaze
 Post subject:
PostPosted: February 3rd, 2012, 5:39 pm 
Offline
User avatar

Joined: March 19th, 2008, 12:43 am
Posts: 5482
Location: the tunnel(?=light)
You can probably keep the source data in a CSV file, but definitely open the CSV with Excel to create the values for column D; Excel's built-in tools for discovering the proper values will outperform AHK almost without exception. If you're using AHK_L and have Excel installed on your machine, open a blank workbook then run this code to see how quickly column D can be populated.

Code:
/*
   Code assumes that:

   -- if cell in column B contains a, cell in column D should be 1
   -- if cell in column B contains bs, cell in column D should be 2
   -- if cell in column B contains vc, cell in column D should be 3
   -- if cell in column B contains gg, cell in column D should be 4
*/
data=
(
1,a,fgw
2,a,wgt
3,a,jh
4,a,er
5,bs,jhrjh
6,bs,err
7,vc,heth
8,vc,ehet
9,vc,j6j6
10,vc,545h4
11,gg,j6j64
12,gg,he5he
13,gg,535h
)
Loop, parse, data, `n, `r
{
   if   (A_Index=1) {
      StringReplace, n, A_LoopField, `,, `,, UseErrorLevel
      cols :=   ErrorLevel+1
   }
   rows :=   A_Index
}
safeArray :=   ComObjArray(12,rows,cols)
Loop, parse, data, `n, `r
{
   i :=   A_Index-1
   Loop, parse, A_LoopField, `,
      safeArray[i,A_Index-1] :=   A_LoopField
}
MsgBox, 64, Excel Step 1, Loading the data.
xl :=   ComObjActive("Excel.Application")
xl.Range["A1:C" rows].Value :=   safeArray
MsgBox, 64, Excel Step 2, Adding Column D.
end :=   xl.Range["C1"].End(xlDown).Row
xl.Range["D1"].Formula :=   "=IF(B1=""a"",1,IF(B1=""bs"",2,IF(B1=""vc"",3,IF(B1=""gg"",4,""N/A""))))"
xl.Range["D1"].AutoFill(xl.Range["D1:D" end]), xl.Range["D1:D" end].Copy(), xl.Range["D1:D" end].PasteSpecial(-4163)
xl.Range["A1"].Select(), xl.CutCopyMode :=   False, xl :=   ""

_________________
Image
Try Quick Search for Autohotkey or see the tutorial for newbies.


Report this post
Top
 Profile  
Reply with quote  
Odlanir
 Post subject:
PostPosted: February 3rd, 2012, 9:21 pm 
Offline

Joined: August 7th, 2011, 1:23 pm
Posts: 754
To avoid hardconding of all the possible values I would do it this way:
Code:
xl.Range["D1"].Value   := 1 ; First item will be ever 1
xl.Range["D2"].Formula :=  "=IF(B2=B1,D1,D1+1)"
xl.Range["D2"].AutoFill(xl.Range["D2:D" end]), xl.Range["D2:D" end].Copy(), xl.Range["D2:D" end].PasteSpecial(-4163)

_________________
Win7 - Firefox 10.0.2 - AHK_L 1.1.07.00
Please bear with me and my English which is so bad at times that even I don't understand myself


Report this post
Top
 Profile  
Reply with quote  
Gee39
 Post subject:
PostPosted: February 6th, 2012, 1:04 pm 
Offline

Joined: November 17th, 2011, 12:55 pm
Posts: 41
None of the methods run errorfree.

The 1e solution starts error in line 42

the 2e from line 44


Report this post
Top
 Profile  
Reply with quote  
sinkfaze
 Post subject:
PostPosted: February 6th, 2012, 1:50 pm 
Offline
User avatar

Joined: March 19th, 2008, 12:43 am
Posts: 5482
Location: the tunnel(?=light)
Fixed.

Code:
/*
   Code assumes that:

   -- if cell in column B contains a, cell in column D should be 1
   -- if cell in column B contains bs, cell in column D should be 2
   -- if cell in column B contains vc, cell in column D should be 3
   -- if cell in column B contains gg, cell in column D should be 4
*/
xlDown :=   -4121
data=
(
1,a,fgw
2,a,wgt
3,a,jh
4,a,er
5,bs,jhrjh
6,bs,err
7,vc,heth
8,vc,ehet
9,vc,j6j6
10,vc,545h4
11,gg,j6j64
12,gg,he5he
13,gg,535h
)
Loop, parse, data, `n, `r
{
   if   (A_Index=1) {
      StringReplace, n, A_LoopField, `,, `,, UseErrorLevel
      cols :=   ErrorLevel+1
   }
   rows :=   A_Index
}
safeArray :=   ComObjArray(12,rows,cols)
Loop, parse, data, `n, `r
{
   i :=   A_Index-1
   Loop, parse, A_LoopField, `,
      safeArray[i,A_Index-1] :=   A_LoopField
}
MsgBox, 64, Excel Step 1, Loading the data.
xl :=   ComObjActive("Excel.Application")
xl.Range["A1:C" rows].Value :=   safeArray
MsgBox, 64, Excel Step 2, Adding Column D.
end :=   xl.Range["C1"].End(xlDown).Row
xl.Range["D1"].Formula :=   "=IF(B1=""a"",1,IF(B1=""bs"",2,IF(B1=""vc"",3,IF(B1=""gg"",4,""N/A""))))"
xl.Range["D1"].AutoFill(xl.Range["D1:D" end]), xl.Range["D1:D" end].Copy(), xl.Range["D1:D" end].PasteSpecial(-4163)
xl.Range["A1"].Select(), xl.CutCopyMode :=   False, xl :=   ""

_________________
Image
Try Quick Search for Autohotkey or see the tutorial for newbies.


Report this post
Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 1 of 2
  [ 24 posts ]  Go to page 1, 2  Next

Board index » AutoHotkey » Support

All times are UTC [ DST ]

Who is online

Users browsing this forum: BrandonHotkey, Edd and 16 guests

You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Powered by phpBB® Forum Software © phpBB Group