September 20, 2012

Finding the Number of Columns in a Datasheet

Helix ALM
Many applications generate reports as Excel spreadsheets, which QA Wizard Pro can easily connect to via an external datasheet definition. If the number of rows is the datasheet is dynamic, which it often is, the current number of rows can be found by using the RecordsetRowCount built-in function and the returned number can be used to loop through each row in the datasheet. If you have a datasheet that has a dynamic number of columns, though, you may be struggling with how to determine the exact number of columns defined at a particular time. One way is to use the default column headers provided by QA Wizard Pro and access values until an undefined column is reached. Accessing a non-existent column will trigger an error, but you can use the Try…Catch…Finally error handling statement to ensure the script continues. If the number of columns is not fixed, the order in which they appear in the datasheet or even column headers themselves may not be known. When creating the external datasheet connection, be sure the “First row contains column headers” checkbox is NOT set. This will create the default “F1”,”F2”, etc. columns headers, which can then be used to retrieve the row data. The function below takes the recordset FILE returned from the datasheet in the OpenRecordset statement, and returns the current number of columns. An OpenRecordset statement should always be used prior to calling the function, to ensure that the most recent version of the data is being used. Column values from the first row of data are retrieved, and when the last column is reached the GetRowValue statement for the next column will fail. That failure is “caught” and the number of columns adjusted for the failure. The error will be reported on the Error tab in in the generated report but can be ignored.
Function RecordsetColumnCount(DATASHEET) NUMCOLS = 1 VALUE = "" While VALUE <> "done" Try VALUE = GetRowValue(DATASHEET,"F" + NUMCOLS) NUMCOLS = NUMCOLS + 1 Catch NUMCOLS = NUMCOLS - 1 VALUE = "done" Finally End Try WEnd Return (NUMCOLS) End Function<script steps> FILE = OpenRecordset(“WysiCorp Bugs”) NUM = RecordsetColumnCount(FILE)
The function can be placed at the beginning of the script that calls it as shown above, or placed in a separate script containing other function and subroutines. The latter option allows any other script to reference it. The function script itself must be loaded via a CallScript step and include the function's script name in the function call.
CallScript(“Functions”) <script steps> FILE = OpenRecordset(“WysiCorp Bugs”) NUM = Functions.RecordsetColumnCount(FILE)
Once you have the number of columns, you can get the actual header values by creating a loop that reads in the first row of data from each column, using the default “Fx”, where x equals 1,2,3, etc. to reference the column header.
For X = 1 to NUM println(GetRowValue(FILE,"F" + X)) Next