Using title row within CSV loop

Support for iMacros. The iMacros software is the unique solution for automating every activity inside a web browser, for data extraction and web testing.
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
adam1337
Posts: 16
Joined: Sun Jun 09, 2019 4:12 pm

Using title row within CSV loop

Post by adam1337 » Mon Jun 17, 2019 4:58 pm

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
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Using title row within CSV loop

Post by chivracq » Tue Jun 18, 2019 1:27 am

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...! :shock:
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...! :wink:

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... :oops:
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...! :shock:
I remember this '@Vitae' User, was nice and clever... 8)


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... 8)


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...!! :twisted:
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...! :P
Getting a bit tired, I had a 5th Method in mind, but I can't recall it right now, oops...! :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 :twisted: , 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...! :P

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).
Last edited by chivracq on Thu Feb 11, 2021 2:29 pm, edited 3 times in total.
- (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...
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Using title row within CSV loop

Post by chivracq » Tue Jun 18, 2019 2:37 pm

Some mini further "Thinking" as I still have your Thread open in my Browser, and you don't seem to have checked the Forum yet since my Reply...

In "terms" of Ease of Implementation, I would go in the following Order for choosing between the 5 Methods I've mentioned...:
5 > 4 > 2 >> 1 > 3. (Big gap between [5>4>2] and [1>3].)

About Method Nb_5, saving/having the Input Data in 1 Row per Group, the easiest would be to have the Data in 4 Cols/Cells:

Code: Select all

Group_Nb / Acc_Nb (x3) / Acc_Name (x3) / File_ID (x3).
This would simplify the whole Implementation to a fairly simple 'Modulo[3]' 'Nested Loop' Scenario, (not difficult to implement, and many Threads already on the Forum, with Examples), and using the same 'Modulo[3]' Value with 'EVAL()' + 'split()[Mod[3]]' for the 3 '!COL[2-4]' to separate each 'Account_Nb' etc... within one same Cell.

+ Maybe adding a Prefix to all Values, which could be used for the 'split()' as a 'split()' on a (Soft) Return may be a bit tricky because of Escaping, and the Index of the Results of a 'split()' starts at "0" (=> [0-2]) which can sometimes be a little bit "confusing", while using a Prefix would shift that Index to [1-3] which will then also be already synchronized with the 'Mod[3]' Value for the 'Nested Loop'...
- (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...
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Using title row within CSV loop

Post by chivracq » Tue Jun 18, 2019 3:18 pm

OK, I "see" you are busy checking your Thread..., good-good... :D

And I would have a 6th Method: 8)

6- Method Nb_6: DataSource as a GoogleSheet:
Yep, why not...!, you could have your DataSource as a GoogleSheet, which can be maintained and accessed from a Web-Interface and therefore directly from iMacros also, which allows a bit a similar Implementation lie Method_2 (the '.html' DataSource) with 'Relative Positioning' on each 'Group_Nb' etc...

BUT...!, (big BUT), GoogleSheets tend to be a bit of a "pain in the ass" with iMacros, and seem to work "best" in Google Chrome as the Browser, I don't know about iMB, but it's not "easy" on FF. :shock:
=> Nearly impossible to use the 'TAG' Mode, have to use the 'EVENT' Mode (which doesn't support 'Relative Positioning', (even if I've "developed" a Workaround)), have to use Keyboard Navigation...

I wouldn't go for this Method myself, to be honest... But I only use iMacros for FF, maybe GoogleDocs/Sheets integrate better with iMacros in iMB..., I don't know... Extra "anti"-Argument is that Google regularly adds/changes some Functionality in GoogleD/S, which can easily break iMacros Scripts, especially if using the 'EVENT' Mode... But OK, that's still a Possibility...

To place it in the "Order" I previously emitted, that would give, I think...:
5 > 4 > 2 >> 1 > 6 > 3.
- (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...
adam1337
Posts: 16
Joined: Sun Jun 09, 2019 4:12 pm

Re: Using title row within CSV loop

Post by adam1337 » Tue Jun 18, 2019 4:03 pm

Thanks a lot for your reply. Just got back to my desk so I haven’t implemented/tested anything yet, I’ll provide an update when I do.
chivracq wrote:
Tue Jun 18, 2019 1:27 am
Compliment for the Quality of your Post(s), that's "unfortunately" rather rare on the Forum, ah-ah...!
Trying to keep things within the FCI parameters…you can be sure your work ensuring posts here follow SOPs has not gone unnoticed :D
chivracq wrote:
Tue Jun 18, 2019 1:27 am
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...!
Will do. I figured it was a sort of client integrity measure to make sure imacros itself is working properly.
chivracq wrote:
Tue Jun 18, 2019 1:27 am
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/Implementation 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... 8)
Yes, this makes perfect sense. I get the idea of 'SET !DATASOURCE_LINE 1' better now, for some reason I was treating it like !LOOP X in my mind.
chivracq wrote:
Tue Jun 18, 2019 1:27 am

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...!), much 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 smaller Set of Data...
I haven’t used EXTRACT myself, but this is correct…There will ultimately be 200+ accounts so avoiding anything that will be more complicated/less consistent for iMacros to interpret should be best.
chivracq wrote:
Tue Jun 18, 2019 1:27 am
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...!! :twisted:
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.
So I wasn’t in alone in coming to this kind of solution…good :D I assumed it was sort of taking the ‘easy way out’ from what I read on other posts but with this many accounts (and now extra the additional GROUP variable) it’s starting to seem like the optimal approach.
chivracq wrote:
Tue Jun 18, 2019 1:27 am
5- Method Nb_5: Hum-hum, I forgot about it, ah-ah...! :P
Getting a bit tired, I had a 5th Method in mind, but I can't recall it right now, oops...! :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 :twisted: , 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...! :P
There has gotta be a macro for this…

