Get number of lines from CSV and use as variable?

Support for iMacros. The iMacros software is the unique solution for automating every activity inside a web browser, for data extraction and web testing.

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.

Get number of lines from CSV and use as variable?

by J4r0sl4v on Sun May 01, 2016 4:30 am

I have a simple script that visits websites listed in "database.csv".
Unfortunately I can't make it loop as many times as many lines are in the CSV, just a fix (like 10 times) as in the example.

Can someone please tell me how can I get the row number from "database.csv" and use it the script below?

Win 8.1 + FFox 45.0.2 + iMacros 8970419

Code: Select all
var macro, iret;

function task()
{
   iimSet("REPEAT", REPEAT);
   macro = "CODE:";
   macro += "VERSION BUILD=8961227 RECORDER=FX\n";   
   macro += "SET !EXTRACT_TEST_POPUP NO\n";
   macro += "SET !TIMEOUT_STEP 0\n";
   macro += "SET !ERRORIGNORE YES\n"; 
   
   macro += "SET !DATASOURCE c:\\work\\database.csv\n";
   macro += "SET !DATASOURCE_LINE {{REPEAT}}\n";
   macro += "URL GOTO={{!COL1}}\n";
   macro += "WAIT SECONDS=2\n";
   macro += "TAG POS=1 TYPE=A ATTR=CLASS:click-this\n";
   macro += "WAIT SECONDS=1\n";
   iret = iimPlay(macro)
}

for(REPEAT=0; REPEAT<10; REPEAT++)
{
   task();
}

finish();
J4r0sl4v
 
Posts: 11
Joined: Fri Feb 12, 2016 8:18 am

Re: Get number of lines from CSV and use as variable?

by chivracq on Sun May 01, 2016 11:13 am

J4r0sl4v wrote:I have a simple script that visits websites listed in "database.csv".
Unfortunately I can't make it loop as many times as many lines are in the CSV, just a fix (like 10 times) as in the example.

Can someone please tell me how can I get the row number from "database.csv" and use it the script below?

Win 8.1 + FFox 45.0.2 + iMacros 8970419

Code: Select all
var macro, iret;

function task()
{
   iimSet("REPEAT", REPEAT);
   macro = "CODE:";
   macro += "VERSION BUILD=8961227 RECORDER=FX\n";   
   macro += "SET !EXTRACT_TEST_POPUP NO\n";
   macro += "SET !TIMEOUT_STEP 0\n";
   macro += "SET !ERRORIGNORE YES\n"; 
   
   macro += "SET !DATASOURCE c:\\work\\database.csv\n";
   macro += "SET !DATASOURCE_LINE {{REPEAT}}\n";
   macro += "URL GOTO={{!COL1}}\n";
   macro += "WAIT SECONDS=2\n";
   macro += "TAG POS=1 TYPE=A ATTR=CLASS:click-this\n";
   macro += "WAIT SECONDS=1\n";
   iret = iimPlay(macro)
}

for(REPEAT=0; REPEAT<10; REPEAT++)
{
   task();
}

finish();

Hum, interesting Technique to put your on the fly generated Macro into a Function, neat...!

You have in this Thread a Solution provided by TechSupport.

