execsql 0.4.4.0

Run a SQL script against a Microsoft Access database and capture output.

Capabilities

You can use the execsql program to:

• Execute a complex query script that selects and summarizes data, and

capture the output in a comma-separated-value (CSV) file. The CSV file can then be used as input to other software, including statistical, spreadsheet, and database applications. * Execute a query script that selects and summarizes data, and view the results directly in Excel. * Execute SQL Data Definition Language (DDL) and Data Manipulation Language (DML) commands, stored in a text file, to create database tables and alter data in the database. Using execsql and other tools such as batch files and the Windows task scheduler, these operations can be set up so that they occur automatically without the need for any interactive use of Access. * Execute "CREATE QUERY..." and "CREATE TEMPORARY QUERY..." DDL statements, which are not natively supported by Access. This feature allows you to maintain a standard set of queries, or task-specific queries, in a text file and easily add them to any Access database. Support for "CREATE TEMPORARY QUERY..." DDL is also important to allow non-trivial data summarization scripts. * Export data and write messages using metacommands embedded in SQL comments.

Requirements

The execsql program used the Common Object Model (COM) to communicate with Access and Excel. Python does not include a standard library that supports COM, and so execsql uses Mark Hammond's pywin32 extension for Windows to provide COM support. A version of pywin32 compatible with your version of Python must be installed so that execsql can be used.

Notes

• Script files can contain comments to document the purpose and operation of

the SQL statements. * A 'CREATE TEMPORARY QUERY...' extension to Access (Jet) SQL is supported to facilitate writing complex scripts. * With the exception of the 'CREATE TEMPORARY QUERY...' statement, the execsql program does not parse or interpret SQL syntax in any way. The program recognizes a SQL statement as consisting of any sequence of non-comment lines that ends with a line ending with a semicolon. SQL syntax used in the script must conform to that recognized by the Jet database engine. * Only the results of the final query in the script will be saved as CSV or routed to Excel. If the final query includes the "CREATE TEMPORARY QUERY..." prefix, then no output will be produced regardless of any output specification that is provided on the command line. If the final query is a DDL command, or a DML command other than a SELECT, UNION, or TRANSFORM statement, the output may not be produced, or may be empty or meaningless.