Microsoft Excel

Information specific to testing with Microsoft Excel

Introduction

Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows.

Configuration

Actions

TestArchitect provides the following built-in actions for working with SQL-compliant databases. They are listed in the typical order of their workflow:

  1. create connection string: Generate a connection string for a specified database on a given host.
  2. connect database: Open a database connection using a connection string.
  3. use database: Specify a database against which the ensuing SQL action lines are to be applied.
  4. execute sql: Execute an SQL command against the currently in-use database; optionally, store returned data into one or more data sets.
  5. disconnect database: Close the database connection.

Connecting to Excel from your test

Use the action create connection string to establish an ODBC connection to the Excel file. Note that the action’s driver argument should have one of the following values:

For Excel version 2007 and laterMicrosoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
For Excel 2.0 – 2003Microsoft Excel Driver (*.xls)

Use the others argument to specify the path and name of the Excel file, using the key DBQ. For example, DBQ=C:\Customers.xlsx.

Querying Excel from your test

For the purpose of running an SQL query, Excel offers a few constructs that can act as tables:

It’s important to note that, for whichever type of table construct you use, TestArchitect treats the first row of the given range as one of column headers. The values in this row are used as the field names of the dynamic data set (customer_ds in all the above examples) that is generated.

Example

create connection string  

Test Lines

Result

Notes


Copyright © 2023 LogiGear Corporation. All rights reserved. LogiGear is a registered trademark, and Action Based Testing and TestArchitect are trademarks of LogiGear Corporation. All other trademarks contained herein are the property of their respective owners.

LogiGear Corporation

1730 S. Amphlett Blvd. Suite 200, San Mateo, CA 94402

Tel: +1(800) 322-0333