cancel
Showing results for 
Search instead for 
Did you mean: 

Excel Data Loop Missing Fields?

SOLVED
Highlighted
Contributor

Excel Data Loop Missing Fields?

My Excel Data Loop in TestComplete is not getting all of the data from the .xlsx spreadsheet.  Any ideas?

 

Example:

 

TC_Excel.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Hero

Re: Excel Data Loop Missing Fields?

https://support.smartbear.com/testcomplete/docs/testing-with/data-driven/excel-storages.html

 

For the data provider to treat spreadsheet data correctly, all data in each column (except the column name) needs to be the same type, for example, text only or numbers only. This is because the provider applies only one type to the whole column, so, if it contains data of several types, some values may be treated incorrectly.

 

You might have to revert to a code based solution and connect to the excel sheet in a different way.

 

You could try treating it like an ADO connection with:

 

let ControlCn = ADO.CreateADOConnection();
//create unique connection string with IMEX=1 to allow for mixed content columns
ControlCn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='<path to excel sheet>';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
ControlCn.LoginPrompt = false;
ControlCn.Open();

 


Thanks,
Carson

Click the Accept as Solution button if my answer has helped

View solution in original post

9 REPLIES 9
Highlighted
Community Hero

Re: Excel Data Loop Missing Fields?

Yeah, Excel isn't really good for the data if you're going to have blank fields.    :/

 

My first choice would be to use csv instead of Excel.  If you can't do that, then the only way we found around it was to put a special character of some sort instead of the blank and then say (in whatever language you use):

 

if excelfield = ".", then set datafield = ""

 

 

Highlighted
Contributor

Re: Excel Data Loop Missing Fields?

The problem is really that the blank fields highlighted should contain the data in the associated Excel fields.  The data it not being picked up in TestComplete.  In the image of the TestComplete viewer is overlayed on the associated Excel spreadsheet for reference.

Highlighted
Community Hero

Re: Excel Data Loop Missing Fields?

Sorry, that wasn't clear at all.

 

Let's see the code that's inside your data loop please.

 

 

Contributor

Re: Excel Data Loop Missing Fields?

It is only a simple data loop pulling data from an Excel file.  No code, nothing fancy.  'STL', Total Loads and the beginning date range do not show up in TestComplete when the data is viewed or looped through.

 

Data Driven Loop CodeDataDrivenLoop-7.png

 

Data Driven Loop Build

DataDrivenLoop-1.png

 

DataDrivenLoop-2.png

 

DataDrivenLoop-3.png

TestComplete Data Loop View

DataDrivenLoop-4.png

Excel Spreadsheet

DataDrivenLoop-5.png

 

 

 

Highlighted
Community Manager

Re: Excel Data Loop Missing Fields?

Thank you for providing the details scot1967!

 

@Marsha_R , @cunderw , @Wamboo , any suggestions?Smiley Happy



Sonya Mihaljova
Community and Education Specialist

 Join SmartBear Connect next April in Boston to meet with software development, API and testing experts from around the world! Get your ticket today!
Highlighted
Community Hero

Re: Excel Data Loop Missing Fields?

https://support.smartbear.com/testcomplete/docs/testing-with/data-driven/excel-storages.html

 

For the data provider to treat spreadsheet data correctly, all data in each column (except the column name) needs to be the same type, for example, text only or numbers only. This is because the provider applies only one type to the whole column, so, if it contains data of several types, some values may be treated incorrectly.

 

You might have to revert to a code based solution and connect to the excel sheet in a different way.

 

You could try treating it like an ADO connection with:

 

let ControlCn = ADO.CreateADOConnection();
//create unique connection string with IMEX=1 to allow for mixed content columns
ControlCn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='<path to excel sheet>';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
ControlCn.LoginPrompt = false;
ControlCn.Open();

 


Thanks,
Carson

Click the Accept as Solution button if my answer has helped

View solution in original post

Highlighted
Contributor

Re: Excel Data Loop Missing Fields?


@cunderw wrote:

https://support.smartbear.com/testcomplete/docs/testing-with/data-driven/excel-storages.html

 

For the data provider to treat spreadsheet data correctly, all data in each column (except the column name) needs to be the same type, for example, text only or numbers only. This is because the provider applies only one type to the whole column, so, if it contains data of several types, some values may be treated incorrectly.

 

You might have to revert to a code based solution and connect to the excel sheet in a different way.

 

You could try treating it like an ADO connection with:

 

let ControlCn = ADO.CreateADOConnection();
//create unique connection string with IMEX=1 to allow for mixed content columns
ControlCn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='<path to excel sheet>';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
ControlCn.LoginPrompt = false;
ControlCn.Open();

 


Very interesting.  I will get with dev and see what they say about this.  Thanks!

Highlighted
Contributor

Re: Excel Data Loop Missing Fields?

@cunderw    I just altered my spreadsheet and forced all the columns to text and TestComplete can now read all the data.  Smiley Happy  I think this may be the solution!  

 

Thanks!

Highlighted
Contributor

Re: Excel Data Loop Missing Fields?

@cunderw I had to use com.  I did not know how to access the data using the method you posted.  It works now.  I had to assign the return value to a project variable because it gets lost when Excel["Quit"](); is called.  Odd.  Any ideas?

 

function ReadDataFromExcel(str_Sheet,int_Column,int_Row)
{
  var str_value;
  var Excel = Sys["OleObject"]("Excel.Application");
  Excel["Workbooks"]["Open"]("C:\\temp\\rLoadSummary.xlsx");
  
  var xlsSheet = Excel.ActiveWorkBook.WorkSheets.Item(str_Sheet);
  xlsSheet["Activate"]();
  
  str_value = Excel["Cells"](int_Row,int_Column);
  
  //Had to set this as a proj var because it does not return the value after Excel["Quit"]()

Project["Variables"]["str_Excel_Value"] = str_value; Excel["Quit"](); return str_value; }