Forum Discussion

scot1967

Frequent Contributor

5 years ago

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:

cunderw
5 years ago
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();

scot1967

Frequent Contributor

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.

Marsha_R

Moderator

5 years ago

Sorry, that wasn't clear at all.

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

scot1967
Frequent Contributor
5 years ago
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 Code

Data Driven Loop Build

TestComplete Data Loop View
Excel Spreadsheet
- sonya_m
  SmartBear Alumni (Retired)
  5 years ago
  Thank you for providing the details scot1967!
  
  Marsha_R , cunderw , Wamboo , any suggestions?:smileyhappy:
  - cunderw
    Community Hero
    5 years ago
    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();