Questions on data valadation and extraction to CSV

Discussions and Tech Support related to website data extraction, screen scraping and data mining using iMacros.
Forum rules
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
drdipa
Posts: 3
Joined: Thu Dec 20, 2018 9:04 pm

Questions on data valadation and extraction to CSV

Post by drdipa » Thu Dec 20, 2018 9:28 pm

This has been written again as I was corrected for my previous attempt
In the small amount code below I would like to do the following:

1. verify that the searched value appears on the page that was retrieved
If so then proceed with text extraction
If not then write a simple text string to a field in the excel file were data will be extracted to. EXAMPLE - search value (col1) / not found (col2)
2. In the code below POS 7 and POS 8 are associated. I would like to write them on the same row in excel. EXAMPLE - search value (col1 row 1) POS7 (col 3 row 1) POS* ( col4 row 1)
3. After all data has been written for that search value, write the next search value data on a new row in the excel file
4. Loop through this until all items have been searched.

I hope this is more clear.

Again thanks ina dvance

VERSION BUILD=12.5.503.8802
TAB T=1
TAB CLOSEALLOTHERS
'SET !EXTRACT_TEST_POPUP
SET !DATASOURCE C:\---\---\----\-----.csv
SET !LOOP 2
SET !DATASOURCE_LINE {{!LOOP}}
WAIT SECONDS=0.5
URL GOTO = https://www.---.com
WAIT SECONDS=0.5
TAG POS=1 TYPE=INPUT:TEXT ATTR=ID:search--site CONTENT={{!COL1}}
TAG POS=1 TYPE=BUTTON:SUBMIT ATTR=TXT:Search
TAG POS=1 TYPE=A ATTR=TXT:*
TAG POS=1 TYPE=H2 ATTR=CLASS:item-class EXTRACT=TXT
TAG POS=1 TYPE=H3 ATTR=CLASS:item-number EXTRACT=TXT
TAG POS=7 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=8 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=9 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=10 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=11 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=12 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=13 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=14 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=15 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=16 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=19 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=20 TYPE=TD ATTR=* EXTRACT=TXT
BACK
Last edited by drdipa on Fri Dec 21, 2018 12:16 am, edited 2 times in total.
chivracq
Posts: 8523
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: How to Record Macro and Loop for Data Extraction To CSV

Post by chivracq » Thu Dec 20, 2018 11:33 pm

drdipa wrote:
Thu Dec 20, 2018 9:28 pm

Code: Select all

imacros browser emulating IE11.
Good Day,
I have used Win Automation in the past and this seems more powerful and similar in the recording macro function.
What I am trying to do is this:

Capture values from an excel spreadsheet column and store them for imacros to use and input for search fields on multiple webpages.
Essentially I will change the CSV values for each website manually.
The program should capture all the values in the CSV colum if possible, and then loop through each one until reaching the last entry,

Code: Select all

 TAG POS=1 TYPE=INPUT:SEARCH ATTR=NAME:q CONTENT= // looped value
TAG POS=1 TYPE=BUTTON:SUBMIT ATTR=CLASS:"search-submit-btn submit-search"
Extract multiple text elements and table data that I will use record macro for.

Export each value and associated text to a row in excel and continue until all values are accounted for.

I can build the exceptions for no data found of web dialogues
I could use help in the imacros data storing and looping through the information, ( I will use the record function to select the data) then extracting to a new excel sheet.

Can someone help me get started with this? Im sure I can figure out the nuiances for each page but the engine of storing the csv values, looping through them and exporting that per row into an excel file is where I am not familiar.

I am using the imacros browser emulating IE11.
If I left out any infomration I apologize.

Thanks in advance for the help.
Oh...!, I thought you were sharing a 'How-to'... => Could add some Qt Mark to your Thread Title...! :roll:

FCIM...! :mrgreen: (Read my Sig...)
"imacros browser emulating IE11." is a bit vague, about 10 Versions of iMacros for iMB and IE have been "emulating IE11"...

Long Post, no clear Qt, mini Script posted has nothing related to the Thread Title... :?

I see some "Can someone help me get started with this?" :roll:
=> Tja, yep: Have a look at the Demos, and read a bit of the Documentation and search/read a few (Pages of) Threads on the Forum and "play" a bit with iMacros..., I would say, Advanced Users like me on the Forum are here to help you when you "really" get stuck somewhere after you've tried your "best" but we are not here to type and/or copy some "Getting Started" Page from the Wiki for you, ah-ah...! :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...
drdipa
Posts: 3
Joined: Thu Dec 20, 2018 9:04 pm

Re: How to Record Macro and Loop for Data Extraction To CSV???

Post by drdipa » Thu Dec 20, 2018 11:47 pm

Thanks kindly, Ill reformat this so it makes sense to someone other than myself.
Again Im new at posting questions to forums.
chivracq
Posts: 8523
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: How to Record Macro and Loop for Data Extraction To CSV???

Post by chivracq » Fri Dec 21, 2018 12:18 am

drdipa wrote:
Thu Dec 20, 2018 11:47 pm
Thanks kindly, Ill reformat this so it makes sense to someone other than myself.
Again Im new at posting questions to forums.
1 Qt Mark was enough, ah-ah...! :wink:

+1 (Pt) for the swift Follow-up... :D

If you could mention your exact FCI...?
=> iMB v11.x/v12.0/v12.5...?, 'Full'/'Trial'...?, OS...?

