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

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

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)
Sub Main Dim i As Integer For i = 1 To Dataset.TotalRows MsgBox Dataset(i).Column1 MsgBox Dataset(i).Column2 Next i End Sub

-------------------------
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:

Category Survey

Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
<AMLOOP TYPE="DATASET" DATASET="ds_priceList"> <AMIF EXPRESSION="%ds_priceList.a% = &quot;&quot;"> <AMSET VARIABLENAME="ds_shopFile.i">9999</AMSET> <AMBREAK /> </AMIF> <AMSET VARIABLENAME="tmp_2">%ds_priceList.a%</AMSET> <AMSET VARIABLENAME="tmp_3">%ds_masterList.e%</AMSET> <AMSET VARIABLENAME="tmp_4">%ds_priceList.b%</AMSET> <AMIF EXPRESSION="%tmp_3% &gt;= %tmp_2%"> <AMSET VARIABLENAME="ds_shopFile.i">%round((tmp_3*tmp_4),2)%</AMSET> <AMBREAK /> </AMIF> </AMLOOP>

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)
Sub Main For ix=1 To ds_PriceList.TotalRows If ds_priceList(ix).a = "" Then ds_ShopFile.i = "9999" GoTo NextFor End If temp_2 = ds_priceList(ix).a temp_3 = ds_priceList(ix).b If temp_3 > temp_2 Then ds_shopFile.i = Round((temp_3*temp_4),2) NextFor: ' this is label Next ix End Sub

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

Category Survey

Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
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_3 = temp_2 Then ds_shopFile.n = ds_vendorList(ix).b NextFor: ' this is label Next ix End Sub <AMLOOP TYPE="DATASET" DATASET="ds_vendorList"> <AMIF EXPRESSION="%ds_vendorList.a% = &quot;&quot;"> <AMSET VARIABLENAME="ds_shopFile.n">9999</AMSET> <AMBREAK /> </AMIF> <AMSET VARIABLENAME="tmp_5">%ds_vendorList.a%</AMSET> <AMSET VARIABLENAME="tmp_6">%ds_masterList.c%</AMSET> <AMIF EXPRESSION="%tmp_5% = %tmp_6%"> <AMSET VARIABLENAME="ds_shopFile.n">%ds_vendorList.b%</AMSET> <AMBREAK /> </AMIF> </AMLOOP>

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

I'll update you on how it went

Thanks

Category Survey

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

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

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)
<AMLOOP TYPE="DATASET" DATASET="ds_vendorList"> <AMIF EXPRESSION="%ds_vendorList.a% = """> <AMSET VARIABLENAME="ds_shopFile.n">9999</AMSET> <AMBREAK /> </AMIF> <AMSET VARIABLENAME="tmp_5">%ds_vendorList.a%</AMSET> <AMSET VARIABLENAME="tmp_6">%ds_masterList.c%</AMSET> <AMIF EXPRESSION="%tmp_5% = %tmp_6%"> <AMSET VARIABLENAME="ds_shopFile.n">%ds_vendorList.b%</AMSET> <AMBREAK /> </AMIF> </AMLOOP>

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)
<AMSCRIPT>Sub Main For ix=1 To ds_vendorList.TotalRows 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 </AMSCRIPT> <AMIF EXPRESSION="%ds_shopFile.n% = """> <AMSET VARIABLENAME="ds_shopFile.n">9999</AMSET> </AMIF>

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?

Category Survey

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)
For n = 1 To 1000 If n > 100 Then Exit For Endif Next n

-------------------------
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:

Category Survey

Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
For ix=1 To ds_vendorList.TotalRows If ds_vendorList(ix).a = "error" Then Exit For EndIf If ds_vendorList(ix).a = "" Then ds_ShopFile.n = "9999" Else temp_5 = ds_vendorList(ix).a temp_6 = ds_masterList.c If temp_5 = temp_6 Then ds_shopFile.n = ds_vendorList(ix).b EndIf EndIf Next ix

