adam1337 wrote: ↑Mon Jun 17, 2019 4:58 pm
Code: Select all
iMacros version: iMacros Browser (x86) version 12.5.503.8802
Operating system: Windows 10 64-bit English
Browser: Internet Explorer version 11.504.17763.0 (iMacros Browser/client, not using browser extension).
Demo success: Yes
I have a csv file which contains multiple columns / rows like this:
Code: Select all
44 GROUP 63 GROUP
442345678 Account name 1 67-8 631234567 Account name 2 56-7
443123123 Account name 6 12-3 639999999 Account name 3 99-9
441231231 Account name 5 23-1 637234892 Account name 5 89-2
Each 'GROUP' contains 3 columns, and a header/title in the first row and first column of each group. This csv file will be dynamic as later on I will add more accounts to existing groups (add rows), or create entirely new groups (3 new columns with x rows).
On the webpage where the macro runs, there are 2 dropdowns.
Step #1 is to select from the first dropdown (dropdown A) using the name of the group (i.e. 44 GROUP, from head/row 1). When that is selected, the second dropdown (dropdown B) automatically populates with the relevant account numbers.
Step #2 is to use the rows from the current GROUP in the csv, to move through each row and take action on dropdown B {{!COL1}}. Once a blank line is hit, it should repeat step #1 for the next group {{!COL4}}. This way it is selecting the value on dropdown A from the csv column head (row 1) to populate dropdown B, and then looping through the current column to make selections from dropdown B, until there are none, before moving to the next GROUP.
Initially I was able to get this to run perfectly, with huge help from chivracq (
viewtopic.php?f=2&t=30341), when there was only one row to pull from, and no header-values that needed to be accounted for.
I found a post here which provides some useful info on how to refer to multiple columns:
viewtopic.php?t=22976
But in my case, prior to referencing each csv column, I need it to use the first row of the csv column (Row 1 of {{!COL1}},{{!COL4}},{{!COL7}} containing text “44 GROUP”, “63 GROUP” etc.) to select the value from the first drop down, only once, before looping through that particular column.
The only solution I can think of, which I’ve so far been avoiding since there always seems to be better solution, is to create individual csv files for each group and add new files when new groups are added.
Here's the code I have now, I've been tweaking it only lightly to avoid butchering the super clean code which was shared with me before. I think it can be simple by adding a couple of lines where I made the comment but I can't get it to work from the current state as a template. If there was a {{!ROW1}} variable it might produce the effect I'm looking for, but otherwise I'm unsure how to reference these rows when new ones will be added later.
Any help will be greatly appreciated, so thanks in advance
Code: Select all
VERSION BUILD=12.5.503.8802
SET !ERRORIGNORE YES
SET !TIMEOUT_PAGE 30
SET !TIMEOUT_STEP 0
TAB T=1
'TAB CLOSEALLOTHERS
'Easy Access Vars:
SET URL_Site [login url]
'Login only on Loop=1:
'=> Conditional Vars for the Login Part:
'Added "EVAL" after WAIT_Login, and removed ' ' between EVAL and (
SET URL_Login EVAL("var d='{{!LOOP}}', us='{{URL_Site}}'; var z; if(d==1){z=us;} else{z='';}; z;")
SET Loop_1 EVAL("var d='{{!LOOP}}'; var z; if(d==1){z=1;} else{z=0;}; z;")
SET WAIT_Login EVAL("var d='{{!LOOP}}'; var z; if(d==1){z=10;} else{z=0;}; z;")
' Go to login portal
'URL GOTO=[login url]
URL GOTO={{URL_Login}}
' Sign-in:
'TAG POS=1 TYPE=INPUT:TEXT ATTR=NAME:userId CONTENT=[userid]
TAG POS={{Loop_1}} TYPE=INPUT:TEXT ATTR=NAME:userId CONTENT=[userid]
'>
SET !ENCRYPTION STOREDKEY
'TAG POS=1 TYPE=INPUT:PASSWORD ATTR=NAME:password CONTENT=[pw]
TAG POS={{Loop_1}} TYPE=INPUT:PASSWORD ATTR=NAME:password CONTENT=[pw]
'>
'TAG POS=1 TYPE=INPUT:SUBMIT ATTR=NAME:signin
TAG POS={{Loop_1}} TYPE=INPUT:SUBMIT ATTR=NAME:signin
'>
WAIT SECONDS={{WAIT_Login}}
'Looping Section:
'set csv file as datasource
SET !DATASOURCE accountlist.csv
SET !DATASOURCE_LINE {{!LOOP}}
'Set download path
ONDOWNLOAD FOLDER=E:\Folder\ FILE={{!COL1}}<SP>document<SP>{{!NOW:yymmdd}} WAIT=YES
SET !TIMEOUT_DOWNLOAD 20
WAIT SECONDS=3
'SELECT FROM DROPDOWN A BASED ON THE HEADER ROW TITLE - Perhaps this needs something to the effect of {{!ROW1}}
TAG POS=1 TYPE=SELECT FORM=NAME:NoFormName ATTR=CLASS:GB01FU-BOW CONTENT="GROUP 44"
WAIT SECONDS=3
'CONTINUE LOOPING THROUGH DROPDOWN B USING CURRENT COLUMN VALUES
TAG POS=1 TYPE=SELECT FORM=NAME:NoFormName ATTR=CLASS:GB01FU-BNW CONTENT=%*{{!COL1}}*
WAIT SECONDS=3
'Choose 'go' to submit selection from dropdown
TAG POS=1 TYPE=BUTTON ATTR=TXT:GO
'Click download link
TAG POS=1 TYPE=A ATTR=TXT:Download
WAIT SECONDS=10
Note 1: the 2 right columns in each group are to be used for naming the saved files, so they aren’t necessarily relevant for this but I’ve left them for completeness.
Note 2: I couldn't decide if the csv formatting is best as is (with the titles in the head row) but I wanted it to be 'modular' so that any new groups and their associated data which are added later will be tied together when they are referenced in the loop, and I figured this was the most consistent way.
Edit: Fixed table formatting
Compliment for the Quality of your Post(s), that's "unfortunately" rather rare on the Forum, ah-ah...!
Oh...!, you can drop the "Demo success: Yes" by your FCI btw, that's only relevant if you are reporting a Bug, or have a Pb using a DataSource for example..., but not really if you are asking a Qt on how to implement a specific Scenario... And I don't believe you recheck all Demos each time before opening a Thread, ah-ah...!
From the first look at your Post, and especially your '.CSV' DataSource, I first thought you were the User from
this recent Thread, and that Thread could be a little bit relevant, from about
this Post and the "Discussion" (well => my Qt's...!) about saving the Data in [1-2-5-10] Cells, and how I implemented a bit further in the Thread the Content of the 'SAVEAS'...
OK..., will be a long Reply, I guess...
I "think", I could think of 5 different (and sometimes possible to combine) ways to implement the Functionality that you want, if I understood your Scenario correctly...
Oh..., and nice Link to this older Thread from 2014, pfff, 5 years ago already, oops...!
I remember this '@Vitae' User, was nice and clever...
1- Method Nb_1: Loop through '.CSV' and compute '!DATASOURCE_LINE':
That would be the "classic" Approach, I guess, you'll be looping through your '.CSV', but you'll have to do a lot of Operations on '!DATASOURCE_LINE' using 'EVAL()' and 'Modulo[]' for 2 or maybe 3 Levels of Nested Loops as the Structure of your '.CSV' is not a "classic" "go down the '.CSV' Row by Row and grab the needed Cols" using the raw '!LOOP' in 'SET !DATASOURCE_LINE {{!LOOP}}'.
That's doable, but you'll have to do some "heavy" Thinking, ah-ah...! A bit complicated that the Col_Nb in '{{!COLn}}' that cannot be computed "directly" from iMacros in '.iim', only from '.js' or '.vbs'. But there are several Workarounds/Implementations in pure '.iim', one is using/misusing '!DATASOURCE_DELIMITER'... which is not supported on iMB, ah-ah...! Then you'd need to each time retrieve both Cells for example between '!COL1' and '!COL4' and using 'EVAL()' to spit out the "correct" one to use for that Loop.
Or to "hard-coded" 2 Blocks for '!COL[1-3]' and '!COLL[4-6]' and using 'EVAL()' to compute which one will be active and which one will be muted, like we did in your Thread for the 'Login' part of your Macro...
Oh...!, and about you missing a '{{!ROW1}}', this is simply achieved with a 'SET !DATASOURCE_LINE 1'.
2- Method Nb_2: DataSource as an '.html' File:
Instead of using a '.CSV' File for your DataSource, it's possible to convert it to an '.html' File from 'Excel'/'Open|LibreOffice', then you don't use the '!DATASOURCE' + '!DATASOURCE_LINE' + '{{!COLn}}' Mechanism, but you open that '.html' File from the Browser in a 2nd Tab and you can extract its whole Data at the Table Level, or Row by Row, or Cell by Cell.
=> To locate your '44 GROUP' for example, you would use:
Code: Select all
TAG POS=1 TYPE=TD ATTR=TXT:*<SP>GROUP
And you would loop on the 'POS' to loop through all different 'GROUP''s.
Once a 'GROUP' is located/tagged, you would then use 'Relative Positioning' to access all corresponding Cells that belong to a 'GROUP'.
For example...:
Code: Select all
TAG POS={{!LOOP}} TYPE=TD ATTR=TXT:*<SP>GROUP EXTRACT=TXT
SET Group_Nb EVAL("var s='{{!EXTRACT}}'; var x,y,z; x=s.split(' '); z=x[0]; z;")
'>
SET !EXTRACT NULL
TAG POS=R1 TYPE=TD ATTR=TXT:{{Group_Nb}}* EXTRACT=TXT
SET Account_Nb_1 {{!EXTRACT}}
'>
SET !EXTRACT NULL
TAG POS=R1 TYPE=TD ATTR=TXT:{{Group_Nb}}* EXTRACT=TXT
SET Account_Nb_2 {{!EXTRACT}}
'>
SET !EXTRACT NULL
TAG POS=R1 TYPE=TD ATTR=TXT:{{Group_Nb}}* EXTRACT=TXT
SET Account_Nb_3 {{!EXTRACT}}
PROMPT Group_Nb:<SP>_{{Group_Nb}}_<BR>Account_Nb_1:<SP>_{{Account_Nb_1}}_<BR>...etc...
... should display the following in the 'PROMPT', I expect...:
Group_Nb: _44_
Account_Nb_1: _442345678_
Account_Nb_2: _443123123_
Account_Nb_3: _441231231_
You get the "Principle", I think...
3- Method Nb_3: DataSource as a '.TXT' File:
Similar to Method_2, but you change the '.CSV' Extension from your '.CSV' File into '.TXT' (or '.ABC' / '.XYZ' / '.ADAM' or whatever Extension that is not associated to Excel nor any Program on your Computer that will want to "handle" that File) and you open it also from the Browser in a 2nd Tab which sees the whole Content as just one big Cell of Data from a Text File.
BUT...!, the whole Content gets extracted as just one big 'EXTRACT'. It's not very easy to re-separate all Cells, or you first need to format the original '.CSV' with extra Columns separating all Cols and that will contain some "##" and "###" to separate the Cells and the Rows as "Delimiters", to then locate each specific Cell that you want with 'EVAL()' + 'split('##')' and 'split('###')'.
Quite Fun and Powerful (I use it myself, ah-ah...!), but I'm not sure it will be very handy in your Case with your "large" '.CSV'. I think it's more useful for a small 'SAVEAS' and a smaller (and fixed) Set of Data...
4- Method Nb_4: Multiple '.CSV''s for each 'SAVEAS' + '!DATASOURCE', one for each 'GROUP':
That's the Method you've mentioned also... I also "discarded" it a bit quickly first, as I foresaw some 'Hassle" linking the Name of the 'SAVEAS' and the DataSource' to reuse, and knowing which ones have been processed, even if i can think of several ways, but hum-hum..., maybe it's not that bad actually...!!
You would simply need for each 'SAVEAS' and the Data belonging to one 'GROUP' to also save the Name of the '.CSV' into a separate 'SAVEAS' File (with TimeStamp and any other Info you may want to keep), that could also be used as a '.LOG' File, and when you need to process all the Groups, you first use that "main" '.CSV' to retrieve the Name of the DataSource to use. The "main" '.CSV' can simply be looped Row by Row and you could re-save the Data from the main '.CSV' to a new File, that could also act as a '.LOG' File, with TimeStamp and "Processed" Status.
5- Method Nb_5: Hum-hum, I forgot about it, ah-ah...!
Getting a bit tired, I had a 5th Method in mind, but I can't recall it right now, oops...!
I have some kind of "associative" Mind, by drawing some kind of [3-5]D Model mentally as I read a Thread/Case/Scenario where possible Implementations fit "naturally" by "connecting" all Elements of the Model, and I "navigate" forth and back in all (Sub-)Directions/Dimensions if I encounter/foresee a "Difficulty" "somewhere" or find a Shortcut, but that whole "Process" takes place in just a few Seconds
, and the "Difficulty" afterwards is to remember all different Paths leading to the/a Solution, ah-ah...!, especially if I have to type a Post in Words to explain them all, oops...!
Oh ja!, got it back, yep, related to the Thread I linked to at the beginning, by saving all Data related to one 'GROUP' into only one Cell in your '.CSV' or maybe several Cells, but all Data in just one same Row (per Group).
- (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...