loop reading csv till the end of file

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
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
lukasz
Posts: 31
Joined: Fri Jan 24, 2020 10:26 am

loop reading csv till the end of file

Post by lukasz » Wed May 06, 2020 2:15 pm

WINDOWS 10 PRO
IMACROS 12.6.505.4525 EE (trial)
USING IMACROS BROWSER WITH IE 11 EMULATION


Hello,

I can't find a solution to my problem. because i use imacros browser the solutions i found are not possible.



I have to complete two fields until there are no more lines in the csv file.
the number of lines in the file may be different each time

the easiest would be to set

Code: Select all

SET !ERRORIGNORE YES
repeat the field completion code more times than the highest possible number of lines in the csv file:

Code: Select all

ADD MyLoop 1
SET !DATASOURCE_LINE {{MyLoop}}
EVENTS TYPE=KEYPRESS SELECTOR="#fast_index" CHARS={{!COL1}}
EVENTS TYPE=KEYPRESS SELECTOR="#fast_quantity" CHARS={{!COL2}}
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=ID:fast_product_submit
WAIT SECONDS =2

but in this case every repetition of this code above the number of lines in csv pastes the values from the last line of the csv file.
so for example if I have 4 lines in csv:

76382746, 10
78943857, 10
74329402, 10
78370203, 10


