Checking for a word in .csv file

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.

Checking for a word in .csv file

by imacronewbie22 on Fri Jun 30, 2017 5:53 am

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

Re: Checking for a word in .csv file

by chivracq on Fri Jun 30, 2017 8:49 am

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

Re: Checking for a word in .csv file

by imacronewbie22 on Fri Jun 30, 2017 10:27 am

Thank you for your help. I like your humor :D Cheers again
imacronewbie22
 
Posts: 20
Joined: Wed Dec 14, 2016 3:57 pm

Re: Checking for a word in .csv file

by chivracq on Fri Jun 30, 2017 11:59 am

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

Re: Checking for a word in .csv file

by imacronewbie22 on Sat Jul 01, 2017 2: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
imacronewbie22
 
Posts: 20
Joined: Wed Dec 14, 2016 3:57 pm

Re: Checking for a word in .csv file

by chivracq on Sat Jul 01, 2017 10:44 am

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

Re: Checking for a word in .csv file

by imacronewbie22 on Sat Jul 01, 2017 4: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
imacronewbie22
 
Posts: 20
Joined: Wed Dec 14, 2016 3:57 pm

Re: Checking for a word in .csv file

by chivracq on Sat Jul 01, 2017 11:48 pm

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

Re: Checking for a word in .csv file

by imacronewbie22 on Sun Jul 02, 2017 12: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
imacronewbie22
 
Posts: 20
Joined: Wed Dec 14, 2016 3:57 pm

Re: Checking for a word in .csv file

by chivracq on Sun Jul 02, 2017 12: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)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: 6484
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Checking for a word in .csv file

by imacronewbie22 on Sun Jul 02, 2017 1: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
imacronewbie22
 
Posts: 20
Joined: Wed Dec 14, 2016 3:57 pm

Re: Checking for a word in .csv file

by chivracq on Sun Jul 02, 2017 2: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)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: 6484
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Checking for a word in .csv file

by imacronewbie22 on Sun Jul 02, 2017 2: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
imacronewbie22
 
Posts: 20
Joined: Wed Dec 14, 2016 3:57 pm

Re: Checking for a word in .csv file

by chivracq on Sun Jul 02, 2017 3: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)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: 6484
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)


Return to Data Extraction and Web Screen Scraping

Who is online

Users browsing this forum: No registered users and 3 guests

-->