How to define a part of an excel value to write in textbox

Support for iMacros. The iMacros software is the unique solution for automating every activity inside a web browser, for data extraction and web testing.

Moderators: Community Moderators, iMacros Moderators

Forum rules
Before asking a question or reporting an issue:
1. Please review the list of FAQ's.
2. Use the Google search box (at the top of each forum page) to see if a similar problem or question has already been addressed. This will search the entire contents of the forums as well as the iMacros Wiki.
3. We can respond much faster to your posts if you include the following information:

CLICK HERE FOR IMPORTANT INFORMATION TO INCLUDE IN YOUR POST

Answering your own posts (e.g. attempting to "bump" your topic) drops your topic from the list of unanswered threads, so it may actually receive less views.

How to define a part of an excel value to write in textbox

by Thrilling on Wed Aug 23, 2017 1:53 am

Hello,

I'm using iMacros 8.9.7 on FIrefox 45.9.0 ESR on Solaris 11.3 (x64, german)

If possible, I'd like to make iMacros put this birthdate information from one Excel value:
excel.png
excel.png (3.03 KIB) Viewed 532 times

Into these three boxes:
date of birth.png
date of birth.png (1.65 KIB) Viewed 532 times


My idea for the two textboxes would be to tell macros only to look at certain characters inside the excel value to put into the appropiate textbox (for example: put characters 7 to 10 into textbox Year)

For the month-Dropdownmenu it'd have to be more complicated, as writing the value "9" (for September) into the dropdownmenu doesnt change it's value. I'd have to write a replacement list into the code so that iMacros knows that "1" = "January" and so forth. Either that or i'd have to tell iMacros that the value "9" means "press the down-arrow key 9 times" which would have the same result.

For reference: Recording manually inputting the desired Value into the three boxes looks like this:
Code: Select all
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Day CONTENT=22
TAG POS=1 TYPE=SELECT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Month CONTENT=%9
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Year CONTENT=1964


Any help would be greatly appreciated
Thrilling
 
Posts: 20
Joined: Fri Aug 11, 2017 7:02 am

Re: How to define a part of an excel value to write in textb

by chivracq on Wed Aug 23, 2017 3:29 am

Thrilling wrote:Hello,

I'm using
Code: Select all
iMacros 8.9.7 on FIrefox 45.9.0 ESR on Solaris 11.3 (x64, german)


If possible, I'd like to make iMacros put this birthdate information from one Excel value:
excel.png

Into these three boxes:
date of birth.png


My idea for the two textboxes would be to tell macros only to look at certain characters inside the excel value to put into the appropiate textbox (for example: put characters 7 to 10 into textbox Year)

For the month-Dropdownmenu it'd have to be more complicated, as writing the value "9" (for September) into the dropdownmenu doesnt change it's value. I'd have to write a replacement list into the code so that iMacros knows that "1" = "January" and so forth. Either that or i'd have to tell iMacros that the value "9" means "press the down-arrow key 9 times" which would have the same result.

For reference: Recording manually inputting the desired Value into the three boxes looks like this:
Code: Select all
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Day CONTENT=22
TAG POS=1 TYPE=SELECT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Month CONTENT=%9
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Year CONTENT=1964


Any help would be greatly appreciated

Yep, you are on the right way and it's not very complicated...
You'll need to use 'EVAL()' to isolate the 3 parts of the Date to fit the 3 Fields on the Site.
You can then use 'substr()' or 'substring()' to isolate each part by their Index like you mention for 'Year' with [7-10], that's a perfect way to do it as the String for the Date in your DataSource always contains 10 Chars thanks to the Day and Month using a leading "0" for [01-09].
(Another Solution would be using 'split()', but your Idea is already perfect...)

For the Month part, I first thought you would need more or less the same Functionality like this other User in this recent Thread who unfortunately didn't mention their FCI and didn't follow up, so I didn't post my Solution, but nope, not even, your Case is even more simple if you notice that for Septb for example (="09" in your DataSource), the Field on the Site expects "9" when selecting by Value, which means you simply need to convert [01-09] to [1-9] by removing the leading Zero.

Now it's time for you to start using 'EVAL()', ah-ah...! Good luck, but it's not very complicated... :wink:

EDIT: 'subStr()' and 'subString()' are completely lowercase..., oops...! :oops: => 'substr()' and 'substring()' [Corrected...]
Last edited by chivracq on Wed Aug 30, 2017 7:47 am, edited 1 time in total.
- (F)CIM = (Full) Config Info Missing: iMacros + Browser + OS with all 3 Versions...
- I usually don't even read the Question if that (required) Info is not mentioned...
- Script & URL usually help a lot for a more "educated" Help...
chivracq
 
