Data driven using Excel

kphapakdy

Hi. I'm new to Sahi. I was trying to do data driven testing by reading it from Excel and am having problem.

My Excel sheet is Discover.xls. It has one column heading (TL_Measure) with 3 values.

TL_Measure (column heading)
call count
test
agent

I want the script to loop through and select each value under TL_Measure until there's no more rows left. Please see my script below:


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


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

for (var $i=0; $i<4; $i++)

}

_setSelected(_select("SelectMeasure_Chart_1"), $rs[0]["TL_Measure"]);

{


Thank you,
Kai

narayan

Hi Kai,

Can you please specify what the problem is?

kphapakdy

Narayan,

When I tried to playback, I get this error:

JSERROR
syntax error

Is my script setup correctly for doing data driven testing using Excel?

Thank you,
Kai

WayneD

Kai,

I noticed in your for look that you use

for (var $i=0; $i<4; $i++)

}

_setSelected(_select("SelectMeasure_Chart_1"), $rs[0]["TL_Measure"]);

{


Instead of leaving $rs[0], you need to increment it. In your case it would be i.

Also, your brackets are reversed for the for statement. Notice the bold below:

for (var $i=0; $i<4; $i++)

{

_setSelected(_select("SelectMeasure_Chart_1"), $rs[$i]["TL_Measure"]);

}



Good luck!

kphapakdy

Wayne,

Thanks so much for your suggestion. I have updated my script according to your suggestion and get a more meaningful error: JSERROR
SyntaxError: missing ] after element list at Jun 21, 2010 5:06:05 PM


// Get the DB instance

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



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


for (var $i=0; $i<4; $i++)

{

_setSelected(_select("SelectMeasure_Chart_1"), $rs[$i]["TL_Measure"]);

}

WayneD

Glad to hear it. I wish I could be more help but I have only started using the program last week myself. I'm trying to do about the same thing as you are though, so I will report back if I figure something out.

Take care

narayan

Hi kphapakdy,

The problem is most probably that the name of the sheet inside your excel is not Sheet1.
I think Sahi would have thrown some exception on the Sahi console. Check what the sheet name is and use that. For example if your sheet is called "sample", you would use

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

Hi Wayne, keep us posted of your experiences.

Regards,
Narayan

kphapakdy

Wayne,

Thanks so much!

pankaj.nith

Hi,

The code below works on IE:

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

//Run a select
var $rs = [];
$rs[0] = [];
$rs = db.select("select * from [Sheet1$]");

for (var $i=0; $i<$rs.length; $i++){
    _alert("" + $rs[$i]["TL_Measure"]);
}

I am getting some odd errors on Firefox though.

Regards,
Pankaj Kumar

narayan

Pankaj,

Except for _alert, the whole script executes on the proxy. It would be surprising if this worked on IE and not on Firefox. As I mentioned earlier, the most likely reason for failure is an incorrect sheet name, which will be confirmed if you see any exceptions on the Sahi console.

pankaj.nith

Hi Narayan,

Thanks again. I downloaded the latest build of sahi and the same code is now working fine on Firefox as well. Must be my mistake somewhere in the code.

Regards,
Pankaj

kphapakdy

Naranjan/Pankaj,

I've used Pankaj's suggestion and still get the same error:SyntaxError: missing ] after element list . Yes, the worksheet is named "Sheet1". I renamed it to "Data" and replaced "Sheet1" with "Data".

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

//Run a select
var $rs = [];
$rs[0] = [];
$rs = db.select("select * from [Data$]");

for (var $i=0; $i<$rs.length; $i++){
_alert("" + $rs[$i]["TL_Measure"]);
}

dp

Hi kphapakdy,

I could run the above script successfully.

Given below are my environment details:
Windows XP, Firefox 3.0.19, Java 1.6.0_12, Sahi V3 build 2010-06-11

Please check if there are some significant difference between mine and yours

Cheers
Dp

narayan

Hi guys,

There is one more forum post with the same problem. http://sahi.co.in/forums/viewtopic.php?id=1650

I suspect that it may have to do with the Excel data.

Kai, could you post the data in your Excel sheet? Or email it to support@sahi.co.in

Regards,
Narayan

kphapakdy

Narayan,

My spreadsheet is named "Discover.xls". It contains 2 column names, namely LT_Measure and LT_Compare. There are three rows of data. The worksheet name is "Data".

LT_Measure LT_Compare
% of Calls None
Call Count Agent
Agitation Agent Group

kphapakdy

dp,

My environment specs: Windows XP Pro, IE 8, Java Build 1.6.0_20-b02)

Thanks,
Kai

kphapakdy

The error in the console that I got is displayed below even though I tried playing backt the script in Post #12 above.


java.sql.SQLException: [Microsoft][ODBC Excel Driver] The Microsoft Jet database
engine could not find the object 'Data$'. Make sure the object exists and that
you spell its name and the path name correctly.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)
at net.sf.sahi.plugin.DBClient.getResult(DBClient.java:101)
at net.sf.sahi.plugin.DBClient.select(DBClient.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:24
7)
at org.mozilla.javascript.optimizer.OptRuntime.callN(OptRuntime.java:86)

at org.mozilla.javascript.gen.c35._c56(<cmd>:462)
at org.mozilla.javascript.gen.c35.call(<cmd>)
at org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime.java:66)

at org.mozilla.javascript.gen.c36._c0(<cmd>:28)
at org.mozilla.javascript.gen.c36.call(<cmd>)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:3
98)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:306
5)
at org.mozilla.javascript.gen.c36.call(<cmd>)
at org.mozilla.javascript.gen.c36.exec(<cmd>)
at org.mozilla.javascript.Context.evaluateString(Context.java:1104)
at net.sf.sahi.rhino.RhinoScriptRunner.run(RhinoScriptRunner.java:93)
at java.lang.Thread.run(Unknown Source)

narayan

Hi everyone,

I could reproduce this error with the file the Kai emailed us. There seems to be some problem in ODBC bridge when used on xlsx files. I saved the file as xls format and things started working correctly. Surprising thing is that I think I remember xlsx working before. Anyway the solution seems to be to save the file as xls (Excel 97-2003 Workbook) format.

We are also making the exception messages be printed in the logs, so that it is easier to debug. Should be available in the next build.

Regards,
Narayan

kphapakdy

Narayan, Wayne D, dp, pankaj.nith:

Thanks so much for everyone's help. I saved my xlsx to xls (Excel 97-2003 Workbook) format and it now works! Please see below my script for data driven testing from Excel.

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

//Run a select

var $rs = [];
$rs[0] = [];

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

for (var $i=0; $i<$rs.length; $i++)

{
_setSelected(_select("SelectMeasure_Chart_1"), $rs[$i]["TL_Measure"]);
}


spreadsheet name: Discover.xls
worksheet: Data
Below is the column heading and data that I had used:


TL_Measure (column heading)
Agitation
Call Duration
Hold Time
Word Count