Mailing list - Entries of 2005


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[qftestJUI] How to access an Excel table from qftestJUI


  • Subject: [qftestJUI] How to access an Excel table from qftestJUI
  • From: Karlheinz Kellerer <Karlheinz.Kellerer@?.de>
  • Date: Fri, 07 Oct 2005 09:07:02 -0000

Hello everyone,

this question comes up recurrently in combination with data driven testing, so the
answer may be interesting for some of you.

Of course there is the way to export the Excel table into a CSV (Comma
Separated Value) file and read this file as it is described in the qftestJUI
user manual "Chapter 11  Scripting", exactly in "11.3.8  A complex example".

But if it is not preferred or possible to go via this extra step of conversion,
it is also possible to directly access the Excel file. This can be done by
making the Excel file accessible as an ODBC data source and use a JDBC-ODBC
Bridge driver to access this data source from qftestJUI via Jython.

For all basic information and technical background how to access a database
from qftestJUI please see

    http://www.qfs.de/archive/qftest-list/2004/msg00839.html


Following steps are necessary to access the Excel Sheet.

1. Make Excel Sheet accessible as ODBC Data Source

Because I have only a German Windows version available, I hope my translation
fits the English version.

ODBC data sources can be created via "Administration" in the MS Windows control
panel. There you are able to select an Excel file as User Data Source by
choosing the Excel Driver. As name of the data source I assume "employee_xls"
for the following example.

2. Server Script Node for access the ODBC Data Source

The following Jython script (which needs to be placed into a qftestJUI server
script node) is an example how to establish a connection to the ODBC
data source "employee_xls". You also need to specify the table sheet name
inside the Excel file, which is "Sheet1" in this case.


    from com.ziclix.python.sql import zxJDBC

    user = ""
    password = ""
    dbclass = "sun.jdbc.odbc.JdbcOdbcDriver"

    db = zxJDBC.connect("jdbc:odbc:employee_xls", user, password, dbclass)

    cursor = db.cursor()
    cursor.execute ("select * from [Sheet1$]")
    rows = cursor.fetchall()
    for row in rows:
        # do what needs to be done, e.g.
        rc.callProcedure("printRow", {"nr": row[0], "surname": row[1], "firstname": row[2]})

    db.close()


Please refer to http://www.python.org/peps/pep-0249.html for further
information about Python's DB API which the ziclix driver supports.

Documentation of the JDBC-ODBC Bridge driver can be found at
http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/bridge.doc.html

Best regards,
    Karl

--
Karlheinz Kellerer                     Karlheinz.Kellerer@?.de
Quality First Software GmbH                    http://www.qfs.de
Tulpenstr. 41                               Tel: +49 8171 919877
DE-82538 Geretsried                         Fax: +49 8171 919876



Videos Downloads Documentation Buy Free Trial