Posts: 6473
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: How to define a part of an excel value to write in textb

by Thrilling on Wed Aug 30, 2017 7:35 am

I managed to figure out the first Problem, but am still stuck with the month.

Solution:
Code: Select all
VERSION BUILD=8970419 RECORDER=FX
TAB T=1
SET !DATASOURCE_DELIMITER ;
SET !DATASOURCE Address.csv
SET !DATASOURCE_LINE 2

SET !VAR1 EVAL("var test = \"{{!col5}}\"; var y = test.substring(0,2); y")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Day content={{!var1}}

SET !VAR3 EVAL("var test = \"{{!col5}}\"; var y = test.substring(6,10); y")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Year content={{!var3}}


The month problem is still causing me trouble.

My idea is to write an "IF, THEN" command for each number "01" up to "09" and if its a match to substr the 0, ELSE dont change it.

1. I don't know the syntax/command to make eval compare if, for example "y = 04"
2. the website thinks that 13=december, 12=november and so forth, meaning i'll have to increase the result by 1, I don't know the snytax/command for this


In any case, thank you very much for your help so far chivracq
Thrilling
 
Posts: 20
Joined: Fri Aug 11, 2017 7:02 am

Re: How to define a part of an excel value to write in textb

by chivracq on Wed Aug 30, 2017 8:18 am

Thrilling wrote:I managed to figure out the first Problem, but am still stuck with the month.

Solution:
Code: Select all
VERSION BUILD=8970419 RECORDER=FX
TAB T=1
SET !DATASOURCE_DELIMITER ;
SET !DATASOURCE Address.csv
SET !DATASOURCE_LINE 2

SET !VAR1 EVAL("var test = \"{{!col5}}\"; var y = test.substring(0,2); y")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Day content={{!var1}}

SET !VAR3 EVAL("var test = \"{{!col5}}\"; var y = test.substring(6,10); y")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Year content={{!var3}}


The month problem is still causing me trouble.

My idea is to write an "IF, THEN" command for each number "01" up to "09" and if its a match to substr the 0, ELSE dont change it.

1. I don't know the syntax/command to make eval compare if, for example "y = 04"
2. the website thinks that 13=december, 12=november and so forth, meaning i'll have to increase the result by 1, I don't know the snytax/command for this

In any case, thank you very much for your help so far chivracq

Yep, very good, and very close..., some IF/ELSE on the leading Zero is indeed a Possibility but here is one easier Solution for Month:
Code: Select all
SET COL_5 "22.09.1964"

SET DOB_Month EVAL("var s='{{COL_5}}'; var x,y,z; x=s.substring(3,5); z=(x*1)+1; z;")
PROMPT COL_5:<SP>_{{COL_5}}_<BR>DOB_Month:<SP>_{{DOB_Month}}_
(Tested on iMacros for FF v8.8.2, Pale Moon v26.3.3 (=FF47), Win10-x64.)

=> Replace my 'COL_5' with your '!COL5' of course...
- (F)CIM = (Full) Config Info Missing: iMacros + Browser + OS with all 3 Versions...
- I usually don't even read the Question if that (required) Info is not mentioned...
- Script & URL usually help a lot for a more "educated" Help...
chivracq
 
Posts: 6473
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: How to define a part of an excel value to write in textb

by Thrilling on Fri Sep 01, 2017 6:24 am

Works like a charm, thank you very much.
Removing the "0" by running it through calculation.. neat!

Code: Select all
VERSION BUILD=8970419 RECORDER=FX
TAB T=1
SET !DATASOURCE_DELIMITER ;
SET !DATASOURCE Address.csv
SET !DATASOURCE_LINE 2


SET !VAR1 EVAL("var test = \"{{!col5}}\"; var y = test.substring(0,2); y")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Day content={{!var1}}

SET !var2 EVAL("var s='{{!COL5}}'; var x,y,z; x=s.substring(3,5); z=(x*1)+1; z;")
TAG POS=1 TYPE=SELECT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Month CONTENT={{!var2}}

SET !VAR3 EVAL("var test = \"{{!col5}}\"; var y = test.substring(6,10); y")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Year content={{!var3}}
Thrilling
 
Posts: 20
Joined: Fri Aug 11, 2017 7:02 am

