Checking for a word in .csv file

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
imacronewbie22
Posts: 20
Joined: Wed Dec 14, 2016 10:57 pm

Checking for a word in .csv file

Post by imacronewbie22 » Fri Jun 30, 2017 12:53 pm

CIM iMacros for FF v9.0.3 Firefox 54.0 Windows 10

I want to check some data from .csv file with random word from site. If that word exists - to do something and if the word doesn't exist - to do something else. But I didn't find anything similar. EVAL function should do that but I don't know how to check whole .csv

Example:
.csv file example.csv

content from example.csv is

science
cubic
virtual
internet
form
future

If I check the word science then it does one operation and if the word is clinic then something else. Is this possible with iMacro and .csv file

If yes, just give me some directions, please. If not, sorry for taking your time, simple no is just fine.

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

Re: Checking for a word in .csv file

Post by chivracq » Fri Jun 30, 2017 3:49 pm

imacronewbie22 wrote:CIM

Code: Select all

iMacros for FF v9.0.3 Firefox 54.0 Windows 10
I want to check some data from .csv file with random word from site. If that word exists - to do something and if the word doesn't exist - to do something else. But I didn't find anything similar. EVAL function should do that but I don't know how to check whole .csv

Example:
.csv file example.csv

content from example.csv is

Code: Select all

science
cubic
virtual
internet
form
future
If I check the word science then it does one operation and if the word is clinic then something else. Is this possible with iMacro and .csv file

If yes, just give me some directions, please. If not, sorry for taking your time, simple no is just fine.

Cheers !
The "M" in "CIM" stands for "Missing" btw... :wink:
But perfect of course that you mention your "FCI"... (= "Full Config Info")

But yep, what you want can be done, and in different ways actually... (+ Several similar Threads on the Forum already...)

1- You are going to need some Conditional Behaviour with your "if/else" part, meaning you will probably need a '.js' Script. If you already use a '.js' Script, you can then loop through all Rows in your '.CSV' until you find a "Match" with the Word you want to check.
This Method works fine if you have say up to 10 or 20 Rows/Keywords in your '.CSV', but it quickly becomes a bit slow if you have more Rows and it could take up to approx 20 sec for 100 Rows.

2- One "simple" Trick is to enclose the whole Content of your '.CSV' between Double Quotes with a leading Double Quote before the first Word and another one after the last Word:

Code: Select all

"science
cubic
virtual
internet
form
future"
iMacros will then "see" your whole '.CSV' as one Cell that you can retrieve with one single Statement on '{{!COL1}}'.

3- If you can maintain the Content of your '.CSV' "horizontally" (=> all Keywords in the same Row), other Advanced User @Shugar posted an interesting Method on the SOF Forum (Stackoverflow) where you can retrieve the whole Content of one single Row in just one Statement by using some Temp "fake" '!DATASOURCE_DELIMITER'. (This Method would only work on FF.)

4- By saving your '.CSV' with the '.TXT' Extension (by simply renaming it) or as '.HTML' from Excel/OO, you can open the File in a second Tab in FF from your Macro and treat it like any "Standard" Web-Page that you then can extract with the 'EXTRACT' Mechanism.

5- And a few other Methods are of course if you only have 10-20 Keywords, you can hardcode to retrieve them Row by Row in your Macro, or instead of using a '.CSV' you could hardcode all your Keywords in one String or Array Var directly in your Macro, or you can use your OS Clipboard to first copy all Cells from Excel/OO before starting your Macro.

