JavaScript - Read Data from Excel and Maintain in dictionary

[Vallalarasu_P]

In Vbscript, We read the values from Excel and add to dictionary which makes the script faster as the excel is read only once and disconnected.

I'm looking for similar to better method in Javascript which can be faster. Please suggest.

Thanks in advance.

[shankar_r]

Instead of storing values in the Dictionary object, you can choose to save it in Project Variable Table type. It is much faster than Dictionary as it in-build and powerful option of TestComplete.

Ref: https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sou...

https://support.smartbear.com/testcomplete/docs/testing-with/variables/data-types/table.html#Editing

[Vallalarasu_P]

Do you suggest to use reading data from excel and convert into Json. so that we can use Json object to read the properties?

Can you share the code, If you have any.

[shankar_r]

 Below is the one i posted in one of the previous questions asked here

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