If equals info in excel cell then...

Discussions and Tech Support related to website data extraction, screen scraping and data mining using iMacros.
Forum rules
iMacros EOL - Attention!

The renewal maintenance has officially ended for Progress iMacros effective November 20, 2023 and all versions of iMacros are now considered EOL (End-of-Life). The iMacros products will no longer be supported by Progress (aside from customer license issues), and these forums will also no longer be moderated from the Progress side.

Thank you again for your business and support.

Sincerely,
The Progress Team

Before asking a question or reporting an issue:
1. Please review the list of FAQ's.
2. Use the search box (at the top of each forum page) to see if a similar problem or question has already been addressed.
3. Try searching the iMacros Wiki - it contains the complete iMacros reference as well as plenty of samples and tutorials.
4. We can respond much faster to your posts if you include the following information: CLICK HERE FOR IMPORTANT INFORMATION TO INCLUDE IN YOUR POST
Post Reply
Jasta014
Posts: 7
Joined: Mon Jan 09, 2017 11:05 pm

If equals info in excel cell then...

Post by Jasta014 » Tue Jan 10, 2017 3:59 am

Version 11.5
Windows 10
IE 11

If the number I extract from a website is equal to the number in Column 1 of excel I would like to grab the name in Column 2 of excel and paste it back into a field on the website. If it does not equal then check next row until there's a match. Is it possible to perform this? I imagine I must have to use EVAL to check for a match but I am unsure how to set up the EVAL statement below.
TAG POS=26 TYPE=TD ATTR=* EXTRACT=TXT
SET !LOOP 1
SET !DATASOURCE C:\Users\*\Desktop\*.csv
SET !VAR1 EVAL("if({{!COL1}}=={{!EXTRACT}}){{{!COL2}}}else{do nothing};")
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:txtBorrower CONTENT={{!VAR1}}
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: If equals info in excel cell then...

Post by chivracq » Tue Jan 10, 2017 7:17 am

Jasta014 wrote:

Code: Select all

Version 11.5
Windows 10 
IE 11
If the number I extract from a website is equal to the number in Column 1 of excel I would like to grab the name in Column 2 of excel and paste it back into a field on the website. If it does not equal then check next row until there's a match. Is it possible to perform this? I imagine I must have to use EVAL to check for a match but I am unsure how to set up the EVAL statement below.

Code: Select all

TAG POS=26 TYPE=TD ATTR=* EXTRACT=TXT
SET !LOOP 1
SET !DATASOURCE C:\Users\*\Desktop\*.csv
SET !VAR1 EVAL("if({{!COL1}}=={{!EXTRACT}}){{{!COL2}}}else{do nothing};") 
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:txtBorrower CONTENT={{!VAR1}}
Interesting Case, ah-ah...!

Yep, it is possible, well..., everything is possible with iMacros, ah-ah...!

There are several ways of implementing what you want, a bit depending on how many Entries (= Rows) do you have in your DataSource?

1- For, say..., up to 10 Entries, you can handle the whole "Processing" within one Run of your Macro but you will need to "emulate" the Looping through the DataSource to fetch all Entries from '!COL1' as they will be needed for your 'EVAL()' Statement for a "long" 'if else if else if else etc...' or 'case' Statement to return the Row Number to reuse afterwards to go and fetch the corresponding '!COL2' Value.

2- For any Number of Entries in your DataSource you could loop your Macro and handle one Row per Loop until a Match has been found and abort the Looping with 'EVAL()' + MacroError()'. But this Approach is "workable" for about 50 or maybe 100 Max Entries, as looping a Macro 50 or 100 times will take "some time", I would think about 10 sec for 50 Loops approx...

3- The same Approach can be implemented using a '.js' Script but you are on IE and '.js' Scripts are only supported on FF.

4- Approach Nb 4 will work for any Nb of Entries in your DataSource, even if you have a few Thousands, ah-ah...! And the whole Processing will take less than 1 sec, which can be an Argument if Speed is needed in your Case. But it requires a bit of "Preparation" outside iMacros before you can run your Macro, ah-ah...!
It will require that you open your '.CSV' File from Excel or OpenOffice and save the File as an '.HTML' File that you will then open in your Browser either manually or from your Macro in a second Tab and your Macro will switch to it to use it as a "Standard" Web-Page with your Nb extracted from TAB_1 used as Attribute to tag its corresponding Cell in 'COL_1' on TAB_2 and extract the Content of 'COL_2' using "Relative Positioning":

Code: Select all

TAB T=1
TAG POS=26 TYPE=TD ATTR=* EXTRACT=TXT

