Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: An alternative to Loop Dataset
Topic Summary:
Created On: 11/17/2009 06:16 PM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
Answer This question was answered by Babylon, on Sunday, November 22, 2009 9:29 PM

Answer:
Eureka!
 11/17/2009 06:16 PM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

I have a Excel sheet which the rows gets updated from another Excel sheet.

Both Excel sheets are loaded into datasets and I loop through each to get data.

It takes about 2 - 2.5 seconds per item. And currently it takes about 1.5 hrs for 2000 records, and I want to improve this if possible.

What would you recommend alternative to Loop Dataset?

One Excel sheet is a master list of products, the other is simply a list of categories of products. For each product a search is conducted on the categories list to see which category the product belongs to and it exits the loop when found.

The category list is approximately 400 records long.

Many thanks in advance.

 Category Survey
 11/17/2009 09:36 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Babylon,
finding corresponding items in two tables is really what SQL is designed for.
It runs quite fast.
But if you really want to loop the datasets, a basic script is the way to do it.

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/18/2009 12:14 AM
User is offline View Users Profile Print this message

Author Icon
Bart van Lagen
Supreme Guru (1000+)

Posts: 1400
Joined: 04/25/2005

And this is the BASIC way:

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Bart van Lagen
ICT Department.
St Jansdal Hospital
Harderwijk, Netherlands
Automate 7.1.1.0 / BPA 7.1.1.0
Windows 2003SP2SRV
 11/18/2009 01:32 AM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

Thank god scripts!

Could I please get help for this, can you convert this into a script so I can use that as an example for other loops:



 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 11/18/2009 05:21 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Babylon,
something like below. Of course I cannot test it.
You will see it looks quite similar to your AM code, so that you will easily be able to modify/debug it.

The For ix . . . .Next ix is in fact the LOOPDATASET


 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/18/2009 07:09 AM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

Hi Jean,

Thanks for the help, your code worked perfectly!

I tried implementing a similar script for another loop, but it doesn't work as expected, can you see the problem?

The result always seems to be 9999 for all records, it should change, but it doesn't.

Also shouldn't this:

If temp_3 = temp_2 Then ds_shopFile.n = ds_vendorList(ix).b
NextFor: ' this is label
Next ix
End Sub

Look like this?


If temp_3 = temp_2 Then ds_shopFile.n = ds_vendorList(ix).b
GoTo NextFor
End If
NextFor: ' this is label
Next ix
End Sub


Many thanks again.

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 11/18/2009 07:43 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

When the If and the Then clause are on the very same line, there is no EndIf needed, hence GoTo NextFor is not needed either, because it comes immediately after.

The only reasons I see for all shopfile.n to be 9999 is that pricelist.a is always empty (="") or dsvendorList.b is also eaqual to 9999

But you do not use the temp5 and temp6 that you have just declared.
Maybe the "if" should be
if temp5=temp6 then . . . ??? instead of if temp2=temp3

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/18/2009 08:00 AM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

I'll update you on how it went

Thanks

 Category Survey
 11/18/2009 08:16 AM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

Well, it still fails.

Here is the script:

Sub Main
For ix=1 To ds_vendorList.TotalRows
If ds_vendorList(ix).a = "" Then
ds_ShopFile.n = "9999"
GoTo NextFor
End If
temp_5 = ds_vendorList(ix).a
temp_6 = ds_masterList.c
If temp_5 = temp_6 Then ds_shopFile.n = ds_vendorList(ix).b
NextFor: ' this is label
Next ix
End Sub


The first 3 rows from Vendors List is as follows:
ACCESS COMMUNICATIONS Access Communications
A-DATA A-Data
ADESSO Adesso

The first 3 rows from Masters List is as follows:
2L-1000P/C Aten KVM Cable PS2M, PS2M, HD15M - PS2M, PS2M, HD15F 1.2m ATEN
2L-1001P/C Aten KVM Cable PS2M, PS2M, HD15M - PS2M, PS2M, HD15F 1.8m ATEN
2L-1003P/C Aten KVM Cable PS2M, PS2M, HD15M - PS2M, PS2M, HD15F 3m ATEN


See if I run the Loop Dataset it works fine.

Why is there " NextFor: ' this is label" in the script? What function does this serve?

Sorry I'm learning scripting still. Thanks for the help.

 Category Survey
 11/18/2009 09:42 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

The "NextFor:" is a label, referencing the line where the "GoTo NextFor" must go in the first IF
The " ' this is a label" is just a comment of mine to show you that this is indeed a label

Then the "Next ix" branches back to the "For ix" to perform the looping.

Could you post the LOOPDATASET that works fine ? So that I can compare it to the basic script.

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/18/2009 03:44 PM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

Here is the code.

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 11/18/2009 03:44 PM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

I've decided to avoid searching for the empty field, it works now with another AM step to check for the empty field.

Still, do you know why the above code didn't work?

Thanks

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


Edited: 11/18/2009 at 08:01 PM by Babylon
 11/18/2009 08:22 PM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