-------------------------
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

Category Survey

Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
Sub Main For ix=1 To ds_PriceList.TotalRows If ds_masterList.e = "" Then ds_shopFile.i = "9999" Else If ds_masterList.e > ds_priceList(ix).a Then ds_shopFile.i = Round((ds_priceList(ix).b*ds_masterList.e),2) Exit For End If End If Next ix End Sub

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

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

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

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)
<AMENDPROCESS PROCESS="%prg_excel%" SAMEPROCESS="all" AM_ONERROR="CONTINUE" /> <!-- VARIABLE - Counter --> <AMVARIABLE NAME="cnt_1">2</AMVARIABLE> <!-- VARIABLE - Temporary Data --> <AMVARIABLE NAME="tmp_1"></AMVARIABLE> <AMVARIABLE NAME="tmp_2"></AMVARIABLE> <AMVARIABLE NAME="tmp_3"></AMVARIABLE> <AMVARIABLE NAME="tmp_4"></AMVARIABLE> <AMVARIABLE NAME="tmp_5"></AMVARIABLE> <AMVARIABLE NAME="tmp_6"></AMVARIABLE> <AMVARIABLE NAME="tmp_7"></AMVARIABLE> <AMVARIABLE NAME="tmp_8"></AMVARIABLE> <AMVARIABLE NAME="priceTime2"></AMVARIABLE> <AMVARIABLE NAME="categryTime2"></AMVARIABLE> <AMVARIABLE NAME="vendorTime2"></AMVARIABLE> <!-- Generate Shop File --> <AMFILEDELETE SOURCE="%fld_ebusiness%\Shop File\%nm_supname%_SF.xls" AM_ONERROR="CONTINUE" /> <AMFILECOPY SOURCE="%fld_ebusiness%\Templates\Ecommerce_TMP.xls" DEST="%fld_ebusiness%\Shop File\%nm_supname%_SF.xls" OVERWRITE="YES" AM_ONERROR="CONTINUE" /> <AMEXCELOPENWORKBOOK WORKBOOK="%fld_ebusiness%\Shop File\%nm_supname%_SF.xls" SESSION="ss_shopFile" VISIBLE="NO" /> <AMEXCELCELLSTODATASET SESSION="ss_shopFile" RESULTDATASET="ds_shopFile" STARTCELLROW="1" STARTCELLCOLUMN="1" ENDCELLROW="%row_endShopfile%" ENDCELLCOLUMN="%col_endShopfile%" /> <AMEXCELOPENWORKBOOK WORKBOOK="%fld_ebusiness%\VendorLists\%nm_supname%_VL.xls" SESSION="ss_vendorList" VISIBLE="NO" /> <AMEXCELCELLSTODATASET SESSION="ss_vendorList" RESULTDATASET="ds_vendorList" STARTCELLROW="1" STARTCELLCOLUMN="1" ENDCELLROW="%row_endVendorList%" ENDCELLCOLUMN="%col_endVendorList%" /> <AMEXCELCLOSEWORKBOOK SESSION="ss_vendorList" /> <AMEXCELOPENWORKBOOK WORKBOOK="%fld_ebusiness%\CategoryLists\%nm_supname%_CL.xls" SESSION="ss_categoryList" VISIBLE="NO" /> <AMEXCELCELLSTODATASET SESSION="ss_categoryList" RESULTDATASET="ds_categoryList" STARTCELLROW="1" STARTCELLCOLUMN="1" ENDCELLROW="%row_endCategoryList%" ENDCELLCOLUMN="%col_endCategoryList%" /> <AMEXCELCLOSEWORKBOOK SESSION="ss_categoryList" /> <AMEXCELOPENWORKBOOK WORKBOOK="%fld_ebusiness%\PriceLists\%nm_supname%_PL.xls" SESSION="ss_priceList" VISIBLE="NO" /> <AMEXCELCELLSTODATASET SESSION="ss_priceList" RESULTDATASET="ds_priceList" STARTCELLROW="2" STARTCELLCOLUMN="1" ENDCELLROW="%row_endPriceList%" ENDCELLCOLUMN="%col_endPriceList%" /> <AMEXCELCLOSEWORKBOOK SESSION="ss_priceList" /> <AMEXCELOPENWORKBOOK WORKBOOK="%fld_ebusiness%\MasterLists\%nm_supname%_ML.xls" SESSION="ss_masterList" VISIBLE="NO" /> <AMEXCELCELLSTODATASET SESSION="ss_masterList" RESULTDATASET="ds_masterList" STARTCELLROW="1" STARTCELLCOLUMN="1" ENDCELLROW="%row_endMaster%" ENDCELLCOLUMN="%col_endMaster%" /> <AMEXCELCLOSEWORKBOOK SESSION="ss_masterList" /> <AMLOOP TYPE="DATASET" DATASET="ds_masterList" FROM="2" TO="%row_endMaster%"> <AMIF EXPRESSION="ds_masterList.a = """> <AMBREAK /> </AMIF> <AMIF EXPRESSION="ds_masterList.l <> 1"> <AMLOOP TYPE="DATASET" DATASET="ds_shopFile" FROM="%cnt_1%" TO="%cnt_1%"> <AMINCREMENTVARIABLE RESULTVARIABLE="cnt_1" /> <!-- v_products_model --> <AMSET VARIABLENAME="ds_shopFile.a">%nm_supcode + ds_masterList.a%</AMSET> <!-- v_products_image --> <AMIF EXPRESSION="%ds_masterList.m% = 1"> <AMSET VARIABLENAME="tmp_1">%ds_masterList.a%</AMSET> <AMREPLACE TEXT="%tmp_1%" FIND="/" REPLACE="-" RESULTVARIABLE="tmp_1" /> <AMSET VARIABLENAME="ds_shopFile.b">products/%nm_supcode%/%nm_supcode + tmp_1%.jpg</AMSET> <AMELSE /> <AMSET VARIABLENAME="ds_shopFile.b">products/photo-coming-soon.jpg</AMSET> </AMIF> <!-- v_products_name_1 --> <AMSET VARIABLENAME="ds_shopFile.c">%ds_masterList.b%</AMSET> <!-- v_products_description_1 --> <AMSET VARIABLENAME="ds_shopFile.d">%ds_masterList.j%</AMSET> <!-- v_products_price --> <!-- <AMSTARTTIMER SESSION="priceTime" /> --> <!-- <AMSCRIPT>Sub Main For ix=1 To ds_PriceList.TotalRows temp_2 = ds_priceList(ix).a temp_3 = ds_priceList(ix).b temp_4 = ds_masterList.e If temp_4 >= temp_2 Then ds_shopFile.i = Round((temp_3*temp_4),0) Else GoTo NextFor End If NextFor: ' this is label Next ix End Sub </AMSCRIPT> --> <AMSCRIPT>Sub Main For ix=1 To ds_PriceList.TotalRows If ds_masterList.e = "" Then ds_shopFile.i = "9999" Else If ds_masterList.e > ds_priceList(ix).a Then ds_shopFile.i = Round((ds_priceList(ix).b*ds_masterList.e),2) Exit End If End If Next ix End Sub </AMSCRIPT> <!-- <AMLOOP TYPE="DATASET" DATASET="ds_priceList"> --> <!-- <AMIF EXPRESSION="%ds_priceList.a% = """> --> <!-- <AMSET VARIABLENAME="ds_shopFile.i">9999</AMSET> --> <!-- <AMBREAK /> --> <!-- </AMIF> --> <!-- <AMSET VARIABLENAME="tmp_2">%ds_priceList.a%</AMSET> --> <!-- <AMSET VARIABLENAME="tmp_4">%ds_priceList.b%</AMSET> --> <!-- <AMSET VARIABLENAME="tmp_3">%ds_masterList.e%</AMSET> --> <!-- <AMIF EXPRESSION="%tmp_3% >= %tmp_2%"> --> <!-- <AMSET VARIABLENAME="ds_shopFile.i">%round((tmp_3*tmp_4),0)%</AMSET> --> <!-- <AMBREAK /> --> <!-- </AMIF> --> <!-- </AMLOOP> --> <!-- v_products_quantity --> <AMSET VARIABLENAME="ds_shopFile.m">%ds_masterList.h%</AMSET> <AMIF EXPRESSION="%ds_shopFile.m% = """> <AMSET VARIABLENAME="ds_shopFile.m">0</AMSET> </AMIF> <!-- v_manufacturers_name --> <!-- <AMSET VARIABLENAME="chk_found">0</AMSET> --> <!-- <AMSTARTTIMER SESSION="vendorTime" /> --> <AMSCRIPT>Sub Main For ix=1 To ds_vendorList.TotalRows 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 </AMSCRIPT> <AMIF EXPRESSION="%ds_shopFile.n% = """> <AMSET VARIABLENAME="ds_shopFile.n">1800 Computers</AMSET> </AMIF> <!-- <AMLOOP TYPE="DATASET" DATASET="ds_vendorList"> --> <!-- <AMIF EXPRESSION="%ds_vendorList.a% = """> --> <!-- <AMSET VARIABLENAME="ds_shopFile.n">1800 Computers</AMSET> --> <!-- <AMBREAK /> --> <!-- </AMIF> --> <!-- <AMSET VARIABLENAME="tmp_5">%ds_vendorList.a%</AMSET> --> <!-- <AMSET VARIABLENAME="tmp_6">%ds_masterList.c%</AMSET> --> <!-- <AMIF EXPRESSION="%tmp_5% = %tmp_6%"> --> <!-- <AMSET VARIABLENAME="ds_shopFile.n">%ds_vendorList.b%</AMSET> --> <!-- <AMBREAK /> --> <!-- </AMIF> --> <!-- </AMLOOP> --> <!-- v_categories_name_1 & 2 --> <!-- <AMSTARTTIMER SESSION="categryTime" /> --> <AMSCRIPT>Sub Main For ix=1 To ds_categoryList.TotalRows temp_7 = ds_categoryList(ix).a temp_8 = ds_masterList.g If temp_7 = temp_8 Then ds_shopFile.o = ds_categoryList(ix).b ds_shopFile.p = ds_categoryList(ix).c GoTo NextFor End If NextFor: ' this is label Next ix End Sub </AMSCRIPT> <AMIF EXPRESSION="%ds_shopFile.o% = """> <AMSET VARIABLENAME="ds_shopFile.o">Others</AMSET> </AMIF> <AMIF EXPRESSION="ds_masterList.h = 0"> <AMSET VARIABLENAME="ds_shopFile.w" AM_DESCRIPTION=" Set variable ds_shopFile.w to value &quot;1&quot;">1</AMSET> <AMELSE /> <AMSET VARIABLENAME="ds_shopFile.w">0</AMSET> </AMIF> <!-- v_tax_class_title, v_status, v_metatags_products_name_status, v_metatags_title_status, v_metatags_model_status, v_metatags_price_status, v_metatags_title_tagline_status --> <!-- v_products_weight --> <AMSET VARIABLENAME="ds_shopFile.j">%ds_masterList.i%</AMSET> <AMSET VARIABLENAME="ds_shopFile.v" AM_DESCRIPTION=" Set variable ds_shopFile.v to value &quot;Taxable Goods&quot;">Taxable Goods</AMSET> <AMSET VARIABLENAME="ds_shopFile.x" AM_DESCRIPTION=" Set variable ds_shopFile.x to value &quot;0&quot;">0</AMSET> <AMSET VARIABLENAME="ds_shopFile.y" AM_DESCRIPTION=" Set variable ds_shopFile.y to value &quot;0&quot;">0</AMSET> <AMSET VARIABLENAME="ds_shopFile.z" AM_DESCRIPTION=" Set variable ds_shopFile.z to value &quot;0&quot;">0</AMSET> <AMSET VARIABLENAME="ds_shopFile.aa" AM_DESCRIPTION=" Set variable ds_shopFile.aa to value &quot;0&quot;">0</AMSET> <AMSET VARIABLENAME="ds_shopFile.ab" AM_DESCRIPTION=" Set variable ds_shopFile.ab to value &quot;0&quot;">0</AMSET> </AMLOOP> </AMIF> <!-- <AMSTOPTIMER RESULTVARIABLE="timer2" /> --> <!-- <AMMESSAGEBOX COUNTDOWNDELAY="1">Finished 1 Item in %timer2%</AMMESSAGEBOX> --> </AMLOOP> <AMEXCELDATASETTOCELLS SESSION="ss_shopFile" DATASET="ds_shopFile" CELLROW="1" CELLCOLUMN="1" /> <AMEXCELCLOSEWORKBOOK SESSION="ss_shopFile" /> <!-- Convert XLS to CSV --> <AMFILEDELETE SOURCE="%fld_ebusiness%\Shop File\%nm_supname%_SF.csv" AM_ONERROR="CONTINUE" /> <AMSCRIPT>'#Reference {00020813-0000-0000-C000-000000000046}#1.4#0#C:\Program Files\Microsoft Office\Office10\EXCEL.EXE#Microsoft Excel 10.0 Object Library Sub Main Dim objXlApp As New Excel.Application Dim objXlBook As Excel.Workbook Set objXlBook = Excel.Workbooks.Open( "C:\EBusiness\Shop File\AnywareComputers_SF.xls") objXlApp.DisplayAlerts = False objXlBook.SaveAs "C:\EBusiness\Shop File\AnywareComputers_SF.csv" , xlCSVWindows objXlBook.Close SaveChanges:=True objXlApp.Quit Set objXlBook = Nothing Set objXlApp = Nothing End Sub </AMSCRIPT>

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)
Sub Main For ix=1 To ds_PriceList.TotalRows If ds_priceList(ix).a = "" Then ds_ShopFile.i = "9999" Exit For End If temp_2 = ds_priceList(ix).a temp_3 = ds_priceList(ix).b If temp_3 > temp_2 Then ds_shopFile.i = Round((temp_3*temp_4),2) Exit For End If Next ix End Sub

-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4

---

Category Survey

Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
Sub Main tmp_4 = ds_masterList.e For ix=1 To ds_PriceList.TotalRows If ds_masterList.e = "" Then ds_ShopFile.i = "9999" Exit For End If tmp_2 = ds_priceList(ix).a tmp_3 = ds_priceList(ix).b If tmp_4 > tmp_2 Then ds_shopFile.i = Round((tmp_3*tmp_4),2) Exit For End If Next ix End Sub

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

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

	AnywareComputers_MLtmp.xls  (20 KB)
	AnywareComputers_PL.xls  (19 KB)

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

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)
Sub Main tmp_4 = ds_masterList.e For ix=1 To ds_PriceList.TotalRows If ds_masterList.e = "" Then ds_ShopFile.i = "9999" Exit For End If tmp_2 = ds_priceList(ix).a tmp_3 = ds_priceList(ix).b tmp_4 = ds_masterList.e If CDbl(tmp_4) >= CDbl(tmp_2) Then ds_shopFile.i = "" ds_shopFile.i = Round((CDbl(tmp_3)*CDbl(tmp_4)),2) Exit For End If Next ix End Sub

Eureka!

Category Survey

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

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

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

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

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

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