Absent Member.
Absent Member.
1338 views

Too many cells or rows when synchronizing xls data sources.

Jump to solution

Hello , we are experiencing problems with our spredsheets for the data-driven tests. We are aware of the limit for the rows (100) , and we make sure to not exceed it, but we still get this error message. What are the other limits? How many sheets we can have in one spreadsheet/workbook? How many columns we can have per sheet?

Thank you in advance.

Boris,

Tags (1)
0 Likes
1 Solution

Accepted Solutions
Micro Focus Expert
Micro Focus Expert

Hi Boris,

Data sources have specific limits to prevent Silk Central from getting into an unstable state due to excessive data generation. They have the following default limits:

- 100 rows for non-atomic tests (option "Each data row is a single test" enabled)

- 1000 rows for atomic tests (option "Each data row is a single test" disabled)

- 20000 rows+cells for XLSX data sources (20000 is the amount of rows + the amount of cells. For example: If you have 3000 rows with 6 columns, the calculated amount is 3000 + (3000 * 6) = 21000, so the limit is exceeded.)

- 220 characters for data set names (Excel sheet names/table names)

- 220 characters for column key names

- The concatenated length of all primary keys may not exceed 255 characters in length for JDBC, Excel, and CSV data sources

- 2000 characters per cell (longer strings will be truncated).

 

I think in your case the 20000 rows/cells limit triggers, you can modify the default limits as described below:

Modify these values with care though to avoid excessive data generation. Before attempting to modify these values, stop the application server and restart it again after you have saved your changes.

 

The row limits for atomic and non-atomic tests can be configured in the TmAppServerHomeConf.xml file. The default path for this file is

C:\Program Files (x86)\Silk\Silk Central 18.0\instance_<instance number>_<instance name>\Conf\AppServer

on the application server. Modify the values of the Config/DataDriven/MaxRowsAtomic and Config/DataDriven/MaxRows XML tags, respectively.

 

The XLSX cell limit can be configured in the configuration.properties file in the Excel data source plugin (DS-Excel.zip). The default path for this file is

C:\Program Files (x86)\Silk\Silk Central 18.0\instance_<instance number>_<instance name>\Plugins

on the application server. Modify the value of the excel.importcelllimit property accordingly.

 

Regards,

Hubert

View solution in original post

8 Replies
Micro Focus Expert
Micro Focus Expert

Hi Boris,

Data sources have specific limits to prevent Silk Central from getting into an unstable state due to excessive data generation. They have the following default limits:

- 100 rows for non-atomic tests (option "Each data row is a single test" enabled)

- 1000 rows for atomic tests (option "Each data row is a single test" disabled)

- 20000 rows+cells for XLSX data sources (20000 is the amount of rows + the amount of cells. For example: If you have 3000 rows with 6 columns, the calculated amount is 3000 + (3000 * 6) = 21000, so the limit is exceeded.)

- 220 characters for data set names (Excel sheet names/table names)

- 220 characters for column key names

- The concatenated length of all primary keys may not exceed 255 characters in length for JDBC, Excel, and CSV data sources

- 2000 characters per cell (longer strings will be truncated).

 

I think in your case the 20000 rows/cells limit triggers, you can modify the default limits as described below:

Modify these values with care though to avoid excessive data generation. Before attempting to modify these values, stop the application server and restart it again after you have saved your changes.

 

The row limits for atomic and non-atomic tests can be configured in the TmAppServerHomeConf.xml file. The default path for this file is

C:\Program Files (x86)\Silk\Silk Central 18.0\instance_<instance number>_<instance name>\Conf\AppServer

on the application server. Modify the values of the Config/DataDriven/MaxRowsAtomic and Config/DataDriven/MaxRows XML tags, respectively.

 

The XLSX cell limit can be configured in the configuration.properties file in the Excel data source plugin (DS-Excel.zip). The default path for this file is

C:\Program Files (x86)\Silk\Silk Central 18.0\instance_<instance number>_<instance name>\Plugins

on the application server. Modify the value of the excel.importcelllimit property accordingly.

 

Regards,

Hubert

View solution in original post

Absent Member.
Absent Member.
Hubert , that's an answer - thank you so much!
0 Likes
Absent Member.
Absent Member.

Hello Hubert,

My name is Jacob. I am a colleague of Boris. Thank you very much for the detailed response it is very helpful.

I did some math on one of the spreadsheets I am trying to synchronize. There are 147 individual rows and 6738 cells being used. Obviously that is not close to the 20,000 number you mentioned in the previous comment.   My data source uses several different sheets and each sheet has different numbers of columns. Does this make a difference?

Another colleague mentioned it could be because we are leaving blank cells in some of the rows in the spreadsheets. Do you think this could be the case?

Thank you for the support,
Jacob

0 Likes
Micro Focus Expert
Micro Focus Expert
Hi Jacob,

To answer your first question, yes this makes a difference as the counts from all sheets in the synchronized file are summed up.

Blank cells: yes, all cells existing in the XLSX file format are considered when calculating the count. During synchronization they have to be processed the same way as if there was data in them.

Hope that helps,
Hubert
0 Likes
Absent Member.
Absent Member.
What do you mean by "counts from all sheets in the synchronized file are summed up"?
0 Likes
Micro Focus Expert
Micro Focus Expert
If you synchronize an XLSX file containing 3 worksheets, e.g. sheet 1 has 100 cells + rows, sheet 2 has 200 cells + rows and sheet 3 has 300 cells + rows then a number of 600 is considered when checking the limit.
0 Likes
Absent Member.
Absent Member.
Ahh i see thank you for clearing that up. So after taking that into consideration the count I come up with is only 6603.

That number includes any blank cells in the document. So I am still at a loss for why i continue to get this error. My source seems to be far under the constraints.
0 Likes
Absent Member.
Absent Member.
Hi Jacob,

If you log a support incident through supportline@microfocus.com or using our support line website supportline.microfocus.com, we can then request the excel file. This will allow us to check the file, and confirm the settings required to successfully upload this to Silk Central.

Thanks,
Matthew
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.