Well, => already 7 different Methods, hum, or 8 even if '.TXT'/'.HTML' count for 2, you have enough to choose, I would think...!? :wink:
- (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...
imacronewbie22
Posts: 20
Joined: Wed Dec 14, 2016 10:57 pm

Re: Checking for a word in .csv file

Post by imacronewbie22 » Fri Jun 30, 2017 5:27 pm

Thank you for your help. I like your humor :D Cheers again
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Checking for a word in .csv file

Post by chivracq » Fri Jun 30, 2017 6:59 pm

imacronewbie22 wrote:Thank you for your help. I like your humor :D Cheers again
Hum, glad I could make you smile, ah-ah...!, welcome to the Fan-Club...! :D

Will be nice after you've "decided" between all Methods if you can post for which one you went (and/or which (other) one(s) you "considered"), and of course if you can then share your Final Script to make this Thread useful for other Users searching the Forum... :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...
imacronewbie22
Posts: 20
Joined: Wed Dec 14, 2016 10:57 pm

Re: Checking for a word in .csv file

Post by imacronewbie22 » Sat Jul 01, 2017 9:29 am

Sure, I will share the solution when I finish everything. But I have a small problem. I decided to start with your simple trick option 2. I put double quotes where is needed. However, it still presses the button when needed and when not. Do you have any idea what's wrong here? I want it to press the button only in case extracted word exists in example.csv

Code: Select all

SET !DATASOURCE example.csv
SET !VAR1 "{{!COL1}}"
PROMPT {{!VAR1}}
TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 "{{!EXTRACT}}"
PROMPT {{!VAR2}}
SET Nb_1 {{!VAR1}}
SET Nb_2 {{!VAR2}}
SET LABEL EVAL("var n1='{{Nb_1}}', n2='{{Nb_2}}'; if((n1)=(n2)){x=1;} else{x=0;} x;")

TAG POS=R1 TYPE=LABEL ATTR=TXT:Press
Thanks in advance
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Checking for a word in .csv file

Post by chivracq » Sat Jul 01, 2017 5:44 pm

imacronewbie22 wrote:Sure, I will share the solution when I finish everything. But I have a small problem. I decided to start with your simple trick option 2. I put double quotes where is needed. However, it still presses the button when needed and when not. Do you have any idea what's wrong here? I want it to press the button only in case extracted word exists in example.csv

Code: Select all

SET !DATASOURCE example.csv
SET !VAR1 "{{!COL1}}"
PROMPT {{!VAR1}}
TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 "{{!EXTRACT}}"
PROMPT {{!VAR2}}
SET Nb_1 {{!VAR1}}
SET Nb_2 {{!VAR2}}
SET LABEL EVAL("var n1='{{Nb_1}}', n2='{{Nb_2}}'; if((n1)=(n2)){x=1;} else{x=0;} x;")

TAG POS=R1 TYPE=LABEL ATTR=TXT:Press
Thanks in advance
Yeah but I see Double Quotes around '{{!COL1}}' in your Script, that's not what I meant, the Double Quotes need to be put inside the '.CSV' File itself, from Excel/OO or maybe Notepad as Excel can act a bit "weird" with the Double Quotes and "decide" automatically it's a Typo or doesn't correspond to the Format of a Cell/Column...
But you should be able to see in your 'PROMPT' if you indeed got the whole Content of the '.CSV' or only 1 Word...

Then you'll need to use 'EVAL()' to re-separate all Words from your File, but I don't see any 'EVAL()' Statement for that...

And in your 'LABEL' Statement, be careful btw when using iMacros "reserved" (Command) Names for your own Vars, 'LABEL' is a Command/Parameter Name for the 'STOPWATCH' Command...
But your 'if' Statement looks strange to me, more correct would be I think:

Code: Select all

SET My_LABEL EVAL("var n1='{{Nb_1}}', n2='{{Nb_2}}'; if(n1==n2){x=1;} else{x=0;} x;")
I guess you are trying to reuse some Statement I once produced to compare Numbers, but your "juggling" and Use of Vars is a bit cumbersome...
- (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...
imacronewbie22
Posts: 20
Joined: Wed Dec 14, 2016 10:57 pm

Re: Checking for a word in .csv file

Post by imacronewbie22 » Sat Jul 01, 2017 11:07 pm

Yes you are right. 99% of all of my solutions are from your script. I removed the quotes from the script but it's still not working. Your trick with quotes works - with prompt I get everything from csv, but I'm not sure what more I should do to make script able to search for the word in example.csv

Code: Select all

SET !DATASOURCE example.csv
SET !VAR1 {{!COL1}}
PROMPT {{!VAR1}}
TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 {{!EXTRACT}}
PROMPT {{!VAR2}}
SET Nb_1 {{!VAR1}}
SET Nb_2 {{!VAR2}}
SET My_LABEL EVAL("var n1='{{Nb_1}}', n2='{{Nb_2}}'; if(n1===n2){x=1;} else{x=0;} x;")
TAG POS=R1 TYPE=My_LABEL ATTR=TXT:Unfollow
I am relatively new to iMacro. I know some simple things , but nothing with java script. I learn, but slowly. Can you help me to achieve this?

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

Re: Checking for a word in .csv file

Post by chivracq » Sun Jul 02, 2017 6:48 am

imacronewbie22 wrote:Yes you are right. 99% of all of my solutions are from your script. I removed the quotes from the script but it's still not working. Your trick with quotes works - with prompt I get everything from csv, but I'm not sure what more I should do to make script able to search for the word in example.csv

Code: Select all

SET !DATASOURCE example.csv
SET !VAR1 {{!COL1}}
PROMPT {{!VAR1}}
TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 {{!EXTRACT}}
PROMPT {{!VAR2}}
SET Nb_1 {{!VAR1}}
SET Nb_2 {{!VAR2}}
SET My_LABEL EVAL("var n1='{{Nb_1}}', n2='{{Nb_2}}'; if(n1===n2){x=1;} else{x=0;} x;")
TAG POS=R1 TYPE=My_LABEL ATTR=TXT:Unfollow
I am relatively new to iMacro. I know some simple things , but nothing with java script. I learn, but slowly. Can you help me to achieve this?

Thanks
"... but I'm not sure what more I should do to make script able to search for the word in example.csv..."
=> Hum..., I said before you had to use some 'EVAL()' Statement to re-separate your Keywords from what you now have in '{{!COL1}}, ah-ah...!
(I usually always use 'split()' myself that I find the easiest and most powerful JS Command to use, ah-ah......., Grrrr...., Gniarf-Gniarf...!!!, POWER....~~~!!!! [ENF_OF_MENTAL_BREAKDOWN] :oops: )

But yep indeed, this is probably the most difficult part in "This Solution"...
=> But hum..., you need to come up with Solutions/or Suggestions at least, wait...........!
=> ........ I stop the World to think for 10 sec... ... and I find 5 Solutions, I trust you can find at least one...! :idea:

To answer your Qt:
'Can you help me to achieve this?"
=> Pfff..., in your Dreams, ah-ah...! :twisted:
Read a few Pages of Forums Threads and you'll quickly know more about iMacros than I do, ah-ah...!! :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...
imacronewbie22
Posts: 20
Joined: Wed Dec 14, 2016 10:57 pm

Re: Checking for a word in .csv file

Post by imacronewbie22 » Sun Jul 02, 2017 7:30 pm

You are right and I found some solution to check is there a word. Now my problem is how to make script to know when to press or not to press. No or Yes. And if yes I need to save this word in somefile.csv Can you give me some advice?

Code: Select all

SET !DATASOURCE example.csv
SET !VAR1 {{!COL1}}

TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 {{!EXTRACT}}
SET !VAR1 EVAL("var m = '{{!COL1}}'.match(/\\b{{!EXTRACT}}\\b/ig); if (!m) 'No'; else 'Yes';")
TAG POS=1 TYPE=LABEL ATTR=TXT:Press
SAVEAS TYPE=EXTRACT FOLDER=C:\Documents\iMacros\DATASOURCES FILE=somefile.csv
Thank you for all your help
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Checking for a word in .csv file

Post by chivracq » Sun Jul 02, 2017 7:49 pm

imacronewbie22 wrote:You are right and I found some solution to check is there a word. Now my problem is how to make script to know when to press or not to press. No or Yes. And if yes I need to save this word in somefile.csv Can you give me some advice?

Code: Select all

SET !DATASOURCE example.csv
SET !VAR1 {{!COL1}}

TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 {{!EXTRACT}}
SET !VAR1 EVAL("var m = '{{!COL1}}'.match(/\\b{{!EXTRACT}}\\b/ig); if (!m) 'No'; else 'Yes';")
TAG POS=1 TYPE=LABEL ATTR=TXT:Press
SAVEAS TYPE=EXTRACT FOLDER=C:\Documents\iMacros\DATASOURCES FILE=somefile.csv
Thank you for all your help
Very good...!! :D

Well, now instead of "spitting out" a 'Yes' / 'No' and you don't do anything with them, spit out a '1' / '0' to reuse with 'POS=n' for the Click on your 'LABEL', 'POS=0' won't do anything anyway... (with '!ERRORIGNORE' of course...)
- (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...
imacronewbie22
Posts: 20
Joined: Wed Dec 14, 2016 10:57 pm

Re: Checking for a word in .csv file

Post by imacronewbie22 » Sun Jul 02, 2017 8:56 pm

Hi thank you but this doesn't work properly for now. When I put POS=n it doesn't press the button at all.

Code: Select all

SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !DATASOURCE example.csv
SET !VAR1 {{!COL1}}
TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 {{!EXTRACT}}
SET !VAR1 EVAL("var m = '{{!COL1}}'.match(/\\b{{!EXTRACT}}\\b/ig); if (!m) '0'; else '1';")
TAG POS=n TYPE=LABEL ATTR=TXT:Press
SAVEAS TYPE=EXTRACT FOLDER=C:\Documents\iMacros\DATASOURCES FILE=somefile.csv
And I thought that the problem is in the m-n. I change m to n but still doesn't work.

Code: Select all

SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !DATASOURCE example.csv
SET !VAR1 {{!COL1}}
TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 {{!EXTRACT}}
SET !VAR1 EVAL("var n = '{{!COL1}}'.match(/\\b{{!EXTRACT}}\\b/ig); if (!n) '0'; else '1';")
TAG POS=n TYPE=LABEL ATTR=TXT:Press
SAVEAS TYPE=EXTRACT FOLDER=C:\Documents\iMacros\DATASOURCES FILE=somefile.csv
Do you think that script should look like this or I am missing something as usual :D
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Checking for a word in .csv file

Post by chivracq » Sun Jul 02, 2017 9:18 pm

Ah-ah, nearly funny, oops, sorry...!

My 'POS=n' was "generic" as an Explanation, but you must reuse your own Var;

Code: Select all

TAG POS={{!VAR1}} TYPE=LABEL ATTR=TXT:Press
- (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...
imacronewbie22
Posts: 20
Joined: Wed Dec 14, 2016 10:57 pm

Re: Checking for a word in .csv file

Post by imacronewbie22 » Sun Jul 02, 2017 9:35 pm

Thank you. We did it :D

Code: Select all

SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !DATASOURCE example.csv
SET !VAR1 {{!COL1}}
TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 {{!EXTRACT}}
SET !VAR1 EVAL("var n = '{{!COL1}}'.match(/\\b{{!EXTRACT}}\\b/ig); if (!n) '0'; else '1';")
TAG POS={{!VAR1}} TYPE=LABEL ATTR=TXT:Press
SAVEAS TYPE=EXTRACT FOLDER=C:\Documents\iMacros\DATASOURCES FILE=somefile.csv
Thank you for your continued support on Sunday. Have a nice week ahead
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Checking for a word in .csv file

Post by chivracq » Sun Jul 02, 2017 10:09 pm

imacronewbie22 wrote:Thank you. We did it :D

Code: Select all

SET !ERRORIGNORE YES
SET !EXTRACT_TEST_POPUP NO
SET !DATASOURCE example.csv
SET !VAR1 {{!COL1}}
TAG POS=2 TYPE=TD ATTR=* EXTRACT=TXT
SET !VAR2 {{!EXTRACT}}
SET !VAR1 EVAL("var n = '{{!COL1}}'.match(/\\b{{!EXTRACT}}\\b/ig); if (!n) '0'; else '1';")
TAG POS={{!VAR1}} TYPE=LABEL ATTR=TXT:Press
SAVEAS TYPE=EXTRACT FOLDER=C:\Documents\iMacros\DATASOURCES FILE=somefile.csv
Thank you for your continued support on Sunday. Have a nice week ahead
Yep, perfect...! Glad I could help... :D
- (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