and this code above will be repeated 6 times.
4 5 and 6 repetition will paste 78370203, 10 :(

and I want it to be done only in 4 repetition, 5 and 6 should be skipped because there is nothing in 5 and 6 line of csv
Last edited by lukasz on Wed May 06, 2020 4:34 pm, edited 1 time in total.
chivracq
Posts: 9374
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: loop reading csv till the end of file

Post by chivracq » Wed May 06, 2020 3:52 pm

lukasz wrote:
Wed May 06, 2020 2:15 pm
WINDOWS 10 PRO
IMACROS LATEST
USING IMACROS BROWSER WITH IE 11 EMULATION


Hello,

I can't find a solution to my problem. because i use imacros browser the solutions i found are not possible.



I have to complete two fields until there are no more lines in the csv file.
the number of lines in the file may be different each time

the easiest would be to set

Code: Select all

SET !ERRORIGNORE YES
repeat the field completion code more times than the highest possible number of lines in the csv file:

Code: Select all

ADD MyLoop 1
SET !DATASOURCE_LINE {{MyLoop}}
EVENTS TYPE=KEYPRESS SELECTOR="#fast_index" CHARS={{!COL1}}
EVENTS TYPE=KEYPRESS SELECTOR="#fast_quantity" CHARS={{!COL2}}
TAG POS=1 TYPE=INPUT:SUBMIT ATTR=ID:fast_product_submit
WAIT SECONDS =2

but in this case every repetition of this code above the number of lines in csv pastes the values from the last line of the csv file.
so for example if I have 4 lines in csv:

76382746, 10
78943857, 10
74329402, 10
78370203, 10


and this code above will be repeated 6 times.
4 5 and 6 repetition will paste 78370203, 10 :(

and I want it to be done only in 4 repetition, 5 and 6 should be skipped because there is nothing in 5 and 6 line of csv

FCIM for me to read, read my Sig, I don't know what "LATEST" means...? :?
And about iMB, mention also 'Pro'/'Enterprise'/'Trial'...?
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
lukasz
Posts: 31
Joined: Fri Jan 24, 2020 10:26 am

Re: loop reading csv till the end of file

Post by lukasz » Wed May 06, 2020 4:35 pm

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

Re: loop reading csv till the end of file

Post by chivracq » Wed May 06, 2020 5:07 pm

lukasz wrote:
Wed May 06, 2020 4:35 pm
edited

OK, perfect, Thanks...
Sorry, but "current"/"last"/"latest" is always vague, there are sometimes up to 4 "latest" Versions available for iMacros and the Browsers, especially FF and CR, which "in the past" has sometimes given a Matrix[16] of possible Combinations/FCI's to "guess" from..., and it won't mean anything anymore to sbd reading the Thread in a few weeks/months/years...

>>>

Alright, then hum..., "the easiest would be to set "SET !ERRORIGNORE YES"...", beeeh..., nope, on the contrary...! If you disable '!ERRORIGNORE', your Script will then automatically abort when the Row/Cell is not found. That would be the easiest Solution, I would think...

There are otherwise different possible Implementations, another "easy" one, and more "controlled" than '!ERRORIGNORE=NO', would be, at the end of your Script, to "already" check for the Next Row using 'EVAL()' on the Length of '!COL1' for example and to trigger some 'MacroError()'. :idea:
You then have Control on the Error Msg, and could include a mini-Report, displaying the Nb of Loops/Rows handled, the Last Row, Execution Time, etc, what you want... (Formatting is like for 'PROMPT', with '<BR>' and '<SP>'...)

You could also compare the Value of '!COL1' between 2 Loops, and abort the Macro once the Value becomes the same between 2 Loops/Rows.
But this is not very straightforward because iMacros cannot "communicate" with itself between 2 Loops as all Vars get reset (by Design) for each Loop, so you need to use some mini-Temp '.txt' File (as a 2nd DataSource), or the OS Clipboard, which is always "a bit dangerous" if your Script is running in the Background and you are doing "other Things" in the meantime where you might want to use the OS Clipboard as well...

Another Technique is to have your DataSource as a '.txt' File (and not '.csv'), and to open it from the Browser in a 2nd Tab from where you can extract its whole Content in just 1 'EXTRACT' Statement to then dynamically calculate/compute the Nb of Rows of Data to know when to stop the Looping, but you might need to add some "special" Formatting to your DataSource between the Cols.
You'll find a few Threads about that Technique on the Forum, like this one for example...:
- Re: Get number of lines from CSV and use as variable?

But the 2 easiest Solutions/Implementations are Disabling '!ERRORIGNORE' or Checking the Next Row, I would think... :|
Last edited by chivracq on Wed May 06, 2020 5:12 pm, edited 1 time in total.
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
lukasz
Posts: 31
Joined: Fri Jan 24, 2020 10:26 am

Re: loop reading csv till the end of file

Post by lukasz » Wed May 06, 2020 5:12 pm

chivracq wrote:
Wed May 06, 2020 5:07 pm



Alright, then hum..., "the easiest would be to set "SET !ERRORIGNORE YES"...", beeeh..., nope, on the contrary...! If you disable '!ERRORIGNORE', your Script will then automatically abort when the Row/Cell is not found. That would be the easiest Solution, I would think...

There are otherwise different possible Implementations, another "easy" one, and more "controlled" than '!ERRORIGNORE=NO', would be, at the end of your Script, to "already" check for the Next Row using 'EVAL()' on the Length of '!COL1' for example and to trigger some 'MacroError()'. :idea:
You then have Control on the Error Msg, and could include a mini-Report, displaying the Nb of Loops/Rows handled, the Last Row, Execution Time, etc, what you want... (Formatting is like for 'PROMPT', with '<BR>' and '<SP>...)

You could also compare the Value of '!COL1' between 2 Loops, and abort the Macro once the Value becomes the same between 2 Loops/Rows.
But this is not very straightforward because iMacros cannot "communicate" with itself between 2 Loops as all Vars get reset (by Design) for each Loop, so you need to use some mini-Temp '.txt' File (as a 2nd DataSource), or the OS Clipboard, which is always "a bit dangerous" if your Script is running in the Background and you are doing "other Things" in the meantime where you might want to use the OS Clipboard as well...

Another Technique is to have your DataSource as a '.txt' File (and not '.csv'), and to open it from the Browser in a 2nd Tab from where you can extract its whole Content in just 1 'EXTRACT' Statement to then dynamically calculate/compute the Nb of Rows of Data to know when to stop the Looping, but you might need to add some "special" Formatting to your DataSource between the Cols.
You'll find a few Threads about that Technique on the Forum, like this one for example...:
- Re: Get number of lines from CSV and use as variable?

But the 2 easiest Solutions/Implementations are Disabling '!ERRORIGNORE' or Checking the Next Row, I would think... :|


I didn't mention it, but it's not all the code. after reading all lines from the csv file, the macro still has a few operations to perform
chivracq
Posts: 9374
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: loop reading csv till the end of file

Post by chivracq » Wed May 06, 2020 5:28 pm

lukasz wrote:
Wed May 06, 2020 5:12 pm
I didn't mention it, but it's not all the code. after reading all lines from the csv file, the macro still has a few operations to perform

Ah...!, hum...!, that's why I always need the "Full Picture" of a Scenario to find "correct" Solutions, ah-ah...! :idea:

Yeah well, then instead of triggering a 'MacroError()' from 'EVAL()', spit out some "Fake" 'Selector' to reuse for the 2 'EVENT' Statements that won't do anything, + a "0" to reuse as "POS=0" for the 'TAG' Statement on the 'Submit' Button...., or conditionally switch to some 'TAB_2' (already Open on some "Fake" Page) where the Script with the extra nonexistent Rows won't do anything... :idea:
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
lukasz
Posts: 31
Joined: Fri Jan 24, 2020 10:26 am

Re: loop reading csv till the end of file

Post by lukasz » Thu May 07, 2020 12:03 pm

I think I don't quite understand.
I'll show you another example:



Code: Select all

ADD MyLoop 1
SET !DATASOURCE_LINE {{MyLoop}}
TAG POS=1 TYPE=TD ATTR=TXT:{{!COL1}}
TAG POS=R1 TYPE=INPUT:TEXT ATTR=NAME:bs CONTENT={{!COL2}}
this code should be repeated as many times as there are lines
and after it know that there is no more lines go to next steps.
doesn't matter what there is just need to run next part after loops are done
lukasz
Posts: 31
Joined: Fri Jan 24, 2020 10:26 am

Re: loop reading csv till the end of file

Post by lukasz » Thu May 07, 2020 12:40 pm

I found some solution myself since im tuning macros from batch file but don't know what next

Code: Select all

Setlocal EnableDelayedExpansion
  for /f "usebackq" %%b in (`type file.csv ^| find "" /v /c`) do (
    "C:\Program Files (x86)\Ipswitch\iMacros\iMacros.exe" -macro BRAAS -var_lines %%b
    )
  )
so macro will be opened with variable {{lines}} = row number,
how can I use this to run this code "{{lines}}" times ?

Code: Select all

ADD MyLoop 1
SET !DATASOURCE_LINE {{MyLoop}}
TAG POS=1 TYPE=TD ATTR=TXT:{{!COL1}}
TAG POS=R1 TYPE=INPUT:TEXT ATTR=NAME:bs CONTENT={{!COL2}}
chivracq
Posts: 9374
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: loop reading csv till the end of file

Post by chivracq » Thu May 07, 2020 12:44 pm

lukasz wrote:
Thu May 07, 2020 12:03 pm
I think I don't quite understand.
I'll show you another example:

Code: Select all

ADD MyLoop 1
SET !DATASOURCE_LINE {{MyLoop}}
TAG POS=1 TYPE=TD ATTR=TXT:{{!COL1}}
TAG POS=R1 TYPE=INPUT:TEXT ATTR=NAME:bs CONTENT={{!COL2}}
this code should be repeated as many times as there are lines
and after it know that there is no more lines go to next steps.
doesn't matter what there is just need to run next part after loops are done

Yeah well, in pure '.iim', you have 2 Ways to loop "only part of the Script":
- Either you repeat (and hard-code) that Block of Code say, 10 times, if you expect Max 10 Rows in your DataSource, and between each Block you will need to implement the Mechanism to check if the Next Block will have to run and do stg or not.
- Or your Script contains only 1 Block of Code, and you will be looping it, and the Part(s) in the Script that don't need to be executed on or until a specific Loop need(s) to be "muted"... And to "mute" or disable some Part of the Code, that's usually done using 'EVAL()', and if it's just a few Lines, then you compute a "0" to use for 'TAG POS=0" for 'TAG' Statements, or some "fake" String to use as 'Selector' if you are using 'EVENT' Statements, or if it's a large(r) Part of the Script, one Technique that I use is to conditionally switch between 'TAB_1' (where the "real" Processing is happening) and 'TAB_2' where some "fake" Site/URL/Local File is opened and where the Script does "nothing" on that Page... (with '!ERRORIGNORE=YES' of course, and '!TIMEOUT_STEP=0' (that can also be conditionally toggled if needed).

Now if you are using iMacros for FF until v9.0.3 (recommended is v8.9.7), you can use a '.js' Script from which you can completely control the Workflow of your Script, and loop and nested-loop up too as many nested Levels as you want any Part of your Script.
The same Functionality is also available if you use the 'Scripting Interface' (only available in iMB 'Enterprise Edition') where you can use any Prog Language, => 'VBS' that you seem to like, for example... :wink:

And New in iMB v12.6, you now also have a '-playlist' Command Line Parameter (supported in iMB 'Pro' + 'Enterprise') that could maybe "do the trick", but hum..., I don't know how it will 'behave" when using a DataSource... :idea:
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
chivracq
Posts: 9374
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: loop reading csv till the end of file

Post by chivracq » Thu May 07, 2020 12:56 pm

lukasz wrote:
Thu May 07, 2020 12:40 pm
I found some solution myself since im tuning macros from batch file but don't know what next

Code: Select all

Setlocal EnableDelayedExpansion
  for /f "usebackq" %%b in (`type file.csv ^| find "" /v /c`) do (
    "C:\Program Files (x86)\Ipswitch\iMacros\iMacros.exe" -macro BRAAS -var_lines %%b
    )
  )
so macro will be opened with variable {{lines}} = row number,
how can I use this to run this code "{{lines}}" times ?

Code: Select all

ADD MyLoop 1
SET !DATASOURCE_LINE {{MyLoop}}
TAG POS=1 TYPE=TD ATTR=TXT:{{!COL1}}
TAG POS=R1 TYPE=INPUT:TEXT ATTR=NAME:bs CONTENT={{!COL2}}

Hum..., and yep indeed, I didn't want to mention it, as I find it a bit "cumbersome", but you can indeed rewrite yourself the 'Scripting Interface' "from scratch" and implement its Functionality in a '.bat'/'.cmd' File, or 'PowerShell' that you seem to like also... 8)
Here is some other "powerful" though "cumbersome" Example also...:
- Re: Verify successful download ...
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
lukasz
Posts: 31
Joined: Fri Jan 24, 2020 10:26 am

Re: loop reading csv till the end of file

Post by lukasz » Thu May 07, 2020 1:24 pm

chivracq wrote:
Thu May 07, 2020 12:44 pm

- Or your Script contains only 1 Block of Code, and you will be looping it, and the Part(s) in the Script that don't need to be executed on or until a specific Loop need(s) to be "muted"... And to "mute" or disable some Part of the Code, that's usually done using 'EVAL()', and if it's just a few Lines, then you compute a "0" to use for 'TAG POS=0" for 'TAG' Statements, or some "fake" String to use as 'Selector' if you are using 'EVENT' Statements, or if it's a large(r) Part of the Script, one Technique that I use is to conditionally switch between 'TAB_1' (where the "real" Processing is happening) and 'TAB_2' where some "fake" Site/URL/Local File is opened and where the Script does "nothing" on that Page... (with '!ERRORIGNORE=YES' of course, and '!TIMEOUT_STEP=0' (that can also be conditionally toggled if needed).

Now if you are using iMacros for FF until v9.0.3 (recommended is v8.9.7), you can use a '.js' Script from which you can completely control the Workflow of your Script, and loop and nested-loop up too as many nested Levels as you want any Part of your Script.
The same Functionality is also available if you use the 'Scripting Interface' (only available in iMB 'Enterprise Edition') where you can use any Prog Language, => 'VBS' that you seem to like, for example... :wink:

And New in iMB v12.6, you now also have a '-playlist' Command Line Parameter (supported in iMB 'Pro' + 'Enterprise') that could maybe "do the trick", but hum..., I don't know how it will 'behave" when using a DataSource... :idea:

Ok so there is no easy way to repeat some code even If I have the number of repetitions inside my macro as variable :(
chivracq
Posts: 9374
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: loop reading csv till the end of file

Post by chivracq » Thu May 07, 2020 1:53 pm

lukasz wrote:
Thu May 07, 2020 1:24 pm
Ok so there is no easy way to repeat some code even If I have the number of repetitions inside my macro as variable :(

Well, the "easy way" is to use a '.js' Script with iMacros for FF or the 'Scripting Interface'... :|

I have an Enhancement Request about some "basic" 'GOTO' Command like we used to have in 'Basic'/'qBasic' in the 1980's :oops: in this Thread where I maintain a List of EnhReq's (Item '11', feel free to "support" it, + any other EnhReq's in that List you might find "useful", ah-ah...!), but tja..., there is no Chance I think it will ever be implemented, @Dev and "the Company", this is "by Design", and they want to make some Money I guess from selling 'Enterprise' Licenses for Advanced/complex (Web-)Automation Projects...

But again, try using the '-playlist' Param with your .bat' File, instead of the '-var' Param that you are currently using..., that might be "a Way"... :idea:
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
lukasz
Posts: 31
Joined: Fri Jan 24, 2020 10:26 am

Re: loop reading csv till the end of file

Post by lukasz » Thu May 21, 2020 2:07 pm

now trying to do it using VBS
once again describe the problem in the same post.

source files appear in my folder and have names in the format
file_1.csv
file_2.csv
...

but I never know what number the next file will have

my macro uses these files as a data source and should be run as long as there are files in the directory - after execution the file is deleted.

I created a simple script in VBS which is supposed to work in such a loop as long as there are files in the folder:
it search for file with specific name and set filename as a variable for use as datasource in macro
it will also check for rows number in csv but this is not yet implemented

this code works good except one thing
for each next loop it excute macro one more time
so it will first load file_1.csv and run my code once
next it will load file_2.csv and put content of "dim macro" twice and run it twice for file_2.csv
what is wrong with my loop?



Code: Select all

With CreateObject("Scripting.FileSystemObject")
    For Each File In .GetFolder("C:\ORDER_FILE").Files
        If StrComp(Left(File.Name, 4), "test", vbTextCompare) = 0 Then
		WScript.Echo File.Path
		
		Dim FilePath
		FilePath = File.Path

		Set fso = CreateObject("Scripting.FileSystemObject") 
		Set theFile = fso.OpenTextFile(File.Path, 8, True) 
		WScript.Echo theFile.Line 

		Dim FileLines
		FileLines = theFile.Line

		Dim iim1, iret

		'initialize iMacros instance
		set iim1= CreateObject ("imacros")
		iret = iim1.iimOpen()
		var=iim1.iimset ("braas",FilePath )


		dim macro
		
			macro = macro & "SET !DATASOURCE {{braas}}" & vbNewLine
			SOME CODE
			SOME CODE
			SOME CODE
			SOME CODE
			SOME CODE
			SOME CODE
			
			
			iret = iim1.iimPlayCode(macro)
			iret = iim1.iimClose()





		Set Fso = Nothing
        End If
    Next
End With
lukasz
Posts: 31
Joined: Fri Jan 24, 2020 10:26 am

Re: loop reading csv till the end of file

Post by lukasz » Fri May 22, 2020 2:10 pm

ok i was able to solve this issue but now i cant create working loop to read csv file

i have rows number in my VBS as a variable FileLines but this code read only first line of csv and then error:

Code: Select all

22.05.2020 16:04:53 PID:1840 START <MacroCode>
22.05.2020 16:04:53 PID:1840 Error -1240: Macro: <MacroCode>, line 1. (-1240) Record index exceeds the number of records.


Code: Select all

dim magicloop

magicloop = magicloop & "SET !DATASOURCE_LINE {{counter}}"& vbNewLine
magicloop = magicloop & "TAG POS=1 TYPE=TD ATTR=TXT:{{!COL1}}" & vbNewLine
magicloop = magicloop & "TAG POS=R1 TYPE=INPUT:TEXT ATTR=NAME:bs CONTENT={{!COL2}}" & vbNewLine



loop:

Code: Select all

		Dim counter
		counter = 1

		Do Until (counter > FileLines)
			iim1.iimSet "counter", counter
			iret = iim1.iimPlayCode(magicloop)
			counter = counter + 1
		Loop

Please help me
chivracq
Posts: 9374
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: loop reading csv till the end of file

Post by chivracq » Fri May 22, 2020 2:46 pm

lukasz wrote:
Fri May 22, 2020 2:10 pm
ok i was able to solve this issue but now i cant create working loop to read csv file

i have rows number in my VBS as a variable FileLines but this code read only first line of csv and then error:

Code: Select all

22.05.2020 16:04:53 PID:1840 START <MacroCode>
22.05.2020 16:04:53 PID:1840 Error -1240: Macro: <MacroCode>, line 1. (-1240) Record index exceeds the number of records.


Code: Select all

dim magicloop

magicloop = magicloop & "SET !DATASOURCE_LINE {{counter}}"& vbNewLine
magicloop = magicloop & "TAG POS=1 TYPE=TD ATTR=TXT:{{!COL1}}" & vbNewLine
magicloop = magicloop & "TAG POS=R1 TYPE=INPUT:TEXT ATTR=NAME:bs CONTENT={{!COL2}}" & vbNewLine



loop:

Code: Select all

		Dim counter
		counter = 1

		Do Until (counter > FileLines)
			iim1.iimSet "counter", counter
			iret = iim1.iimPlayCode(magicloop)
			counter = counter + 1
		Loop

Please help me

The Syntax for 'iimSet()' is incorrect I think..., => the "()" are missing... :idea:

I'm actually "nearly surprised" you don't get some Compiler/Parser Error... :o
- (F)CI(M) = (Full) Config Info (Missing): iMacros + Browser + OS (+ all 3 Versions + 'Free'/'PE').
- I don't even read the Qt if that (required) Info is not mentioned...!
- Script & URL help a lot for more "educated" Help...
Post Reply