Code: Select all

Recall = iimGetLastExtract ( “C:/Brain/Thoughts/Sub-directions/fifth-dimension”  )
Never tested but it should do the trick :D
chivracq wrote:
Tue Jun 18, 2019 1:27 am
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' of maybe several Cells, but all Data in just one same Row (per Group).
// Nb_5 continued on following reply
Some mini further "Thinking" as I still have your Thread open in my Browser, and you don't seem to have checked the Forum yet since my Reply...

In "terms" of Ease of Implementation, I would go in the following Order for choosing between the 5 Methods I've mentioned...:
5 > 4 > 2 >> 1 > 3. (Big gap between [5>4>2] and [1>3].)

About Method Nb_5, saving/having the Input Data in 1 Row per Group, the easiest would be to have the Data in 4 Cols/Cells:
Code: Select all
Group_Nb / Acc_Nb (x3) / Acc_Name (x3) / File_ID (x3).
This would simplify the whole Implementation to a fairly simple 'Modulo[3]' 'Nested Loop' Scenario, (not difficult to implement, and many Threads already on the Forum, with Examples), and using the same 'Modulo[3]' Value with 'EVAL()' + 'split()[Mod[3]]' for the 3 '!COL[2-4]' to separate each 'Account_Nb' etc... within one same Cell.

+ Maybe adding a Prefix to all Values, which could be used for the 'split()' as a 'split()' on a (Soft) Return may be a bit tricky because of Escaping, and the Index of the Results of a 'split()' starts at "0" (=> [0-2]) which can sometimes be a little bit "confusing", while using a Prefix would shift that Index to [1-3] which will then also be already synchronized with the 'Mod[3]' Value for the 'Nested Loop'...
This is the way to go from what I’ve gathered…I’m already enjoying the simplicity of the master GROUP spreadsheet though I haven’t begun yet.

This will be much easier to interact with once I get the syntax down…I’ll start browsing around to see what I can find.
chivracq wrote:
Tue Jun 18, 2019 1:27 am
And I would have a 6th Method:

6- Method Nb_6: DataSource as a GoogleSheet:
Yep, why not...!, you could have your DataSource as a GoogleSheet, which can be maintained and accessed from a Web-Interface and therefore directly from iMacros also, which allows a bit a similar Implementation lie Method_2 (the '.html' DataSource) with 'Relative Positioning' on each 'Group_Nb' etc...

BUT...!, (big BUT), GoogleSheets tend to be a bit of a "pain in the ass" with iMacros, and seem to work "best" in Google Chrome as the Browser, I don't know about iMB, but it's not "easy" on FF.
=> Nearly impossible to use the 'TAG' Mode, have to use the 'EVENT' Mode (which doesn't support 'Relative Positioning', (even if I've "developed" a Workaround)), have to use Keyboard Navigation... But OK, that's an Idea...
Nice solution, and a buzzer beater as I was about to ‘submit’ :D

Though I’ll probably stick with iMB for now (I started with it and have had issues replicating the behavior on CR/FF, never imagined I would be opting for IE!) it’s certainly good to know google sheets is even an option….

Thanks again. I’ll be sure to keep you in the loop…

Code: Select all

 SET !LOOP chivracq
Last edited by adam1337 on Tue Jun 18, 2019 4:32 pm, edited 2 times in total.
chivracq
Posts: 10301
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: Using title row within CSV loop

Post by chivracq » Tue Jun 18, 2019 4:17 pm

Yep, seen your Post... I don't quote it, as the Thread is already pretty "heavy" on Quotes... :wink:
The first very long Quote in your Post was not very "necessary" where you even quote yourself again, the Thread becomes a bit difficult and lengthy to read otherwise...

Oh...!, and I noticed a mini-Typo in one of my previous Posts, going to correct it... (Some "of" that should read "or"... ("of" happens to mean "or" in Dutch, oops...!)) :oops:
Last edited by chivracq on Wed Jun 19, 2019 2:41 am, edited 1 time in total.
- (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...
adam1337
Posts: 16
Joined: Sun Jun 09, 2019 4:12 pm

Re: Using title row within CSV loop

Post by adam1337 » Tue Jun 18, 2019 4:33 pm

Ok noted, good call. I tried to clean it up a bit.
Post Reply