Page 1 of 1
Help with Excel extraction and input
Posted: Sun Oct 18, 2009 7:41 am
by snwskier2
I'm having a difficult time figuring out how to do the below task. Here is the scenario.I
have an database in Excel 'sheet1' consisting of properties and property contacts. Each row contains a property and related contacts. 1. I
need to extract their "lastname" "firstname" "city" "state" & maybe
"zipcode" from Excel, find their contact information via Anywho.com,
2. then paste the one or many results (i.e. name, address, and phone number) along with the corresponding Property "PIN", in a xls sheet2 same workbook. 2.5 there may be one or more than one related contact for any one property, all property related contacts are on the same row. 3.Needs to run until it reaches the last property PIN.Example (xls sheet1) PIN lastname firstname city state zip lastname2 firstname2 city21212123123 Doe John Chicago IL 60601 Smith James PlainfieldI do appreciate any help, thank you.
Help with Excel extraction and input
Posted: Thu Nov 05, 2009 4:51 am
by Oleg
I wrote small example (see below).
This task processes Excel table with 5 columns
You should select top-left cell and press Ctrl-Alt-J
The task put each value into variable and moves the selection to next cell. After 5 cells it moves the selection to next row and etc.
Then the value will be empty the task will stop.
Of course, you may change the Hot-Key and amount of amount of columns.
;*******************************
;* RoboTask Task file &n bsp;*
;* Do not edit in text editor! *
;*******************************
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|1380072790
ContinueOnError=INTEGER|0
Events=FOLDER
ExternalName=STRING|"Task871"
Hide=INTEGER|0
ID=INTEGER|1483449583
LocalVariables=STRING|"a1,a2,a3,a4,a5"
LogOnAsUser=INTEGER|1
Name=STRING|"Excel input"
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0
[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action12=FOLDER
Action13=FOLDER
Action14=FOLDER
Action15=FOLDER
Action16=FOLDER
Action17=FOLDER
Action18=FOLDER
Action19=FOLDER
Action2=FOLDER
Action20=FOLDER
Action21=FOLDER
Action22=FOLDER
Action23=FOLDER
Action24=FOLDER
Action25=FOLDER
Action26=FOLDER
Action27=FOLDER
Action28=FOLDER
Action29=FOLDER
Action3=FOLDER
Action30=FOLDER
Action31=FOLDER
Action32=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER
[Actions\Action1]
ActionID=STRING|"A_LOOP_WHILE"
Enabled=INTEGER|-1
Name=STRING|"While loop"
Params=FOLDER
[Actions\Action1\Params]
case=STRING|"0"
operator=STRING|"0"
type=STRING|"3"
value1=STRING|"true"
[Actions\Action10]
ActionID=STRING|"A_CLIPBOARD_COPY"
Enabled=INTEGER|-1
Name=STRING|"Copy"
Params=FOLDER
[Actions\Action10\Params]
mode=STRING|"1"
[Actions\Action11]
ActionID=STRING|"A_CLIPBOARD_GET"
Enabled=INTEGER|-1
Name=STRING|"Get Text"
Params=FOLDER
[Actions\Action11\Params]
variable=STRING|"a2"
[Actions\Action12]
ActionID=STRING|"A_STR_TRIM"
Enabled=INTEGER|-1
Name=STRING|"STR Trim"
Params=FOLDER
[Actions\Action12\Params]
custom=STRING|"0"
eol=STRING|"1"
lead=STRING|"1"
source=STRING|"{a2}"
spaces=STRING|"1"
tabs=STRING|"1"
trailing=STRING|"1"
variable=STRING|"a2"
[Actions\Action13]
ActionID=STRING|"A_GENERAL_PAUSE"
Enabled=INTEGER|-1
Name=STRING|"Delay 200 ms"
Params=FOLDER
[Actions\Action13\Params]
delay=STRING|"2"
[Actions\Action14]
ActionID=STRING|"A_GENERAL_SENDKEYS"
Enabled=INTEGER|-1
Name=STRING|"Send keys to active window"
Params=FOLDER
[Actions\Action14\Params]
currentwindow=STRING|"1"
fixedwindow=STRING|"1"
keylayout=STRING|"0"
keys=STRING|"{RIGHT}"
sendkind=STRING|"0"
wincaption=STRING|"Notepad"
[Actions\Action15]
ActionID=STRING|"A_CLIPBOARD_COPY"
Enabled=INTEGER|-1
Name=STRING|"Copy"
Params=FOLDER
[Actions\Action15\Params]
mode=STRING|"1"
[Actions\Action16]
ActionID=STRING|"A_CLIPBOARD_GET"
Enabled=INTEGER|-1
Name=STRING|"Get Text"
Params=FOLDER
[Actions\Action16\Params]
variable=STRING|"a3"
[Actions\Action17]
ActionID=STRING|"A_STR_TRIM"
Enabled=INTEGER|-1
Name=STRING|"STR Trim"
Params=FOLDER
[Actions\Action17\Params]
custom=STRING|"0"
eol=STRING|"1"
lead=STRING|"1"
source=STRING|"{a3}"
spaces=STRING|"1"
tabs=STRING|"1"
trailing=STRING|"1"
variable=STRING|"a3"
[Actions\Action18]
ActionID=STRING|"A_GENERAL_PAUSE"
Enabled=INTEGER|-1
Name=STRING|"Delay 200 ms"
Params=FOLDER
[Actions\Action18\Params]
delay=STRING|"2"
[Actions\Action19]
ActionID=STRING|"A_GENERAL_SENDKEYS"
Enabled=INTEGER|-1
Name=STRING|"Send keys to active window"
Params=FOLDER
[Actions\Action19\Params]
currentwindow=STRING|"1"
fixedwindow=STRING|"1"
keylayout=STRING|"0"
keys=STRING|"{RIGHT}"
sendkind=STRING|"0"
wincaption=STRING|"Notepad"
[Actions\Action2]
ActionID=STRING|"A_CLIPBOARD_COPY"
Enabled=INTEGER|-1
Name=STRING|"Copy"
Params=FOLDER
[Actions\Action2\Params]
mode=STRING|"1"
[Actions\Action20]
ActionID=STRING|"A_CLIPBOARD_COPY"
Enabled=INTEGER|-1
Name=STRING|"Copy"
Params=FOLDER
[Actions\Action20\Params]
mode=STRING|"1"
[Actions\Action21]
ActionID=STRING|"A_CLIPBOARD_GET"
Enabled=INTEGER|-1
Name=STRING|"Get Text"
Params=FOLDER
[Actions\Action21\Params]
variable=STRING|"a4"
[Actions\Action22]
ActionID=STRING|"A_STR_TRIM"
Enabled=INTEGER|-1
Name=STRING|"STR Trim"
Params=FOLDER
[Actions\Action22\Params]
custom=STRING|"0"
eol=STRING|"1"
lead=STRING|"1"
source=STRING|"{a4}"
spaces=STRING|"1"
tabs=STRING|"1"
trailing=STRING|"1"
variable=STRING|"a4"
[Actions\Action23]
ActionID=STRING|"A_GENERAL_PAUSE"
Enabled=INTEGER|-1
Name=STRING|"Delay 200 ms"
Params=FOLDER
[Actions\Action23\Params]
delay=STRING|"2"
[Actions\Action24]
ActionID=STRING|"A_GENERAL_SENDKEYS"
Enabled=INTEGER|-1
Name=STRING|"Send keys to active window"
Params=FOLDER
[Actions\Action24\Params]
currentwindow=STRING|"1"
fixedwindow=STRING|"1"
keylayout=STRING|"0"
keys=STRING|"{RIGHT}"
sendkind=STRING|"0"
wincaption=STRING|"Notepad"
[Actions\Action25]
ActionID=STRING|"A_CLIPBOARD_COPY"
Enabled=INTEGER|-1
Name=STRING|"Copy"
Params=FOLDER
[Actions\Action25\Params]
mode=STRING|"1"
[Actions\Action26]
ActionID=STRING|"A_CLIPBOARD_GET"
Enabled=INTEGER|-1
Name=STRING|"Get Text"
Params=FOLDER
[Actions\Action26\Params]
variable=STRING|"a5"
[Actions\Action27]
ActionID=STRING|"A_STR_TRIM"
Enabled=INTEGER|-1
Name=STRING|"STR Trim"
Params=FOLDER
[Actions\Action27\Params]
custom=STRING|"0"
eol=STRING|"1"
lead=STRING|"1"
source=STRING|"{a5}"
spaces=STRING|"1"
tabs=STRING|"1"
trailing=STRING|"1"
variable=STRING|"a5"
[Actions\Action28]
ActionID=STRING|"A_GENERAL_PAUSE"
Enabled=INTEGER|-1
Name=STRING|"Delay 200 ms"
Params=FOLDER
[Actions\Action28\Params]
delay=STRING|"2"
[Actions\Action29]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{a1}"""
Params=FOLDER
[Actions\Action29\Params]
icon=STRING|"1"
msg0=STRING|"{a1}"
msg1=STRING|"{a2}"
msg2=STRING|"{a3}"
msg3=STRING|"{a4}"
msg4=STRING|"{a5}"
msgcount=STRING|"5"
playsound=STRING|"0"
showmessage=STRING|"1"
[Actions\Action3]
ActionID=STRING|"A_CLIPBOARD_GET"
Enabled=INTEGER|-1
Name=STRING|"Get Text"
Params=FOLDER
[Actions\Action3\Params]
variable=STRING|"a1"
[Actions\Action30]
ActionID=STRING|"A_GENERAL_PAUSE"
Enabled=INTEGER|-1
Name=STRING|"Delay 500 ms"
Params=FOLDER
[Actions\Action30\Params]
delay=STRING|"5"
[Actions\Action31]
ActionID=STRING|"A_GENERAL_SENDKEYS"
Enabled=INTEGER|-1
Name=STRING|"Send keys to active window"
Params=FOLDER
[Actions\Action31\Params]
currentwindow=STRING|"1"
fixedwindow=STRING|"1"
keylayout=STRING|"0"
keys=STRING|"{DOWN}{LEFT}{LEFT}{LEFT}{LEFT}"
sendkind=STRING|"0"
wincaption=STRING|"Notepad"
[Actions\Action32]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action4]
ActionID=STRING|"A_STR_TRIM"
Enabled=INTEGER|-1
Name=STRING|"STR Trim"
Params=FOLDER
[Actions\Action4\Params]
custom=STRING|"0"
eol=STRING|"1"
lead=STRING|"1"
source=STRING|"{a1}"
spaces=STRING|"1"
tabs=STRING|"1"
trailing=STRING|"1"
variable=STRING|"a1"
[Actions\Action5]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER
[Actions\Action5\Params]
case=STRING|"0"
operator=STRING|"7"
type=STRING|"0"
value1=STRING|"{a1}"
[Actions\Action6]
ActionID=STRING|"A_LOOP_BREAK"
Enabled=INTEGER|-1
Name=STRING|"Break"
[Actions\Action7]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
[Actions\Action8]
ActionID=STRING|"A_GENERAL_PAUSE"
Enabled=INTEGER|-1
Name=STRING|"Delay 200 ms"
Params=FOLDER
[Actions\Action8\Params]
delay=STRING|"2"
[Actions\Action9]
ActionID=STRING|"A_GENERAL_SENDKEYS"
Enabled=INTEGER|-1
Name=STRING|"Send keys to active window"
Params=FOLDER
[Actions\Action9\Params]
currentwindow=STRING|"1"
fixedwindow=STRING|"1"
keylayout=STRING|"0"
keys=STRING|"{RIGHT}"
sendkind=STRING|"0"
wincaption=STRING|"Notepad"
[Events]
Event1=FOLDER
[Events\Event1]
Enabled=INTEGER|-1
EventID=STRING|"E_GENERAL_HOTKEY"
Params=FOLDER
[Events\Event1\Params]
hotkey=STRING|"49226"