TAB T=2
'URL GOTO=file:///C:/Users/*/Desktop/Your_DataSource.html
TAG POS=1 TYPE=TD ATTR=TXT:{{!EXTRACT}}
SET !EXTRACT NULL
TAG POS=R1 TYPE=TD ATTR=TXT:* EXTRACT=TXT

TAB T=1
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:txtBorrower CONTENT={{!EXTRACT}}
... With TAB_2 already opened in this Example..., but you should understand the Principle... 8)

Well, good luck and mention for which Approach you choose, and post your final Script... or if you still get stuck...
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE'/'Trial').
- FCI not mentioned: I don't even read the Qt...! (or only to catch Spam!)
- Script & URL help a lot for more "educated" Help...
Jasta014
Posts: 7
Joined: Mon Jan 09, 2017 11:05 pm

Re: If equals info in excel cell then...

Post by Jasta014 » Tue Jan 10, 2017 3:02 pm

Yes I would only have 10 rows, so I am just looking for assistance on setting up the EVAL statement below to handle this? Especially the else statement section. I think the whole thing may be incorrect, I just took a guess at it.

Code: Select all

TAG POS=26 TYPE=TD ATTR=* EXTRACT=TXT
SET !LOOP 1
SET !DATASOURCE C:\Users\*\Desktop\*.csv
SET !VAR1 EVAL("if({{!COL1}}=={{!EXTRACT}}){TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:txtBorrower CONTENT={{!COL2}}}else{do nothing and move to next row until a match is found};") 
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: If equals info in excel cell then...

Post by chivracq » Tue Jan 10, 2017 4:40 pm

Jasta014 wrote:Yes I would only have 10 rows, so I am just looking for assistance on setting up the EVAL statement below to handle this? Especially the else statement section. I think the whole thing may be incorrect, I just took a guess at it.

Code: Select all

TAG POS=26 TYPE=TD ATTR=* EXTRACT=TXT
SET !LOOP 1
SET !DATASOURCE C:\Users\*\Desktop\*.csv
SET !VAR1 EVAL("if({{!COL1}}=={{!EXTRACT}}){TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:txtBorrower CONTENT={{!COL2}}}else{do nothing and move to next row until a match is found};") 
OK then, if you have only 10 Entries, then you can go for my Approach Nb_1:

Code: Select all

TAG POS=26 TYPE=TD ATTR=* EXTRACT=TXT
SET !DATASOURCE C:\Users\*\Desktop\*.csv

SET !DATASOURCE_LINE 1
SET n1 {{!COL1}}
SET !DATASOURCE_LINE 2
SET n2 {{!COL1}}
'...
SET !DATASOURCE_LINE 10
SET n10 {{!COL1}}

SET !VAR1 EVAL("var d='{{!EXTRACT}}'; var z; if(d=='{{n1}}'){z=1;} else if(d=='{{n2}}'){z=2;} else if ... else if(d=='{{n10}}'){z=10;}; z;")
SET !DATASOURCE_LINE {{!VAR1}}
TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:txtBorrower CONTENT={{!COL2}}
Not tested but you've got everything you need, I would think... Use 'PROMPT' to follow your Vars and debug your Script if it doesn't work directly...

EDIT:
"var d='!EXTRACT}}';" must be "var d='{{!EXTRACT}}';" of course... (Corrected...)
Last edited by chivracq on Tue Jan 10, 2017 6:30 pm, edited 2 times in total.
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE'/'Trial').
- FCI not mentioned: I don't even read the Qt...! (or only to catch Spam!)
- Script & URL help a lot for more "educated" Help...
Jasta014
Posts: 7
Joined: Mon Jan 09, 2017 11:05 pm

Re: If equals info in excel cell then...

Post by Jasta014 » Tue Jan 10, 2017 5:07 pm

That did it, thank you!

