18 January 2026:


This forum is now archived and is in read-only mode. Please continue discussions on our improved new Sahi Pro Community forum.



Sahi Pro is an enterprise grade test automation platform which can automate web, mobile, API, windows and java based applications and SAP.

Data driven using Excel

kphapakdykphapakdy Members
edited November -1 in Sahi - Open Source
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

Comments

  • narayannarayan Administrators
    Hi Kai,

    Can you please specify what the problem is?
  • kphapakdykphapakdy Members
    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
  • WayneDWayneD Members
    edited June 2010
    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!
  • kphapakdykphapakdy Members
    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"]);

    }
  • WayneDWayneD Members
    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
  • narayannarayan Administrators
    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
  • kphapakdykphapakdy Members
    Wayne,

    Thanks so much!
  • pankaj.nithpankaj.nith Members
    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
  • narayannarayan Administrators
    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.nithpankaj.nith Members
    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
  • kphapakdykphapakdy Members
    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"]);
    }
  • dpdp Members
    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
  • narayannarayan Administrators
    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
  • kphapakdykphapakdy Members
    edited June 2010
    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
  • kphapakdykphapakdy Members
    dp,

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

    Thanks,
    Kai
  • kphapakdykphapakdy Members
    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)
  • narayannarayan Administrators
    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
  • kphapakdykphapakdy Members
    edited June 2010
    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
This discussion has been closed.