Updating value back to Excel

ashav

Hi,

Wanted to update values to an excel during the running of the sahi script. Read in help file that updates to excel are possible just like selects.
Need help with a sample update code that can be run on the excel.Something like this.
Update [$Sheet1]
set Column2='RequiredValue'
where Column1 = 'FilterValue'

Regards
Asha

narayan

Asha,

If you are using the jdbc:odbc driver, make sure you add readOnly=false at the end of the connection string.

For example:
Given a excel sheet "D:\\testbed\\sahi_sample2.xls", with the following data:

Name    Age    Sex
Raju          20    Male
Ramu        30    Male
Sheela       25    Female

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\\testbed\\sahi_sample2.xls;readOnly=false","","");
$rs = db.select("select * from [Sheet1$]");
_assertEqual("Raju", $rs[0]["Name"]);
_assertEqual(20, parseInt($rs[0]["Age"]));
db.update("update [Sheet1$] set Age=21 where Name='Raju'");
$rs = db.select("select * from [Sheet1$]");
_assertEqual(21, parseInt($rs[0]["Age"]));

Note the readOnly=false in the connection string on the first line.

Regards,
Narayan

ashav

Thanks Narayan. This worked. Can you also let me know delete syntax.
I am trying something like this...

$procdata = $proc.update("delete from [Sheet1$]");

with readOnly = false
But this doesnt seem to be working.

Regards
Asha