![]() |
|
![]() |
AutoMate Discussion | ![]() |
Topic Title: An alternative to Loop Dataset Topic Summary: Created On: 11/17/2009 06:16 PM Status: Read Only |
Linear : Threading : Single : Branch |
![]() |
![]()
|
![]() Answer: Eureka! | ||||||||
![]() |
||||||||
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.
|
||||||||
|
||||||||
![]() |
||||||||
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.
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
||||||||
![]() |
||||||||
And this is the BASIC way:
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 ![]() |
||||||||
|
||||||||
![]() |
||||||||
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:
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||
|
||||||||
![]() |
||||||||
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
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 ![]() |
||||||||
|
||||||||
![]() |
||||||||
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.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||
|
||||||||
![]() |
||||||||
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
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
||||||||
![]() |
||||||||
I'll update you on how it went
![]() Thanks
|
||||||||
|
||||||||
![]() |
||||||||
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.
|
||||||||
|
||||||||
![]() |
||||||||
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.
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
||||||||
![]() |
||||||||
Here is the code.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||
|
||||||||
![]() |
||||||||
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
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 |
||||||||
|
||||||||
![]() |
||||||||
How do I break out of a loop when a match is found? Is GoTo NextFor the break?
|
||||||||
|
||||||||
![]() |
||||||||
The exit statement. In this example the For loop will be broken when n is greater than 100.
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 ![]() |
||||||||
|
||||||||
![]() |
||||||||
You can avoid GOTO's by using a full flow:
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 ![]() |
||||||||
|
||||||||
![]() |
||||||||
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 ![]()
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||
|
||||||||
![]() |
||||||||
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.
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
||||||||
![]() |
||||||||
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.
|
||||||||
|
||||||||
![]() |
||||||||
I'm afraid you would have to post your entire task for us to see.
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
||||||||
![]() |
||||||||
Thanks for your further help. Attached is the code.
Thanks again.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||
|
||||||||
![]() |
||||||||
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 ?
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 ![]() |
||||||||
|
||||||||
![]() |
||||||||
---
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||
|
||||||||
![]() |
||||||||
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.
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
||||||||
![]() |
||||||||
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: < < < < About to go crazy ![]() Attached are 2 files of the MasterList's 3 rows and Price List's all rows. Regards, Serkan
|
||||||||
|
||||||||
![]() |
||||||||
Can you post the script that creates the error ?
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
||||||||
![]() |
||||||||
Eureka!
Thank you very very MUCH Jean! Your the best! Thanks for your awesome help! So happy. Thank you!
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||
|
||||||||
![]() |
||||||||
Eureka!
|
||||||||
|
||||||||
![]() |
||||||||
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.
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
||||||||
![]() |
||||||||
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...
|
||||||||
|
||||||||
![]() |
||||||||
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.
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
||||||||
![]() |
||||||||
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?
|
||||||||
|
||||||||
![]() |
||||||||
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.
|
||||||||
|
||||||||
![]() |
||||||||
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 ?
------------------------- Jean Delfosse - Automate Beta Tester Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4 ![]() |
||||||||
|
FuseTalk Enterprise Edition v4.0 - © 1999-2021 FuseTalk Inc. All rights reserved.