Just in case anyone else is trying to use this, the EVAL line below was just missing a couple {{

EVAL("var d='!EXTRACT}}';
Jasta014
Posts: 7
Joined: Mon Jan 09, 2017 11:05 pm

Re: If equals info in excel cell then...

Post by Jasta014 » Tue Jan 10, 2017 5:31 pm

Follow up to this. If I set the imacro up for n10 but there only ends up being 5 rows on the excel sheet I get an error. Is there any way to make it stop once it hits the row that matches to avoid this error? Or some sort of check that can be put in place?
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: If equals info in excel cell then...

Post by chivracq » Tue Jan 10, 2017 6:35 pm

Jasta014 wrote:That did it, thank you!

Just in case anyone else is trying to use this, the EVAL line below was just missing a couple {{

EVAL("var d='!EXTRACT}}';
Oh yep, of course...! I typed the whole Script from scratch, and didn't have a chance to test it... I've corrected my previous Post...
Jasta014 wrote:Follow up to this. If I set the imacro up for n10 but there only ends up being 5 rows on the excel sheet I get an error. Is there any way to make it stop once it hits the row that matches to avoid this error? Or some sort of check that can be put in place?
Hum, simply use '!ERRORIGNORE' I would think...
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE'/'Trial').
- FCI not mentioned: I don't even read the Qt...! (or only to catch Spam!)
- Script & URL help a lot for more "educated" Help...
Jasta014
Posts: 7
Joined: Mon Jan 09, 2017 11:05 pm

Re: If equals info in excel cell then...

Post by Jasta014 » Tue Jan 10, 2017 8:47 pm

Oh simple. Thanks again!
Louise17
Posts: 6
Joined: Thu Aug 03, 2017 8:25 am

Re: If equals info in excel cell then...

Post by Louise17 » Wed Aug 30, 2017 9:30 am

Hi Sir,

I dont really know how to post a different topic but i think my issue is somewhat related to this.

I am using windows 10
imacros version 12.05

i am running a series of i macros command to fill out a website form, on the middle of the filling out my code encounters an error (eg. the email address is already existing on the form) therefore it does not allow us to move to the next page of the website but the macro code still goes thru each of the macro coding.

macro = macro + "TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:q9149 CONTENT={{emal}}" + vbNewLine
macro = macro + "TAG POS=1 TYPE=INPUT:HIDDEN FORM=NAME:frmAppForm ATTR=ID:q9150-postback CONTENT={{fam}}" + vbNewLine
macro = macro + "TAG POS=1 TYPE=INPUT:HIDDEN FORM=NAME:frmAppForm ATTR=ID:q9153-postback CONTENT={{F_M_3_C_O_B}}" + vbNewLine
macro = macro + "TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:q9155 CONTENT={{FAMILY_MEMBER_3_OCCUPATION}}" + vbNewLine
macro = macro + "TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:q9156 CONTENT={{FAMILY_MEMBER_3_NAME_OF_EMPLOYER}}" + vbNewLine

sample code above:
say like the bold code is where the error appears, and i want to stop it form runninig so that it could proceed to the next row on the excel rather than continuing with the rest of the macro code but not able to go to the next page (as this would take time)

thanks
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: If equals info in excel cell then...

Post by chivracq » Wed Aug 30, 2017 12:45 pm

Louise17 wrote:Hi Sir,

I dont really know how to post a different topic but i think my issue is somewhat related to this.

I am using

Code: Select all

windows 10 
imacros  version 12.05
i am running a series of i macros command to fill out a website form, on the middle of the filling out my code encounters an error (eg. the email address is already existing on the form) therefore it does not allow us to move to the next page of the website but the macro code still goes thru each of the macro coding.

Code: Select all

 [b] macro = macro + "TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:q9149 CONTENT={{emal}}" + vbNewLine[/b]
  macro = macro + "TAG POS=1 TYPE=INPUT:HIDDEN FORM=NAME:frmAppForm ATTR=ID:q9150-postback CONTENT={{fam}}" + vbNewLine
  macro = macro + "TAG POS=1 TYPE=INPUT:HIDDEN FORM=NAME:frmAppForm ATTR=ID:q9153-postback CONTENT={{F_M_3_C_O_B}}" + vbNewLine
  macro = macro + "TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:q9155 CONTENT={{FAMILY_MEMBER_3_OCCUPATION}}" + vbNewLine
  macro = macro + "TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:q9156 CONTENT={{FAMILY_MEMBER_3_NAME_OF_EMPLOYER}}" + vbNewLine
sample code above:
say like the bold code is where the error appears, and i want to stop it form runninig so that it could proceed to the next row on the excel rather than continuing with the rest of the macro code but not able to go to the next page (as this would take time)

thanks
Oh...!, I didn't know iMB v12.0 had been released, ah-ah...! 8)

Well..., your Post/Case is related to this current Thread if you want to do the Validation from your DataSource, which is one Approach indeed, but hum..., I'm not sure it's the most reliable Approach, you could have already "tried" to use one Entry, and stg went "wrong" when submitting the Form, and that Entry is not "burnt" yet...
I would think you can better let the Site do the Validation (which it obviously already does anyway even if I don't see any 'Submit' Statement in your Script, but it happens maybe on the fly from the 'INPUT' Field...), then check for that Error/Validation Msg from the Page before filling in the next Fields, abort the Form Filling part and start over with the next Entry in your DataSource...

And hum, mini-Remark, I notice you are using 4 different Naming "Conventions" for the 5 Vars in your Script (with a Typo btw for "emal" if it's meant for "email" like I guess), you could better stick to only one Naming Convention for all your Vars... :idea:
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE'/'Trial').
- FCI not mentioned: I don't even read the Qt...! (or only to catch Spam!)
- Script & URL help a lot for more "educated" Help...
Post Reply