18 January 2026:
Sahi Pro is an enterprise grade test automation platform which can automate web, mobile, API, windows and java based applications and SAP.
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.
How to add data from page to excel using update method
aspatel80
Members
Hi Team,
I want to add data present in the current form in excel sheet for which i have created connection string. The code is as follows:
var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=e:/testbed/Matter_Data.xls;readOnly=false","","");
var db2 = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=e:/testbed/Created_Matters.xls;readOnly=false","","");
//***Some piece of code****
var $rs=db.select("Select * from [Sheet1$]");
var i;
var $mcode;
for(i=0;i<2;i++)
{
//***Some piece of code****
_set($mcode, _textbox("id of textbox").value);
db2.update("insert into [Sheet1$] values ($mcode,'','','','','','','','')");
}
Here im trying to create two connection for two diffrent excel file. From first, im taking values already present in the file and creating an item. but before creating next item, i save first detail of the form in another excel file. For this i take the value of the textbox in a variable and then put it im my insert query. My problem is that im not able to add $mcode variable in insert query within update method. The line "db2.update("insert into [Sheet1$] values ($mcode,'','','','','','','','')");" actually doesnt excute and bypasses.
Please help me how to adding variable value in my query.
Thank You.
I want to add data present in the current form in excel sheet for which i have created connection string. The code is as follows:
var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=e:/testbed/Matter_Data.xls;readOnly=false","","");
var db2 = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=e:/testbed/Created_Matters.xls;readOnly=false","","");
//***Some piece of code****
var $rs=db.select("Select * from [Sheet1$]");
var i;
var $mcode;
for(i=0;i<2;i++)
{
//***Some piece of code****
_set($mcode, _textbox("id of textbox").value);
db2.update("insert into [Sheet1$] values ($mcode,'','','','','','','','')");
}
Here im trying to create two connection for two diffrent excel file. From first, im taking values already present in the file and creating an item. but before creating next item, i save first detail of the form in another excel file. For this i take the value of the textbox in a variable and then put it im my insert query. My problem is that im not able to add $mcode variable in insert query within update method. The line "db2.update("insert into [Sheet1$] values ($mcode,'','','','','','','','')");" actually doesnt excute and bypasses.
Please help me how to adding variable value in my query.
Thank You.
Comments
Replace your update query with this one and try once if it works:
db2.update("insert into [Sheet1$] values (" + $mcode + ",'','','','','','','','')");
Regards,
Pankaj.
Tried your suggestion, but there was error saying "missing ) after argument list". May be "" doesnt work as i already have it when writing the query.
Your suggestion worked fine. Very sorry for the previous post, there was mistake in my code. Thank You.
Im having problem in inserting data into my excel file. I used the statement which worked fine that day, but after that i couldnt use it again. Rather, SAHI gave me exception of specifying very few parameters!!!! The exception is as follows:
java.sql.SQLException: [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.
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.executeUpdate(Unknown Source)
at net.sf.sahi.plugin.DBClient.execute(DBClient.java:60)
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%
Can you describe your excel sheet. I mean how many named columns are there in your excel sheet? The number of columns should match that you are using in your insert query.
Regards,
Pankaj.
I have 13 columns in my excel shee present in Sheet1 tab/table. I want to save data in first column of the sheet.
I have found the problem. Actually we need to specify '' also. Correct statement is as follows:
db2.update("insert into [Sheet1$] values (' "+ $mcode + " ')");
Where $mcode represents any variable value.
Keep posting!
I need to access excel 2007 file whose extension is xlsx. Making changes to the driver & url for xls didnt helped me in any way, rather gave exception for driver.
Please suggest me driver and url for excel 2007.