Comments

• pankaj.nith Members

  December 2008 edited December 2008

  Hi,
  
  I havnt worked with excel drivers but try changing code to this:

  var $rs = [];
  $rs[0] = [];
  _set($rs, db.select("select * from [Sheet1$]"));
  _set($datap, $rs[$i]["datap"]);//hope you are setting this $i (index) somewhere
  

  Regards,
  Pankaj.
  PS: Try explicitly declaring the variables as arrays when thay are supposed to be arrays eg $datap in this case and declare it as two dimesnsional array just like $rs.
• KMANDO Members

  December 2008

  Thanks for the quick response.
  
  
  to your comment above: I do not know where to set the $i (index) , that is pretty much my script right there, well before all of the clicks and such.
  
  
  after implementation of your suggestion i get the following error:
  
  _setGlobal('\$rs', db.select("select * from [Sheet1$]")); ReferenceError: db is not defined
• KMANDO Members

  December 2008

  here is my updated code:
  

  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver 
  
  (*.xls)};DBQ=C:/data1.xls","","");
  
  var $rs = [];
  $rs[0] = [];
  _set($rs, db.select("select * from [Sheet1$]"));
  _set($datap, =($rs[$i]["datap"]);//hope you are setting this $i (index) somewhere
  
  len=100
  for(var $i=0;$i<=1;$i++)
  {
  _setValue(_textbox("Seed"), $datap);
  
  
  
  
  
  _click(_submit("Hit Me"));
  
  _wait(3000);
  
• pankaj.nith Members

  December 2008

  Ok.
  Just for test purposes try running this code:

  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver 
  
  (*.xls)};DBQ=C:/data1.xls","","");
  
  var $rs = [];
  $rs[0] = [];
  _set($rs, db.select("select * from [Sheet1$]"));
  _setValue(_textbox("Seed"), $rs[0]["datap"]);//"datap" should be the heading of the column from where the value is retrieved and not the value itself
  _click(_submit("Hit Me"));
  _wait(3000);
  

  Regards,
  Pankaj.
• KMANDO Members

  December 2008

  Hi,
  
  I see that you have removed the loop to iterate the many columsn of information,
  
  with the suggested changes, it seems I am now getting the following error:
  JSERROR unterminated string literal
  
  
  nothing is highlighted red in the log though.
• thakurpravin Members

  December 2008

  hi KMANDO,
  
  I think error in your code is around :-
  var $datap $rs[$i]["datap"]); // IT SHOULD BE NAME OF THE HEADER IN YOUR EXCEL SHEET.
  
  Lets discuss in this way, suppose your data is stored in your excel sheet "data1.xls" in this way
  
  name|age|sex //This is the table header in ur excel sheet
  jack|20|male
  
  Now use code like:-
  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:/sahi/scripts/test2.xls","","");
  
  var $rs = db.select("select * from [Sheet1$]");
  var $data1 =($rs[$i]["name"]); //name of the column
  var $data2 =($rs[$i]["age"]); //name of the column
  var $data3 =($rs[$i]["sex"]); //name of the column
  
  hope it will help you. I am able to fetch the data using excel in the same fashion.
• KMANDO Members

  December 2008

  ok, so now its getting the values form the excel sheet, just isnt following through, as it will not set the values into the textbox
  here is hte error :
  
  _setValue(_textbox("Seed"), "software solution");// TypeError: el is null
  
  
  
  here is my code so far:
  
  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver
  
  (*.xls)};DBQ=C:/data1.xls","","");
  
  var $rs = db.select("select * from [Sheet1$]");
  var $data1 =($rs[0]["name"]); //name of the column
  
  
  _setValue(_textbox("Seed"), $rs[0]["name"]);
  
  
  
  _click(_submit("Hit Me"));
  
  _wait(3000);
  
  
  
  
  
  
  
  It seems to be the "_setValue(_textbox("Seed"), $rs[0]["name"]);" line which is causing the problem
  any suggestions?
• KMANDO Members

  December 2008

  Great seems to be working perfectly !!
  
  thanks thakurpravin and pankaj.nith for putting up with me !
  
  now one last thing to complete this would be to get it to iterate through the list of values in the excel sheet until it finishes
• thakurpravin Members

  December 2008

  even iteration is an easy job.
  
  
  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:/sahi/scripts/test2.xls","","");
  
  var $rs = db.select("select * from [Sheet1$]");
  var $data1 =($rs[$i]["data1"]);
  var $data2 =($rs[$i]["data2"]);
  
  len=100
  for(var $i=0;$i<1;$i++)
  {
  _setValue(_textbox("eventCode"), $data1);
  _setValue(_textbox("externalEventCode"), $data2);
  }
  _debugToFile(($rs[$i]["data1"])++($rs[$i]["data2"])+, "D:\\values.csv");
  
  Try to take help from script above, it will fetch all values available in excel sheet. Hope, it will help you.