Re: How to define a part of an excel value to write in textb

by chivracq on Fri Sep 01, 2017 7:13 am

Thrilling wrote:Works like a charm, thank you very much.
Removing the "0" by running it through calculation.. neat!

Code: Select all
VERSION BUILD=8970419 RECORDER=FX
TAB T=1
SET !DATASOURCE_DELIMITER ;
SET !DATASOURCE Address.csv
SET !DATASOURCE_LINE 2

SET !VAR1 EVAL("var test = \"{{!col5}}\"; var y = test.substring(0,2); y")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Day content={{!var1}}

SET !var2 EVAL("var s='{{!COL5}}'; var x,y,z; x=s.substring(3,5); z=(x*1)+1; z;")
TAG POS=1 TYPE=SELECT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Month CONTENT={{!var2}}

SET !VAR3 EVAL("var test = \"{{!col5}}\"; var y = test.substring(6,10); y")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Year content={{!var3}}

OK..., good to hear...! :D

"Removing the "0" by running it through calculation.. neat!"
=> Yep, I find it the easiest (and shortest) way to convert a String to a Number (and JavaScript indeed automatically removes leading Zeros, even if I've never tested if this Syntax/Trick works as well in other Browsers than FF) rather than using 'parseInt()' / 'Number()' / 'toNumber()' that I never remember how to spell correctly and what the exact Syntax is...

I had mentioned at the beginning of the Thread that your Solution with 'substring()' (or 'substr()') was perfect, but that you could use 'split()' as well, that I find easier (and more flexible/reliable) to use, so here is how your Script would look like with 'split()'...:
Code: Select all
VERSION BUILD=8970419 RECORDER=FX
TAB T=1
SET !DATASOURCE_DELIMITER ;
SET !DATASOURCE Address.csv
SET !DATASOURCE_LINE 2

SET !VAR1 EVAL("var s='{{!COL5}}'; var x,y,z; x=s.split('.'); z=x[0]; z;")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Day content={{!var1}}

SET !VAR2 EVAL("var s='{{!COL5}}'; var x,y,z; x=s.split('.'); y=x[1]; z=(y*1)+1; z;")
TAG POS=1 TYPE=SELECT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Month CONTENT={{!var2}}

SET !VAR3 EVAL("var s='{{!COL5}}'; var x,y,z; x=s.split('.'); z=x[2]; z;")
TAG POS=1 TYPE=INPUT:TEXT FORM=ID:aspnetForm ATTR=ID:ctl00_phBody_StepViewMPersonalDetailsStep_BirthDate_Year content={{!var3}}
(Not tested...)
- (F)CIM = (Full) Config Info Missing: iMacros + Browser + OS with all 3 Versions...
- I usually don't even read the Question if that (required) Info is not mentioned...
- Script & URL usually help a lot for a more "educated" Help...
chivracq
 
Posts: 6473
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: How to define a part of an excel value to write in textb

by Thrilling on Fri Sep 01, 2017 8:35 am

ah, so you can determine a character as the separator and select each segment.. nifty tool. I'll keep it in mind for further usage. :) Thank you
Thrilling
 
Posts: 20
Joined: Fri Aug 11, 2017 7:02 am

Re: How to define a part of an excel value to write in textb

by chivracq on Fri Sep 01, 2017 9:33 am

Thrilling wrote:ah, so you can determine a character as the separator and select each segment.. nifty tool. I'll keep it in mind for further usage. :) Thank you

Yep, you can use one single Char as the "Separator", or a Space, or a complete String even, and you can access each "Segment" with 'x[0]', 'x[1]', 'x[2]', etc...
I find 'split()' a very powerful Command, it can be used to deal with Dynamic ID's, dynamically find the Max_Nb of Entries in a DDLB, I use it as a Substitute for the 'SEARCH' Command, to do a Global 'replace()' without the Need for complex 'REGEXP', it can check if a String exists in a DataSource and identify its corresponding Row_Nb in 0.5 sec for 1000 Rows while the best Solution I've seen in JS needs about 2 min to do the same Task, etc..., there are many-many Applications for this Command... 8)
- (F)CIM = (Full) Config Info Missing: iMacros + Browser + OS with all 3 Versions...
- I usually don't even read the Question if that (required) Info is not mentioned...
- Script & URL usually help a lot for a more "educated" Help...
chivracq
 
Posts: 6473
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)


Return to General Support & Discussions

Who is online

Users browsing this forum: No registered users and 4 guests

-->