64k limit, need a workaround

fury

First this is a great application, thanks!

Here's my script:

_click(_link("Duplicate"));
_setSelected(_select("portlet_17_1wlw-select_key:{actionForm.numAccounts}"), "1");
_click(_submit("Go"));
_setValue(_textbox("portlet_17_1{pageFlow.duplicateList[0].url}"), "somesitedotcom");
_click(_submit("Done"));

I have a spreadsheet of roughly 600 URLs to be processed by this script. Using the CONCATENATE function in Excel I was able to create a .sah file containing the script to execute this for all the URLs, however when I try and run it an error tells me the file is too large (exceeds 64k) or something to that effect. I'm sure there's a way to grab the values from the Excel sheet instead of manually creating the script, but I'm not sure how to do so, and this script works with smaller numbers of URLs (say, 25). How do I go about automating the script to 'communicate' with the Excel sheet instead of manually creating a script that's apparently too large?

narayan

Hi Fury,

Have a look at http://sahi.co.in/w/data-driven-testing

Let us know if you are stuck somewhere.

Regards,
Narayan

fury

Hi Narayan,

Thanks for the information. I read through the corresponding pages, however I'm failing to understand how the examples directly correspond to my situation. Getting sahi to 'communicate' with the Excel file is straightforward using var db = _getDB, however I'm not clear as to what to do next. How do I get the URLs listed in the file to propagate into my function automatically?

For example, if I have a single column Excel file with the word URL in cell A1, somesite2.com in A2, somesite3.com in A3, and somesite4.com in A4, how do I tell sahi to incorporate those values into the script mentioned in my first post? Please keep in mind I did create the script manually already using the CONCATENATE function and sahi won't run because the .sah file is too large.

I'm sure this is easier than I'm making it, and any additional help or examples is appreciated.

narayan

Hi Fury,

Sahi will consider the first line of Excel as a header. So give it a name like URL

So your Excel now looks like:

---
URL
http://site1.example.com
http://site2.example.com
---

Move your steps into a function and allow it to take a parameter for the URL. Then invoke your function in a loop.

function performStep($url){
  _click(_link("Duplicate"));
  _setSelected(_select("portlet_17_1wlw-select_key:{actionForm.numAccounts}"), "1");
  _click(_submit("Go"));
  _setValue(_textbox("portlet_17_1{pageFlow.duplicateList[0].url}"), $url);
  _click(_submit("Done"));
}


var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", 
"jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\\temp\\sample.xls;readOnly=false", 
"", 
"");

$rs = db.select("select * from [Sheet1$]");

for (var $i=0; $i < $rs.length; $i++){
  var $row = $rs[$i];
  var $url = $row[0]; // or $row["URL"]
  performStep($url);
}

Hope that helps.
Regards,
Narayan

narayan

You could also move the excel read part into a reusable function like this:

function readExcelFile($filePath, $sheetName){
    var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", 
            "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + $filePath + ";readOnly=false", 
            "", 
            "");
    if (!$sheetName) $sheetName = "Sheet1";
    return $db.select("select * from [" + $sheetName + "$]");    
}

var $rs = readExcelFile ("D:\\temp\\sample.xls");

fury

Hi Narayan,

The first suggestion worked, thanks!

I am now trying a script with more steps, however I get an error stating "JSERROR missing ) after argument list" each attempt. The concept is the same, I'm using an Excel sheet with only one column (although with 17000 rows instead of 3 this time). I kept the cell A1 the same as your other example (URL) and the path to the Excel sheet is correct. Any suggestions?

function performStep($url){
_setValue(_textbox("accountSearchPortlet_1{actionForm.accountId}"), "$url");
_click(_submit("GO"));
_click(_link("VIS"));
_click(_link("Edit[2]"));
_setValue(_textbox("portlet_8_2{actionForm.homeURL}"), "something dot com");
_setValue(_textbox("portlet_8_2{actionForm.domain}"), "something dot com");
_setSelected(_select("portlet_8_2wlw-select_key:{actionForm.baseUser}"), "40");
_click(_checkbox("portlet_8_2wlw-checkbox_key:{actionForm.cP}"));
_setValue(_textbox("portlet_8_2{actionForm.h}"), "/h.htm");
_setSelected(_select("portlet_8_2wlw-select_key:{actionForm.S}"), "E");
_click(_checkbox("portlet_8_2wlw-checkbox_key:{actionForm.offersA}"));
_click(_checkbox("portlet_8_2wlw-checkbox_key:{actionForm.offersC}"));
_click(_checkbox("portlet_8_2wlw-checkbox_key:{actionForm.offersG}"));
_click(_checkbox("portlet_8_2wlw-checkbox_key:{actionForm.A}"));
_click(_checkbox("portlet_8_2wlw-checkbox_key:{actionForm.emailHasCE}"));
_setValue(_textbox("portlet_8_2{actionForm.emailCE}"), "something at something dot com");
_setValue(_textarea("portlet_8_2{actionForm.emailCT}"), "Thank you");
_click(_submit("Done"));
_click(_link("Vanity P N"));
_click(_link("Edit[2]"));
_click(_checkbox("integrationSetupVanity_2wlw-checkbox_key:{actionForm.enableH}"));
_click(_checkbox("integrationSetupVanity_2wlw-checkbox_key:{actionForm.enableC}"));
_click(_checkbox("integrationSetupVanity_2wlw-checkbox_key:{actionForm.enableE}"));
_click(_submit("Done"));
_click(_link("Account Look-up"));

}


var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", 
"jdbc:odbc:Driver={Microsoft Excel Driver 

(*.xls)};DBQ=C:\\sahi\\userdata\\scripts\\264561Fix.xls;readOnly=false", 
"", 
"");

$rs = db.select("select * from [Sheet1$]");

for (var $i=0; $i < $rs.length; $i++){
  var $row = $rs[$i];
  var $url = $row[0]; // or $row["URL"]
  performStep($url);
}

narayan

Hi Fury

_setValue(_textbox("accountSearchPortlet_1{actionForm.accountId}"), "$url");
should be
_setValue(_textbox("accountSearchPortlet_1{actionForm.accountId}"), $url);

and

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", 
"jdbc:odbc:Driver={Microsoft Excel Driver 

(*.xls)};DBQ=C:\\sahi\\userdata\\scripts\\264561Fix.xls;readOnly=false", 
"", 
"");

should be 

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", 
"jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\\sahi\\userdata\\scripts\\264561Fix.xls;readOnly=false", 
"", 
"");

Hope that helps.

fury

Almost there! The values in my spreadsheet are numbers, so for example I have a column of:

1
2
3

When the numbers are copied into my web form, they are done so as follows:

1.0
2.0
3.0

The .0 screws up the process. I have tried adding a ' as in '1 but then sahi reads it as null. Any suggestions?

narayan

Do a parseInt() to get the integer values.

eg.

_setValue(_textbox("count"), parseInt($i));

fury

No luck, NaN gets pasted into the field. I have tried with cell A2 as only a number and the number preceded by an apostrophe.

fury

Got it working, was some sort of problem with cell A2, thanks again!