• pankaj.nith Members

  December 2008 edited December 2008

  Ya, i just wanted to check where the error is. Can you post the error and your script, if possible.
  But the the $i set in the for loop is not used anywhere in the loop itself. I think you will have to rectify the loop first.
• KMANDO Members

  December 2008 edited December 2008

  Hi,
  
  thanks for the aid so far, the code below starts off at google.ca and at the end of it it is made sure it returns back to iterate again, but the iteration does not kick in using the second value from the excel sheet.
  
  do you see anything in the below code that I may be missing?
  
  thanks again !
  
  
  

  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver 
  
  (*.xls)};DBQ=C:/data1.xls","","");
  
  var $rs =  db.select("select * from [Sheet1$]");
  var $data1 =($rs[0]["name"]); //name of the column
  
  
  len=100
  for(var $i=0;$i<1;$i++)
  {
  
  _setValue(_textbox("seed"), $rs[0]["name"]);
  _click(_submit("submit"));
  _wait(3000);
  _click(_image(1));
  _wait(2000);
  
  
  _debugToFile(_getText(_cell(_table(0), 1, 2)), "C:\\textfile2.xls");
  _debugToFile(_getText(_cell(_table(0), 2, 2)), "C:\\textfile2.xls");
  
  _navigateTo("http://www.google.ca");
  _debugToFile(($rs[$i]["name"])+[',']+($rs[$i]["data2"])+[','], "C:\\values.csv");
  
  }
  
• pankaj.nith Members

  December 2008

  Try this:

  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver 
  
  (*.xls)};DBQ=C:/data1.xls","","");
  
  var $rs = [];
  $rs[0] = [];
  _set($rs,  db.select("select * from [Sheet1$]"));
  //var $data1 =($rs[0]["name"]); //name of the column
  
  
  var $len=100; //I don't know what you are using this, but i hope you need this for iterating through
  for(var $i=0;$i<$len;$i++)
  {
  
  _setValue(_textbox("seed"), $rs[$i]["name"]);
  _click(_submit("submit"));
  _wait(3000);
  _click(_image(1));
  _wait(2000);
  
  
  _debugToFile(_getText(_cell(_table(0), 1, 2)), "C:\\textfile2.xls");
  _debugToFile(_getText(_cell(_table(0), 2, 2)), "C:\\textfile2.xls");
  
  _navigateTo("http://www.google.ca");
  _debugToFile(($rs[$i]["name"])+[',']+($rs[$i]["data2"])+[','], "C:\\values.csv");
  
  }
  
• KMANDO Members

  December 2008 edited December 2008

  pankaj.nith - with your code it seems that I am getting a db connection error,
  
  _setGlobal('\$rs', db.select("select * from [Sheet1$]")); ReferenceError: db is not defined
  
  
  thakurpravin - your code seems to be working correctly with the excel sheet, but not looping through to repeat test with the rest of the variables in the sheet.
• KMANDO Members

  December 2008 edited December 2008

  OK , so far the script cycles through and loops, but still does not obtain the second value in the excel sheet upon iterating the second time.
  
  so far the script:

  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel 
  
  Driver (*.xls)};DBQ=C:/data1.xls","","");
  
  var $rs =  db.select("select * from [Sheet1$]");
  var $Uname =($rs[0]["Uname"]); //name of the column
  
  var $len=100;
  for(var $i=0;$i<$len;$i++)
  {
  
  _setValue(_textbox("seed"), $Uname);
  _click(_submit("submit"));
  _wait(3000);
  _navigateTo("http://www.google.ca");
  
  }
  _debugToFile(($rs[$i]["Uname"])+[',']+($rs[$i]["data2"])+[','], "C:\\values.csv");
  
• KMANDO Members

  December 2008

  does someone maybe have a sample xls sheet I can try out with this ?
  
  thanks
• KMANDO Members

  December 2008 edited December 2008

  I will see if I can repeat this with a simple search engine and place this information into a tutorial for others, seems to be a hot topic.
  Thanks guys !
  
  
  below is the full working code:
  
  

  var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/data1.xls","","");
  
  var $rs = [];
  $rs[0] = [];
  var $rs =  db.select("select * from [Sheet1$]");
  var $data1 =($rs[0]["Uname"]); // Uname is the name of the column
  
  var $len=100;
  for(var $i=0;$i<$len;$i++)
  {
  
  _setValue(_textbox("seed"),$rs[$i]["Uname"]);
  
  _click(_submit("submit"));
  _wait(3000);
  
  _debugToFile(_getText(_cell(_table(0), 2, 1)), "C:\\textfile2.xls");
  _click(_image(1));
  _wait(2000);
  _debugToFile(_getText(_cell(_table(0), 1, 2)), "C:\\textfile2.xls");
  _debugToFile(_getText(_cell(_table(0), 2, 2)), "C:\\textfile2.xls");
  
  _navigateTo("http://www.google.ca");
  }