xlsql - reading data from excel file

saini.007

Hi,

Can you please send me one exmaple of using xlsql to fetching data from an excel file into my script using xlsql library.

Also can you please let me know the installation instructions to successfully install xlsql.


Regards,

Rajesh Saini.

saini.007

For this script:
var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:/sahi/Data/Users.xls","","");
var $rs = [];
_set($rs, db.select("select * from Sheet1"));
_setValue(_textbox("userName"), $rs[0][0]);
_setValue(_password("password"), $rs[0][1]);
_click(_imageSubmitButton("Login"));
_assertExists(_byId("WebPartManager1_gwpTimeSheet1_TimeSheet1_lblShift"));
_assertContainsText("Current Shift : MORNING",_byId("WebPartManager1_gwpTimeSheet1_TimeSheet1_lblShift"));

_click(_link("Logout"));


Getting error:
Driver: sun.jdbc.odbc.JdbcOdbcDriver
JDBC URL: jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:/sahi/Data/Use
rs.xls
Username:
Password:
SQL: select * from Sheet1
java.sql.SQLException: [Microsoft][ODBC Excel Driver] External table is not in t
he expected format.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:3
23)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at net.sf.sahi.plugin.DBClient.getResult(DBClient.java:67)
at net.sf.sahi.plugin.DBClient.select(DBClient.java:56)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at net.sf.sahi.command.CommandExecuter.execute(CommandExecuter.java:53)
at net.sf.sahi.LocalRequestProcessor.getLocalResponse(LocalRequestProces
sor.java:42)
at net.sf.sahi.ProxyProcessor.processLocally(ProxyProcessor.java:199)
at net.sf.sahi.ProxyProcessor.run(ProxyProcessor.java:71)
at java.lang.Thread.run(Thread.java:619)

saini.007

Hi,

We are evaluating Sahi to be used in our project.

But we need our scripts to be data driven using excel & with this error we are unable demo the same to our client.

Can you check why th error "java.sql.SQLException: [Microsoft][ODBC Excel Driver] External table is not in t
he expected format." comes.

narayan

Saini,

Looks like the issue is with xlsql and the excel file rather than on Sahi's side. I say this because Sahi seems to find the driver and invoke it. The error is being thrown by the driver. There is only one thing which comes to my mind right now: replace /sahi/Data/Users.xls with \\sahi\\Data\\Users.xls and try. Let us know what you find.

saini.007

Thank you,

1.) Instead of xlsql i am using the standard jdbc odbc driver from Sun Microsystem. (which is in rt.Jar in its JRE directory), so this makes me feel that driver should be fine.
2.) On my system we have both Ms Office Professional 2003 & Ms Office Standard 2007, but i have saved the file as .xls instead of .xlsx extension so that it takes as per MS-Office 2003 version.
3.) I changed /sahi/Data/Users.xls with \\sahi\\Data\\Users.xls

Although in the Sahi Server command prompt i get the same above error.
In addition to that i also get the below error in the results report, which means the $rs is not getting result of the query. (:) which is beacause it seems it does not recognizes properly the excel file.

Error loading script. Firefox may point to the exact line. TypeError: '0.0' is null or not an object
No trace available
Error loading script. Firefox may point to the exact line. TypeError: '0.0' is null or not an object
No trace available
Error loading script. Firefox may point to the exact line. TypeError: '0.0' is null or not an object
No trace available
Error loading script. Firefox may point to the exact line. TypeError: '0.0' is null or not an object
No trace available

Any ideas now if you re-think in the above case.

I also tried access driver & its database, in which it was able to successfully pick value from the table( as it shows in the script running area), but it was unable to set value on the login page and still gave the same above error.

narayan

Saini, in order to check, would it be possible for you to access the same file using simple java and your drivers? Sahi just uses standard jdbc and whatever driver you give it to access the db.

The columns need to be accessed by column name. example $rs[0]["CustomerName"]. I am not sure this makes it convenient or inconvenient so let us know.

Btw you can change the first 3 lines to this:

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:/sahi/Data/Users.xls","","");
var $rs =  db.select("select * from Sheet1");

sumeet

When I use the above lines to connect to Excel as DB, it gives me below exception -

Note - 'Master1' is the worksheet name
============================================================

java.sql.SQLException: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Master1'. 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:105)
at net.sf.sahi.plugin.DBClient.select(DBClient.java:83)
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:142)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:201)
at org.mozilla.javascript.optimizer.OptRuntime.callN(OptRuntime.java:84)
at org.mozilla.javascript.gen.c1._c12(<cmd>:69)
at org.mozilla.javascript.gen.c1.call(<cmd>)
at org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime.java:64)
at org.mozilla.javascript.gen.c3._c0(<cmd>:134)
at org.mozilla.javascript.gen.c3.call(<cmd>)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:337)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:2755)
at org.mozilla.javascript.gen.c3.call(<cmd>)
at org.mozilla.javascript.gen.c3.exec(<cmd>)
at org.mozilla.javascript.Context.evaluateString(Context.java:1144)
at net.sf.sahi.rhino.ScriptRunner.run(ScriptRunner.java:52)
at java.lang.Thread.run(Unknown Source)

============================================================

sumeet

Solution to above error is when you pass the sheet name in select query you will have to pass it like below -

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