Read data from spreadsheet based on Info in a column

[Adagio]

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

[Marsha_R]

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-sou...

[Adagio]

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]

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

[shankar_r]

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";   
}

[NisHera · Accepted solution]

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]

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 

[Adagio]

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