Forum Discussion

Adagio's avatar
Adagio
Frequent Contributor
7 years ago

Read data from spreadsheet based on Info in a column

Hello,

 

I'm trying read the following info from a spreadsheet by using a javascript code. For each ItemNum, I have to read the 'Related PIN' and do an operation. Once all the Related PINs are covered for an ItemNum, I move to new Item and repeat the same. 

 

I haven't been able to come up with a better way yet. Any ideas or suggestions would be appreciated!

 

 

Thank you

Abhi

  • NisHera's avatar
    NisHera
    7 years ago

    below is what Marsha_R suggested in Java script code skeleton

    var RelatedPIN
var ItemNum
DDT.ExcelDriver("C:\\myfolder\\myData.xlsx","Sheet1",true);
  do
  {
    if (DDT.CurrentDriver.Value('ItemNum')!=null){
		//set my item number
		ItemNum = DDT.CurrentDriver.Value('ItemNum');
		//and go inside item ... write some code to go into new item using ItemNum
	}
	RelatedPIN = DDT.CurrentDriver.Value('RelatedPIN');
	// code for do an operation ...do what ever your operation

    DDT.CurrentDriver.Next(); 
  }
  while (!(DDT.CurrentDriver.EOF()));
  DDT.CloseDriver(DDT.CurrentDriver.Name);

    let us know any more help needed...... 

  • shankar_r's avatar
    shankar_r
    Community Hero
    7 years ago

    I would make your sheet as like below,

     

    ItemNum Related PIN
    5546 556445
    5546 5564456
    5546 5564457
    5546 5564458
    5578 778665
    5578 778666
    5589 998989
    5589 998988
    5589 889989

     

    And using below Excel operations to loop thru for particular item number.

    function test(){
      var excelFileName = "D:\\Automation\\Scripts\\WorkingScripts\\Automation_Project\\TestData.xls"
      var excelSheetName = "Sheet1";
      var ItemNumber = 5588; 
      var columnToFilter = "ItemNumber"
      fn_ExcelUsingADODB_test(excelFileName,excelSheetName,columnToFilter,ItemNumber);  
}

function fn_ExcelUsingADODB_test(excelFileName,excelSheetName,columnToFilter,columnvalue)
{
      var ctExcelProvider64 = "Microsoft.ACE.OLEDB.12.0",ctExcelProvider32 = "Microsoft.Jet.OLEDB.4.0";
      
      var excelConnection = new Sys.OleObject("ADODB.Connection");
      
      var str_Connection = "Provider=" + (is64BitClient() ? ctExcelProvider64 : ctExcelProvider32) + ";Data Source = " + excelFileName + ";Persist Security Info=False;Extended Properties=Excel 8.0;"
      
      excelConnection.Open(str_Connection);
      
      var excel_recordSet = new Sys.OleObject("ADODB.Recordset");
      
      var excelQuery = "select * from [" + excelSheetName + "$] where " + columnToFilter + " = '" + columnvalue + "'" ;
      
      excel_recordSet.Open(excelQuery, excelConnection);
      
      if(!excel_recordSet.bof)
      {
            excel_recordSet.MoveFirst()
            while(!excel_recordSet.eof)
            {
                  for(var i=0; i!= excel_recordSet.fields.count; ++i)
                  {
                        Log.Message(excel_recordSet.fields.Item(i).name);
                        //do your stuff here
                  }
                  
            }
      }
      excel_recordSet.Close();
      excelConnection.Close();
}
function is64BitClient()
{
      var tcprocess = Sys.WaitProcess("Test*te", 0);
      
      if(tcprocess.Exists)
      {
            if(tcprocess.ProcessType == "x64")
            {
                  return true;
            }
            else if(tcprocess.ProcessType == "x86")
            {
                  return false;
            }
            else
            {
                  throw "Not able to grab the process type";
            }
      }
      throw "Not able to find the TestComplete/TestExecute process";   
}

     

     

    • Adagio's avatar
      Adagio
      Frequent Contributor
      7 years ago

      Thanks shankar_r for your inputs. It might not work efficiently  in my casebut still I'll see if it helps. Really appreciate your help.

       

      Thank you

      Abhi

  • NisHera's avatar
    NisHera
    Valued Contributor
    7 years ago

    below is what Marsha_R suggested in Java script code skeleton

    var RelatedPIN
var ItemNum
DDT.ExcelDriver("C:\\myfolder\\myData.xlsx","Sheet1",true);
  do
  {
    if (DDT.CurrentDriver.Value('ItemNum')!=null){
		//set my item number
		ItemNum = DDT.CurrentDriver.Value('ItemNum');
		//and go inside item ... write some code to go into new item using ItemNum
	}
	RelatedPIN = DDT.CurrentDriver.Value('RelatedPIN');
	// code for do an operation ...do what ever your operation

    DDT.CurrentDriver.Next(); 
  }
  while (!(DDT.CurrentDriver.EOF()));
  DDT.CloseDriver(DDT.CurrentDriver.Name);

    let us know any more help needed...... 

    • Adagio's avatar
      Adagio
      Frequent Contributor
      7 years ago

      Thanks NisHera...It gave me a good idea on how to use it for even more complex scenario that  I have. really appreciate you help!

       

      Thank you

      Abhi 

  • Marsha_R's avatar
    Marsha_R
    Moderator
    7 years ago

    Are you looking for help setting up your data or help doing a data-driven loop?

     

    We've used a lot of data set up that way.  The other way we've organized it was to have one sheet with 

    ItemNum Sheet  in the first sheet

    where Sheet is a separate sheet that just has the PINs for one item number

     

    You iterate through the ItemNums and for each one, find its sheet with PINs and iterate through those.

     

    If you need help with data-driven loops, you can find that here:

    https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sources/excel/index.html

     

    • Adagio's avatar
      Adagio
      Frequent Contributor
      7 years ago

      Thanks Marsha! I'm familiar with these data driven loops, but haven't been able to use them in this case. I would have to keep this data in the same spreadsheet because this info would change very frequently and couple other reasons too.

       

      Thank you

      Abhi

      • Marsha_R's avatar
        Marsha_R
        Moderator
        7 years ago

        I don't know javascript but something like this should work

         

        for loop = 0 to EndOfFile

          if loop.itemnum is not null then

               set variable.itemnum = loop.itemnum

          set variable.pin = loop.pin

          ***do stuff with variables

        next loop