Getting error when trying to fetch data from Excel sheet

pranoti

Hello,
I want to fetch data from exel sheet and use in my application. I have Download and install AccessDatabaseEngine_x64.exe which is saved in C:\Program Files\Microsoft Office and my sahi is in C: I have written following code so data is fetch from excel file and used .
I have sahi Version : V3.5 Build : 2011-07-19, Microsoft excel starter 2010, window 7 , FF 12
_navigateTo("http://172.16.2.54:8080/erp/login.hst";
var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xlsx)};DBQ=C:\\Users\\pranoti.dutta\\sahi\\userdata\\scripts\\try.xlsx","",""); var $rs = []; $rs = $db.select("select * from [Sheet1$]"); var $length1 = $rs.length; for (var $i = 0; $i < $rs.length; $i++) { _setValue(_textbox("j_userName"), $rs[$i]["NAME"]); _setValue(_password("j_password"),$rs[$i]["PASSWORD"]);
_click(_submit("Login")); _click(_link("Log Out"));
_click(_link("Click here")); }
but I am getting following error log:
ERROR
exception: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)

Can u help for how to fetch data from excel sheet .

Pratik Anand Shah

Hi Pranoti,


You haven't write readOnly=false in the connection string on the first line. This is required if you wish to update the Excel sheet.

Just change the connection string from

var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver",
"jdbc:odbc:Driver={Microsoft Excel Driver (*.xlsx)};
DBQ=C:\\Users\\pranoti.dutta\\sahi\\userdata\\scripts\\try.xlsx",
"","");

to

// Get the DB instance
var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", 
"jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\\Users\\pranoti.dutta\\sahi\\userdata\\scripts\\try.xlsx;readOnly=false", 
"", 
"");

Regards,
Pratik Shah

pranoti

Thanks Pratik ,
Its working fine now..

skumar

Hello,

I also see the same issue in my end,
var $dbConn = _getDB("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + $workbookLocation + "/" + $workbookName + ";readOnly=false", "", "");

I had the readOnly=false in the connection URL, still facing the issue.

I am using Win-7 64 bit machine, but all the 32 bit office products are installed, so I installed the AccessDatabaseEngine.exe.

And also verified that, having 32 bit JVM.

any help please?

Thanks.

I see below error,

ERROR [2 ms] [03:47:04.033]
exception: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.sf.sahi.plugin.DBClient.getResult(DBClient.java:131)
at net.sf.sahi.plugin.DBClient.select(DBClient.java:105)
at net.sf.sahi.plugin.DBClient.select(DBClient.java:99)
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:126)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:225)
at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1473)
at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:815)
at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:109)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:394)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3091)
at org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:120)
at org.mozilla.javascript.Context.evaluateString(Context.java:1079)
at net.sf.sahi.rhino.RhinoScriptRunner.run(RhinoScriptRunner.java:172)
at java.lang.Thread.run(Unknown Source)