Not able to conenct to execl to get data

madhukar

Hi,

I am new to Sahi, i am trying to connect to execl to get data. I have used following code to connect to excel and get the data.

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=E:\\sahi_pro\\userdata\\scripts\\Testdata\\testdata.xls.xlsx;readOnly=false", "", "");
$rs = db.select("select * from [Sheet1$]");


when i execute this, script failed, got the following error.

exception: java.sql.SQLException: [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator. 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: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:247) at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:3330) at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:2487) at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:164) at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398) at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065) at org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:175) at org.mozilla.javascript.Context.evaluateString(Context.java:1104) at net.sf.sahi.rhino.RhinoScriptRunner.run(RhinoScriptRunner.java:144) at java.lang.Thread.run(Unknown Source)

Please help me out in conencting to execl to get data.

Sumitra

Hi madukar,

Can you please check the excel sheet file extension, it should be testdata.xls or testdata.xlsx.

Also, have a look at below URLs:

http://sahi.co.in/forums/viewtopic.php?id=1682

http://sahi.co.in/forums/viewtopic.php?id=2451

http://sahi.co.in/forums/viewtopic.php?id=1840

What version of Java are you using?

Regards
Sumitra

madhukar

Hi Sumitra,

Thank you very much reply.

i am using testdata.xls only, it was copy paste problem.

i am using Java version "1.6.0_26" and Windows XP

If i save my xls document as 1997-2003 work book, while executing scripts fails, but logs contains nothing.

Sumitra

Hi madhukar,

Most probably 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$]");

Also, kindly have a look at the above mentioned links which i have provided and check if it solves your issue.

Regards
Sumitra

spark164

Hi Sumitra,

Using Sahi v3.5
I am facing same problem as mentioned by Madhukar.

I am using
OS : Win 7 x64,
MS Office : 2010 x64,
Java : 1.7.0_25 x64

The script was working fine on WinXP x32, Office2010 x32, Java 1.7 x32

But now after upgrading the system it is giving following error, similar to what Madhukar mentioned few years back.

---

C:\Progra~1\Intern~1\iexplore.exe -noframemerging http://sahi.example.com/_s_/dyn/Driver_initialized
java.sql.SQLException: [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.
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:247)
at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:3330)
at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:2487)
at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:164)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)
at org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:175)
at org.mozilla.javascript.Context.evaluateString(Context.java:1104)
at net.sf.sahi.rhino.RhinoScriptRunner.run(RhinoScriptRunner.java:114)
at java.lang.Thread.run(Unknown Source)

---

As I red some where in blog.
I recreated the excel and tried with OLD and NEWly created xlsx file, but same error on sahi console and in log.

Please help.

Regards,
Anand