How do I break out of a loop when a match is found? Is GoTo NextFor the break?

 Category Survey
 11/19/2009 03:28 AM
User is offline View Users Profile Print this message

Author Icon
Bart van Lagen
Supreme Guru (1000+)

Posts: 1400
Joined: 04/25/2005

The exit statement. In this example the For loop will be broken when n is greater than 100.

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Bart van Lagen
ICT Department.
St Jansdal Hospital
Harderwijk, Netherlands
Automate 7.1.1.0 / BPA 7.1.1.0
Windows 2003SP2SRV
 11/19/2009 03:32 AM
User is offline View Users Profile Print this message

Author Icon
Bart van Lagen
Supreme Guru (1000+)

Posts: 1400
Joined: 04/25/2005

You can avoid GOTO's by using a full flow:

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Bart van Lagen
ICT Department.
St Jansdal Hospital
Harderwijk, Netherlands
Automate 7.1.1.0 / BPA 7.1.1.0
Windows 2003SP2SRV
 11/19/2009 05:12 AM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

Mate, scripting is really frustrating.

I don't understand why this script won't work as it should.

Here is first 3 rows from Master List's E Column:
(empty)
12
15

The Price List table contains:
1499.99 1.06
1249.99 1.065
999.99 1.07
749.99 1.08
499.99 1.09
374.99 1.095
249.99 1.1
149.99 1.1
99.99 1.12
74.999 1.16
49.999 1.18
24.999 1.25
9.999 1.35
4.99 1.7
0.001 2

From the Script the output comes out as this for first 3 rows:
9999
24
15.9