I'll probably be your "best Friend" on the Forum, but I only help Users using the Forum "a bit correctly" (= Correct Sub-Forum + Descriptive Thread Title + FCI mentioned + Neat Follow-up on (all) their (previous) Threads + Sharing their Solution/Final Script) and + "really get stuck somewhere after they've tried their "best""... (But I tend to "act" a bit "autistically" if you don't mention your FCI, especially if I asked..., just saying...)
- (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...
drdipa
Posts: 3
Joined: Thu Dec 20, 2018 9:04 pm

Re: Questions on data valadation and extraction to CSV

Post by drdipa » Fri Dec 21, 2018 7:54 pm

In response to FCI:
iMB version 12.5.503.8802 and Trial ( at least until I can get this going)
OS is Win 10pro Version 1803 / 64bit


Thanks for your help :)
BFF
chivracq
Posts: 8523
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Questions on data valadation and extraction to CSV

Post by chivracq » Fri Dec 21, 2018 10:21 pm

drdipa wrote:
Fri Dec 21, 2018 7:54 pm
In response to FCI:

Code: Select all

iMB version 12.5.503.8802 and Trial ( at least until I can get this going)
OS is Win 10pro Version 1803 /  64bit
Thanks for your help :)
BFF
OK, good..., and I'll re-quote your OP as you've pretty much completely rewritten it...:
drdipa wrote:
Thu Dec 20, 2018 9:28 pm
This has been written again as I was corrected for my previous attempt
In the small amount code below I would like to do the following:

1. verify that the searched value appears on the page that was retrieved
If so then proceed with text extraction
If not then write a simple text string to a field in the excel file were data will be extracted to. EXAMPLE - search value (col1) / not found (col2)
2. In the code below POS 7 and POS 8 are associated. I would like to write them on the same row in excel. EXAMPLE - search value (col1 row 1) POS7 (col 3 row 1) POS* ( col4 row 1)
3. After all data has been written for that search value, write the next search value data on a new row in the excel file
4. Loop through this until all items have been searched.

I hope this is more clear.

Again thanks ina dvance

Code: Select all

VERSION BUILD=12.5.503.8802
TAB T=1
TAB CLOSEALLOTHERS
'SET !EXTRACT_TEST_POPUP
SET !DATASOURCE C:\---\---\----\-----.csv
SET !LOOP 2
SET !DATASOURCE_LINE {{!LOOP}}
WAIT SECONDS=0.5
URL GOTO = https://www.---.com
WAIT SECONDS=0.5
TAG POS=1 TYPE=INPUT:TEXT ATTR=ID:search--site CONTENT={{!COL1}} 
TAG POS=1 TYPE=BUTTON:SUBMIT ATTR=TXT:Search
TAG POS=1 TYPE=A ATTR=TXT:*
TAG POS=1 TYPE=H2 ATTR=CLASS:item-class EXTRACT=TXT
TAG POS=1 TYPE=H3 ATTR=CLASS:item-number EXTRACT=TXT
TAG POS=7 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=8 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=9 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=10 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=11 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=12 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=13 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=14 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=15 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=16 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=19 TYPE=TD ATTR=* EXTRACT=TXT
TAG POS=20 TYPE=TD ATTR=* EXTRACT=TXT
BACK
Muuuuch better Quality indeed now, you "learn" quick, ah-ah...! :wink:

OK, URL not posted, I can't have a look at the Site myself, as I would understand much quicker and better how the Site/Page "behaves" than with your "Col3/Col4/Pos7/Pos8/etc...", ah-ah...! But OK, fair enough...

One thing is that you cannot rewrite or resave in the same '.CSV' that you use as DataSource for your Input Data, the Data that you later extract from the Site with your Script. Or you would need to use some Online GoogleSheet for example that you can access from your Script for the Input Data and edit on-the-fly for saving the Data you are extracting in the same corresponding Rows...
The way to achieve that Functionality is to read from your '.CSV' Row by Row and you'll be recreating a new '.CSV' with the original Data + the new Data from the Extraction that you want to add to it.
=> You can control and manipulate the (Content of the) '!EXTRACT' Built-in Var, and every 'SAVEAS TYPE=EXTRACT' will save one Row to your (new) '.CSV'.

The "Control and Manipulation" on the '!EXTRACT' Var is done with the 'EXTRACT' Mechanism (that you are already using), and eventually using some Temp Vars to store some specific Extracts if you want to reorder the final Order of all Data to be saved, using the 'SET !EXTRACT' and 'ADD !EXTRACT' Commands to "populate" that '!EXTRACT' Var in the Order that you want if the "automatic" Order from all 'EXTRACT''s Commands you have in your Script does not reflect exctly the final Order you would like per Col in the 'SAVEAS'.

And you can also accessorily use the 'EVAL()' Command if you further want to manipulate any specific 'EXTRACT' or Temp Var (if you don't want to keep the whole 'EXTRACT' for example, or trim extra Spaces) or on the full '!EXTRACT' Var itself, usually just before firing the 'SAVEAS'.
You can use 'PROMPT {{!EXTRACT}}' to follow the Content of '!EXTRACT' and understand how iMacros "formats" it with its "[EXTRACT]" Separator to separate each Value between Cols in your '.CSV'.

For Fields/Values that won't be found, the 'EXTRACT' Mechanism will then save some "#EANF#" Value telling you the Data was not found on the Page, that you can remove from 'Excel' if you don't like all those "#EANF#", ah-ah...! It is also possible to remove them with 'EVAL()' before doing the 'SAVEAS' or maybe even to only conditionally fire the 'SAVEAS' if there is indeed some Data to extract.

You may also have a look at 'Relative Positioning' that could be used instead of all your 'POS=7' + 'POS=8' + etc... :idea:

(But everything I just mentioned is also documented and explained in the Wiki and the Demos or can be understood if you "play" a bit with iMacros... :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...
Post Reply