If equals info in excel cell then...

Discussions and Tech Support related to website data extraction, screen scraping and data mining using iMacros.

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.

If equals info in excel cell then...

by Jasta014 on Mon Jan 09, 2017 8:59 pm

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}}
Jasta014
 
Posts: 7
Joined: Mon Jan 09, 2017 4:05 pm

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

by chivracq on Tue Jan 10, 2017 12: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)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: 5855
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

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

by Jasta014 on Tue Jan 10, 2017 8:02 am

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};")
Jasta014
 
Posts: 7
Joined: Mon Jan 09, 2017 4:05 pm

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

by chivracq on Tue Jan 10, 2017 9:40 am

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 11:30 am, edited 2 times 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: 5855
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

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

by Jasta014 on Tue Jan 10, 2017 10:07 am

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 4:05 pm

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

by Jasta014 on Tue Jan 10, 2017 10:31 am

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?
Jasta014
 
Posts: 7
Joined: Mon Jan 09, 2017 4:05 pm

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

by chivracq on Tue Jan 10, 2017 11:35 am

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)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: 5855
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

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

by Jasta014 on Tue Jan 10, 2017 1:47 pm

Oh simple. Thanks again!
Jasta014
 
Posts: 7
Joined: Mon Jan 09, 2017 4:05 pm


Return to Data Extraction and Web Screen Scraping

Who is online

Users browsing this forum: No registered users and 2 guests

Website Monitoring