You can specify the file type Query Scheduler creates when it runs a query, and you can specify designated recipients to whom to export the file, and various delivery methods for the export.
To set up Query Scheduler to automatically export a query results file you must do the following:
- Set up the file type
- Set up the export type
Note: You can use the attached template as a guide for exporting. Right click and choose "Save As..." (or "Save Target as...") to download this file, sample file.
Set up the file type
Query Scheduler can create two types of files for query results:
- Delimited files with the .csv extension
- Excel files
Query Scheduler can either zip or encrypt the query results file before sending it to designated recipients.
Note: You can only specify one file type per query.
When you set up a file type, you must specify the name. You can use the following variables in the name:
| Variable | Replacement text |
|---|---|
| %ID% | Query ID number |
| %yyyyMMdd% | Year, month, and day the query results file is created |
| %HHmm% | Hour and minute the query results file is created |
Note: TEAM Software recommends you use variables in the file name. If you do not and you select the Overwrite Existing File check box, new queries with the same name are not saved.
To set up the file type as a delimited text file
- On the Query Scheduler (Export Settings tab) in the File Types area File Name field, type the name for a file. You can use either a static name or a variable name. A preview of the name appears below the box.
- To overwrite existing files with the same name when Query Scheduler creates a new file, select the Overwrite Existing Filecheck box.
- Select the CSV option. The screen changes to show the required fields for sending a delimited text file.
- In the Delimiterfield, specify the delimiter to use between results. You can use any character as a delimiter.The default delimiter is a comma.
- (optional) To include the column names from the WinTeam database as headers in the CSV file, select the Include Headers check box.
- To specify additional file options, do one of the following:
- To send the query results file with no additional processing, select None.
- To zip the query results file and protect it with a password, select the Zip Results option. The Password box appears. Type the password for the file in it.
- To encrypt the query results file using Pretty Good Privacy (PGP), select the PGP Encrypt Results option. The Public Key box appears. Type the public key in it. (To open the file, you must have the private key.)
To set up the file type as an Excel file
- On the Query Scheduler (Export Settings tab), type the name for the file. You can use either a static name or a variable name. A preview of the name appears below the field.
- Select the Excel option. The screen changes to show the required fields for sending an Excel file.
- In the Excel File Pathfield, type the path to the Excel template file. The Excel template must reside on the same server as the Query Scheduler Engine.
- In theWorksheet Name field, type the name of the worksheet in the Excel file that you want to use.
- In the Starting Cell field, type the name of the cell in which you want to insert the first record of the query results.
- To specify additional file options, do one of the following:
- To send the query results file with no additional processing, select None.
- To zip the query results file and protect it with a password, select the Zip Results option. The Passwordfield displays. Type the password for the file in it.
- To encrypt the query results file using Pretty Good Privacy (PGP), select the PGP Encrypt Results option. The Public Key box appears. Type the public key in it. (To open the file, you must have the private key.)
Set up the export type
You can designate recipients of the query results file, and you can specify whether to send them an email with the file attached or to FTP the file. You can set up Query Scheduler to send query results as an attachment to an email to designated recipients each time the query is run.
Note: If you send query results to an email group, you must include certain information in the query. For details, see Custom Queries.
To send query results in an email
- On the Query Scheduler (Export Settings tab), select the Email check box.
- In the Addressfield, type the email address of the query result recipient. To send the results to more than on recipients, enter a semi-colon between the email addresses.
- To send the query results to an email group, in the Group field, type the name of an email group or double-click in the box and create a new email group.
- To specify a subject line for the email, in the Subjectfield, type the subject. The number of records in the query results appears at the beginning of the subject line, regardless of what you type in the Subject field.
- In the Bodyfield, type a message to appear in the body of the email.
- To send the query results as an attachment rather than a path, select the Attach Report in Email check box. If you clear this check box, Query Scheduler sends the path to the query results file instead of attaching the query results file to the email.
- To specify an address to which to send an email if there is an error with the query, in the Error Address field, type an email address. If you do not enter an email address, Query Scheduler sends email messages about query errors to the Admin email address.
To send query results by FTP
- On the Query Scheduler (Export Settings tab), select the FTP Resultscheck box.
- In the Addressfield, type the FTP URL. Query Scheduler saves the query results file at the root of the address. You can append a directory name at the end of the URL using \<directory name>, and Query Scheduler will save the query results file to that directory instead.
- In the Username field type the FTP user name.
- In the Passwordfield, type the FTP password.
- To use Secure File Transfer protocol (SFTP), select the Secure File Transfer Protocol (SFTP) check box.