Other Methods would be to first have a first Sub-Macro loop through your DataSource until the Length of '{{!COL1}}' is 0, but depending on the Size of your '.CSV', TechSupport's Method will probably be faster...
Other Method is to make your Macro loop "indefinitely" and check for each Run as well if the Length of the Next Row is still >0 to break the Loop.
Oh...!, and another quick and easy Method if you generate/maintain your '.CSV' File from Excel is to have a Cell placed on the First Row and to let Excel automatically compute Row Count and you then first retrieve that Value from a first Sub-Macro and reuse it in your 'js' Script.
- (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: 6475
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Get number of lines from CSV and use as variable?

by J4r0sl4v on Sun May 01, 2016 2:52 pm

Unfortunately I don't know sh!t about javascript in general :roll:
(I tried to learn it, but even if I am not a newbie, I have don't have a programmer mindset, I am more of a visual person, I do graphics design btw.)

So thanks for the Tech Support example, but I still have no idea:
- Where that script puts the value? (I guess columns variable)
- If I try to pass the "columns" variable to my iMacros like this (don't laugh) :D

Code: Select all
if((columns) < 1)
{
success = "CODE:\n";
success += "URL GOTO=http://google.com\n";
iimPlay(success);
}
else
fail = "CODE:\n";
fail += "URL GOTO=http://youtube.com\n";
iimPlay(fail);


Of course I get an error message:
ReferenceError: column is not defined, line 99 (Error code: -991)


So the value is NOT in the "columns" because that is not even defined. :roll:
J4r0sl4v
 
Posts: 11
Joined: Fri Feb 12, 2016 8:18 am

Re: Get number of lines from CSV and use as variable?

by chivracq on Sun May 01, 2016 3:29 pm

J4r0sl4v wrote:Unfortunately I don't know sh!t about javascript in general :roll:
(I tried to learn it, but even if I am not a newbie, I have don't have a programmer mindset, I am more of a visual person, I do graphics design btw.)

So thanks for the Tech Support example, but I still have no idea:
- Where that script puts the value? (I guess columns variable)
- If I try to pass the "columns" variable to my iMacros like this (don't laugh) :D

Code: Select all
if((columns) < 1)
{
success = "CODE:\n";
success += "URL GOTO=http://google.com\n";
iimPlay(success);
}
else
fail = "CODE:\n";
fail += "URL GOTO=http://youtube.com\n";
iimPlay(fail);


Of course I get an error message:
ReferenceError: column is not defined, line 99 (Error code: -991)


So the value is NOT in the "columns" because that is not even defined. :roll:

Graphic Designer is perfect, it is Creativity which is the most important Skill required to do exciting things with iMacros, I am an Artist ((Body) Painting) and DJ/VJ myself and I'm no JavaScript Guru either at all, I don't even use any '.js' Scripts at all, I do all my Coding in pure '.iim'...

But concerning TechSup's Script (which honestly I find the most complicated Option from all Options I've mentioned), I think you don't need 'columns' in your case, but 'lines' which is an Array containing all Rows and 'lines.length' is the Value you are after, the Length of the Array will be the Number of Rows.

The Excel Solution I mentioned with a Computed Cell would be very simple, that's the easiest Option I would think...
- (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: 6475
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Get number of lines from CSV and use as variable?

by blasouveir on Fri Aug 11, 2017 2:50 pm

This may be an old thread, but since the issue is not really resolved, I figured I could bump in with my solution. I was searching for the same problem, and since I couldn't find a suitable solution, I figured I could write my own code. I am sharing it here in hopes that someone may find this useful in the future.

Basically this is a javascript snippet that should return the number of rows in the .csv file, as well as the contents of the last line.
This code assumes that the value in the second column is never an empty string.
For my purposes, the csv file was generated with another iMacro, and I made sure to put a timestamp value in that column programatically, so I know that it is never an empty string.

Code: Select all
      
         const LOG_FULL_PATH = "c://path_to_your_csv//file.csv";
         var i=1;
         var load =  "CODE:";
         load +=  "SET !EXTRACT NULL" + "\n";
         load +=  "SET !DATASOURCE " + LOG_FULL_PATH + "\n";
         load +=  "SET !DATASOURCE_COLUMNS 2" + "\n";
         load +=  "SET !DATASOURCE_LINE " + i + "\n";
         load +=  "SET !extract {{!col1}}" + "\n";
         load +=  "ADD !extract {{!col2}}" + "\n";
         iimPlay(load);
         value=iimGetLastExtract(0);
         prevValue = value;

         while (value != "") {
            i++;   
            load =  "CODE:";
            load +=  "set !EXTRACT NULL" + "\n";
            load +=  "SET !DATASOURCE " + LOG_FULL_PATH + "\n";
            load +=  "SET !DATASOURCE_COLUMNS 2" + "\n";
            load +=  "SET !DATASOURCE_LINE " + i + "\n";
            load +=  "SET !extract {{!col1}}" + "\n";
            load +=  "ADD !extract {{!col2}}" + "\n";
            iimPlay(load);
            prevValue = value;
            value=iimGetLastExtract(0);
         }

         lastLog = prevValue.split("[EXTRACT]");
         var numberOfRows = i-1;


lastLog is an array which contains the elements of the last row (lastLog[0] is the first column, lastLog[1] is the second one)
numberOfRows returns the number of rows in the csv file.

The purpose of my iMacro is to do a certain task, and write a log line in a csv file, then another iMacro reads the last line in the same csv file (the line that the previous iMacro wrote), extract the values and do some tasks based on those values.
I have to mention that this is a daily task, so after a while the csv file gets filled up, and every day the task is executed slower then the previous one. Therefore, an occasional maintenance is required to clean up the csv file.
blasouveir
 
Posts: 2
Joined: Sat Jul 15, 2017 4:56 am

Re: Get number of lines from CSV and use as variable?

by chivracq on Fri Aug 11, 2017 6:27 pm

blasouveir wrote:This may be an old thread, but since the issue is not really resolved, I figured I could bump in with my solution. I was searching for the same problem, and since I couldn't find a suitable solution, I figured I could write my own code. I am sharing it here in hopes that someone may find this useful in the future.

Basically this is a javascript snippet that should return the number of rows in the .csv file, as well as the contents of the last line.
This code assumes that the value in the second column is never an empty string.
For my purposes, the csv file was generated with another iMacro, and I made sure to put a timestamp value in that column programatically, so I know that it is never an empty string.

Code: Select all
      
         const LOG_FULL_PATH = "c://path_to_your_csv//file.csv";
         var i=1;
         var load =  "CODE:";
         load +=  "SET !EXTRACT NULL" + "\n";
         load +=  "SET !DATASOURCE " + LOG_FULL_PATH + "\n";
         load +=  "SET !DATASOURCE_COLUMNS 2" + "\n";
         load +=  "SET !DATASOURCE_LINE " + i + "\n";
         load +=  "SET !extract {{!col1}}" + "\n";
         load +=  "ADD !extract {{!col2}}" + "\n";
         iimPlay(load);
         value=iimGetLastExtract(0);
         prevValue = value;

         while (value != "") {
            i++;   
            load =  "CODE:";
            load +=  "set !EXTRACT NULL" + "\n";
            load +=  "SET !DATASOURCE " + LOG_FULL_PATH + "\n";
            load +=  "SET !DATASOURCE_COLUMNS 2" + "\n";
            load +=  "SET !DATASOURCE_LINE " + i + "\n";
            load +=  "SET !extract {{!col1}}" + "\n";
            load +=  "ADD !extract {{!col2}}" + "\n";
            iimPlay(load);
            prevValue = value;
            value=iimGetLastExtract(0);
         }

         lastLog = prevValue.split("[EXTRACT]");
         var numberOfRows = i-1;


lastLog is an array which contains the elements of the last row (lastLog[0] is the first column, lastLog[1] is the second one)
numberOfRows returns the number of rows in the csv file.

The purpose of my iMacro is to do a certain task, and write a log line in a csv file, then another iMacro reads the last line in the same csv file (the line that the previous iMacro wrote), extract the values and do some tasks based on those values.
I have to mention that this is a daily task, so after a while the csv file gets filled up, and every day the task is executed slower then the previous one. Therefore, an occasional maintenance is required to clean up the csv file.

Arggghhh-Arggghhh-Arggghhh, Shit-Shit-Shit...!!! :mrgreen: :twisted: :mrgreen:
OK, I had started typing a (fairly) long Reply, that got lost when doing some Testing before saving/posting..., re-aarrrggghhh...!! :mrgreen:

OK, I was saying Thanks for sharing your Solution, always useful for other Users, but that you still should have mentioned in which Env you had tested it as many Commands regularly get broken/not supported anymore from a specific Version for iMacros or FF...
=> Probable FCI: iMacros for FF v8.9.7 or v9.0.3, FF54, Win-x32/x64.

Then I was saying that your Solution would indeed probably be fairly slow, about 10-20 sec I would think for 100 Rows, and I posted another Solution which I expected to be much quicker, around 1 sec for 1000 Rows I was thinking, and that's when I went testing it, that I lost my Reply, ah-ah...!

I don't feel like typing it all over again right now, a bit tired, I'll try to post it tomorrow or when I have the time, good News is that it was clocking at 0.350-0.450 sec for 1000 Rows in the best Conditions (then it only takes 3 Lines, but there are 3 different "Cases"), and in the "worst" Case, it was still doing its Job in 0.850-0.950 sec for the same 1000 Rows... (on a 40Kb File), while I reckon your Solution would need about 1.5-2 min. OK, I'll try to post it tomorrow, even if hum, I have some Event that might end up quite late, OK, I'll see... 8)
- (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: 6475
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)

Re: Get number of lines from CSV and use as variable?

by blasouveir on Fri Aug 11, 2017 11:55 pm

chivracq wrote:OK, I was saying Thanks for sharing your Solution, always useful for other Users, but that you still should have mentioned in which Env you had tested it as many Commands regularly get broken/not supported anymore from a specific Version for iMacros or FF...
=> Probable FCI: iMacros for FF v8.9.7 or v9.0.3, FF54, Win-x32/x64.

Yeah, I didn't feel like mentioning that, since it's fairly similar to OP's configuration: FF v.47.0.2, iMacros 8.9.7, Windows 10 x64

chivracq wrote:I don't feel like typing it all over again right now, a bit tired, I'll try to post it tomorrow or when I have the time, good News is that it was clocking at 0.350-0.450 sec for 1000 Rows in the best Conditions (then it only takes 3 Lines, but there are 3 different "Cases"), and in the "worst" Case, it was still doing its Job in 0.850-0.950 sec for the same 1000 Rows... (on a 40Kb File), while I reckon your Solution would need about 1.5-2 min. OK, I'll try to post it tomorrow, even if hum, I have some Event that might end up quite late, OK, I'll see... 8)

The speed of execution I've noticed to be varying greatly depending on the machine you are testing on. I've noticed my laptop excecutes all the macros up to 5 times slower. Also, the same macro excecuted on the same machine can show different results, depending mostly on other processes running at the same time.

Well, I know that my solution is slow. Anything that has to itereate all the rows in a file is not recommended in terms of speed, but it was the only one I could think of (with my 1 month iMacros experience) that would work in my case. The best case scenario would be to just have a built-in function that will spit out the number of rows, not having to iterate through all of them. If you got a faster one, by all means, please share it!

A month ago I didn't know squat about iMacros, what I've learned that some fairly simple tasks that you take for granted in other programming languages, in iMacros are a real pain.
blasouveir
 
Posts: 2
Joined: Sat Jul 15, 2017 4:56 am

Re: Get number of lines from CSV and use as variable?

by chivracq on Mon Aug 14, 2017 9:35 pm

blasouveir wrote:
chivracq wrote:OK, I was saying Thanks for sharing your Solution, always useful for other Users, but that you still should have mentioned in which Env you had tested it as many Commands regularly get broken/not supported anymore from a specific Version for iMacros or FF...
=> Probable FCI: iMacros for FF v8.9.7 or v9.0.3, FF54, Win-x32/x64.

Yeah, I didn't feel like mentioning that, since it's fairly similar to OP's configuration:
Code: Select all
FF v.47.0.2, iMacros 8.9.7, Windows 10 x64.

Yeah maybe, but sorry, I usually don't react to Threads when FCI is not mentioned..., I only reacted to your Post because you were sharing a Solution..., hum, and I thought I had a "better" one...
And yep, @OP was on FF45 in May 2017, was only 3 months ago, current FF Version is now FF54 in Aug 2017, I'm on FF57, we are talking about 9-12 Versions further, a lot of (stupid...!) "things" have happened to FF since FF45...

blasouveir wrote:
chivracq wrote:I don't feel like typing it all over again right now, a bit tired, I'll try to post it tomorrow or when I have the time, good News is that it was clocking at 0.350-0.450 sec for 1000 Rows in the best Conditions (then it only takes 3 Lines, but there are 3 different "Cases"), and in the "worst" Case, it was still doing its Job in 0.850-0.950 sec for the same 1000 Rows... (on a 40Kb File), while I reckon your Solution would need about 1.5-2 min. OK, I'll try to post it tomorrow, even if hum, I have some Event that might end up quite late, OK, I'll see... 8)

The speed of execution I've noticed to be varying greatly depending on the machine you are testing on. I've noticed my laptop excecutes all the macros up to 5 times slower. Also, the same macro excecuted on the same machine can show different results, depending mostly on other processes running at the same time.

Well, I know that my solution is slow. Anything that has to itereate all the rows in a file is not recommended in terms of speed, but it was the only one I could think of (with my 1 month iMacros experience) that would work in my case. The best case scenario would be to just have a built-in function that will spit out the number of rows, not having to iterate through all of them. If you got a faster one, by all means, please share it!

A month ago I didn't know squat about iMacros, what I've learned that some fairly simple tasks that you take for granted in other programming languages, in iMacros are a real pain.

Yep, Speed can depend on the "Machine", but don't worry, in the worst case, "my" Solution takes max 1 sec to run for 1000 Rows / 40Kb of Data, while loading some "Standard" Web-Page usually takes around 5 sec, I ran my Tests with 3 or 4 other Scripts running at the same time in parallel in 3 different Profiles with one native JS Script doing 100-140 Clicks per sec on some stupid Cookie (CookieClicker) + some iMacros running at the same time on the same Page + listening to some Music and maybe 500 Tabs opened in all my 4 or 5 Browser Instances opened at that time, and my Laptop is not a Gamer Computer, ah-ah...! So the Conditions were definitely not "optimal"...!

But OK, I like to play "Games", here is my "winning" Script that does the job in 0.350-0.450 Sec, you can test it by yourself with very little Adaptation on the File and its Path..., see if you can "guess" the missing Explanations...:
Code: Select all
VERSION BUILD=8820413 RECORDER=FX
TAB T=1

'TAB OPEN
'TAB T=2
'Or use 'EVENT' Mode on 'Ctrl^9'...
'>
'Open File with 1000 Rows of Data, 40Kb:
URL GOTO=file:///D:/TEMP/iMacros/Current/iMacros/Downloads/SAVEAS_Temp_20170812_02h.log
'Extract on </pre></body></html>:
TAG POS=1 TYPE=PRE ATTR=* EXTRACT=TXT
'TAB CLOSE
'>
TAB T=1
SET Last_Row_Nb EVAL("var s='{{!EXTRACT}}'; var x,y,z; x=s.split('###'); y=x.length; z=y-2; z;")

'Add STOPWATCH: Total Macro Time:
STOPWATCH LABEL=Total_Macro
PROMPT STOPWATCH:<SP>{{!STOPWATCHTIME}}<BR><BR>Last_Row_Nb:<SP>_{{Last_Row_Nb}}_
(Tested on iMacros for FF v8.8.2, Pale Moon v26.3.3 (=FF47), Win10-x64.)

I had mentioned "3 Cases", I've left all my Debug Info in the Script about the 2 other "Cases", when doing the Row Count in a 2nd Tab, already opened (=> 0.650-0.850 Sec), or that needs to be opened (and closed) (=> 0.850-0.950 Sec).

3 Lines I said... (with some simplified/optimized Version for the 'EVAL()'...):
Code: Select all
URL GOTO=file:///D:/TEMP/My_LOG_File.log
TAG POS=1 TYPE=PRE ATTR=* EXTRACT=TXT
SET Last_Row_Nb EVAL("'{{!EXTRACT}}'.split('###').length-2;")
- (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: 6475
Joined: Sat Apr 13, 2013 6:07 am
Location: Amsterdam (NL)


Return to General Support & Discussions

Who is online

Users browsing this forum: No registered users and 6 guests

-->