But actually, it should be, according to the table Price List:
9999 (because MasterList row is empty.
16.20
20.25

Sorry about the complexity, but the scripts for pricing is not working, I've tried alot of things including using Exit Sub and Exit All no luck.

Going crazy


 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 11/19/2009 05:39 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

The script when running takes the MadterList and ShopFile datasets at their current position that must have been set prior to running the script.
I assume they are set in the correct position by portions of the task that precede the basic script.
Are they ?
It is really very difficult helping to debug while not seeing the environment.

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/19/2009 05:57 AM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

Ok, Master List is the first loop. Shop List is a sub loop. But it shouldn't matter. The script should only find which row in Price List has a value which matches Master List value. When found, simply set the value for Shop row, exit the loop. Next time round, do another search for the current Master List and Shop List, by this time, their values would be updated as the loops counter goes up further down.

 Category Survey
 11/19/2009 07:11 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

I'm afraid you would have to post your entire task for us to see.

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/19/2009 07:19 AM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

Thanks for your further help. Attached is the code.

Thanks again.

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 11/20/2009 12:44 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Babylon,
I think I made a mistake in my first suggestion, which propagates any time you use it for subsequent loops.

The AMBREAK which is to get out of the loop does translates into "Exit For" while my suggestion was to Continue looping (GoTo NextFor)

I apologize for overlooking your code.
Could you try this one instead ?

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/20/2009 06:02 AM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

---

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 11/20/2009 06:09 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

If the problem is on this line
If tmp_4 > tmp_2 Then
and both are numbers try
If CDbl(tmp_4) > CDbl(tmp_2) Then

Same thing for
Round((CDbl(tmp_3)*CDbl(tmp_4)),2)

This will force the comparison and multiplication to run with numbers.

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/20/2009 06:53 AM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

I've tried that, but it printed out an error message:
Script Error: "Expecting an already dimensined array." At script line 11(Step 58....

Jean, the script is working fine to a certain degree. From my opinion, it doesn't exit the loop properly when a match is found.

Here is the scenario of what happens with this script:

<> 1st. Loop: Match found for "" empty field, replace with 9999, exit script.

<>2nd Loop: Match found for 12, it's higher then 9.999, so multiply by 1.35, another match is found 12 is higher then 4.99, so multiply by 1.7, another match is found 12 is higher then 0.001, so multiply by 2, replace with 24 <> It should have exited when it found 12 higher then 9.999, and set value to 12 * 1.35, but instead value is 24.

<>3rd Loop: Match found for 15, it's higher then 1499.99, replace with 15 * 1.06 which is 15.9, this is what the final result is. This is not correct.

<>4th Loop: Match found for 23, it's higher then 1499.99, replace with 23 * 1.06 which is 24.38, this is what the final result is. This is not correct.

About to go crazy

Attached are 2 files of the MasterList's 3 rows and Price List's all rows.

Regards,
Serkan


 Category Survey



 11/20/2009 08:25 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Can you post the script that creates the error ?

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 11/22/2009 09:29 PM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

Eureka!

Thank you very very MUCH Jean!

Your the best!

Thanks for your awesome help! So happy. Thank you!

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 11/22/2009 09:29 PM
User is offline View Users Profile Print this message

Author Icon
Babylon
Journeyman (50-99)

Posts: 60
Joined: 06/17/2006

Answer Answer
Eureka!

 Category Survey
 11/22/2009 10:53 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

You're welcome, Serkan.
I'm sorry I mislead you for some time with my misunderstanding the BREAK problem.
But happy it's now OK.

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 04/26/2013 05:56 PM
User is offline View Users Profile Print this message

Author Icon
JivaJentile
Journeyman (50-99)

Posts: 94
Joined: 03/07/2006

Hi Jean~
ive been wrestling with a related issue for some time. i bet you will come up with something for me...
we have a large number of workflows that upload files to FTP servers. sometimes these fail, usually due to connection issues with the ftp server, or 'out of memory' errors on the automate server. the big issue for us is how to recover from these failures. let me elaborate...
i have tried 2 main types of approach, file-by-file and batch uploads.
assume the files are staged in a folder on the local network.
once they upload successfully, they get moved to an archive folder.
this way, should the process fail, we can determine what got uploaded and what didnt. seems simple enough...
file-by file:
when i loop the folder, and upload/archive each file in turn, the process is WAY too slow. ie for 1000 files, it took over 2.5 hrs. this is not acceptable.
i tried looping a dataset instead, created in a previous step, to my surprise, it took even longer.
Batch:
the batch upload (*.* everything in the folder) is super fast, 5.5 min for the same files. if i create a results dataset, i can then use this in future step to validate the result for each file.
but there is a big problem with the batch method. IF there is an error during the upload, and the result dataset is lost, there is No Way to determine what uploaded and what didnt!

i have seen posts here on the forum re using basic script instead of the AM loops to speed things up;
also saw some re using a plain loop instead of a folder or dataset loop. but im not sure how i would apply this to my specific situation (as i need to do an FTP upload inside the loop).

hope i explained the issue properly. do you have any thoughts to share? my file by file code as exists looks something like this...
loop the folder
- upload the file
- catch any errors and skip to the next file (this orphans the file in the staging folder, which is what we want)
- if no error, go ahead and move the file to archive folder
endloop

i need to either speed up the file by file loop [and the repeated calls to the FTP may be just as bad as the looping]
or find a way to persist and report on the results dataset in the event of unhandled system exceptions.

sorry if this is a messy post. let me know if i can clarify anything for you. and thanks once again for your excellent advice...



 Category Survey
 04/27/2013 05:51 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Jiva,
there is no easy solution to recover loops interrupted by a crash.
Why not before the mass upload make a file of the filenames to upload,
then later (same task or later) make an FTP (short or long) list of the files in the FTP server, and check whether all (or which) files were actually uploaded.
This may not be a solution if the FTP server processes the files and deletes them.

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 04/29/2013 10:15 AM
User is offline View Users Profile Print this message

Author Icon
JivaJentile
Journeyman (50-99)

Posts: 94
Joined: 03/07/2006

Jean~
yes, i do save a list of the upload candidates to SQL before uploading them; unfortunately pulling a dir from the ftp to confirm is unreliable as several of our customers begin pulling down the files as they arrive.
my thinking at this point is whether there may be a way to setup a separate task in the workflow that gets called in the event of an upload failure and simply writes out the contents of the result set [if it still exists at that point; it is defined globally as a shared dataset]. i see that 'start task' is an option on the error pane, but have never done this and am not sure. it seems to me that if the upload task bombs, theres a good chance that the result set will be lost, but perhaps it will persist if we do this.
anyhow, something along these lines? what do you think?

 Category Survey
 04/29/2013 10:18 AM
User is offline View Users Profile Print this message

Author Icon
JivaJentile
Journeyman (50-99)

Posts: 94
Joined: 03/07/2006

Jean~
oops, too clarify, i am referring to the batch upload here, where the dilemma is having no way to confirm what was uploaded if the result dataset is lost.
as far as the file by file looping, i dont need to recover cause just the fact of files being orphaned in the staging folder will give us enough to do a manual correction; the looping problem is simply that it is way too slow.

 Category Survey
 04/30/2013 12:24 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Jiva,
normally the shared dataset is maintained during the whole workflow execution, independently of tasks success/failures.
But I think datasets behave as the variables do : the version of a variable (or dataset supposedly) modified by a task is returned to the shared element (var or ds) ONLY AFTER completion of the task, and I doubt it be the case if the task fails.
Hence I would preferably write incrementally the processed files names to a file, so that - whatever the outcome of the upload task - that log file can be processed in a wrap-up task at the end of the workflow after a possible failure of the uploading task, and then compared to the dataset in order to determine the missed uploads.

The workflow could then be summarized as follows :

Initial task to create the shared dataset of all files to upload
On success :
Upload task, that logs each uploaded file to a log file
On failure :
Wrap-up task that compares the shared dataset to the upload log, in order to be able to resume uploading if necessary, either in a subsequent task or even in a resume of the previous upload task if it is designed to do so.

For instance the wrap-up task could tick in the dataset all the uploaded files mentioned in the log.
And the upload task would loop the dataset and upload only those not-ticked files. First pass would try to process all files (since none has been ticked previously)
and second or following passes would upload the remaining not-ticked ones.

Do you see what I mean ?

 Category Survey


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1307 guests browsing this forum, which makes a total of 1307 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog