Using the Config EditorReference Replacer

Launching the ReplaceRefs.exe executable without any parameters will load the Configuration UI.

Config Options

The 'Setup' tab allows you to define the files and folders that will be processed by your config file.

General

Enabled: When ticked, the config file will be processed when run in processing mode. When enabling the config file, it will be sanity checked. If there are any problems, these will be displayed and the file disabled. Note: A sanity check is also made before processing the file, if directories are deleted after the the UI check, the file will not be processed and administrators e-mailed.

Single Query: In most cases, your SQL should support Single Query mode. If you find you cannot get your query to run properly, try disabling it. This will be slower and have more of a performance impact on the database server. See the SQL Creation Guide for more information on Single Query mode.

Expired E-mail

The expired file e-mail is optional. If you do not want to send an e-mail, leave the recipient field blank. E-mails are always sent via BCC to avoid accidentally exposing e-mail addresses to 3rd parties.

If you do not specify a sender, the administration sender address is used. If you'll only be sending from one address, use this as it means there is a single place to change the e-mail address.

You can insert a few references into the e-mail subject/body to help identify the file. Click into either the subject/body text boxes, click the 'Insert field' drop down and select the field to insert. A %REFERENCE% placer will be inserted and replaced at runtime.

You can optionally attach the file and use HTML e-mail with the tick boxes below the subject.

To send a test e-mail, enter an address into the box below the body and press the 'Send test' button.

Database Settings

Connection String

The application uses an OdbcConnection to connect to the database and should work with any database that provides an ODBC driver. Sample MySQL and MS SQL Server connection strings are below.

  • MS SQL Server:
    Driver={SQL Server};Server=host\instance;Database=DbName;Uid=username;Pwd=password;
  • MS SQL Server (AD authentication):
    Driver={SQL Server};Server=host\instance;Database=DbName;Trusted_Connection=Yes;
  • MySQL:
    Driver={MySQL ODBC 5.1 Driver};Server=host;Port=3306;Option=4;Database=DbName;Uid=username;Pwd=password;

Click the 'Test' button to test connecting to the database server.

SQL Query

You will want to build your SQL query in your editor of choice, SSMS, MySQL Workbench etc. and then bring it over. You will need to structure your query as described in the SQL Creation Guide.

Source Settings

The application scans the source folder for files matching the pattern. The pattern must be a valid regular expression. You can use RegexPal.com to build and test your expression.

References extracted by the regular expression are passed into the SQL query. If no results are returned for the reference combination within 'Max Age', the file is considered expired and the file moved to the 'Expired to' folder.

When references are found, the output file is created in the destination folder (see below) and the success action is taken. This can be either move or remove the source file. Move allows you to keep the file, however it must be placed outside of the source folder to prevent the file being picked up again.

Destination Settings

The output file is placed into the 'Save to' folder with the name defined in the 'Filename' field. The file name can contain source and/or SQL field values.

Replacement fields are index-based and placed in curly braces {}. Source reference indexes refer to the regular expression group from the source file name and should be prefixed with 'src_'. SQL fields should match the field alias returned by the query.

An example filename could be: NewFile_{src_0}-{sql_0}.txt

If your regular expression doesn't return the file extension, this can be copied from the source file name by ticking 'Add extension from source file'.

Testing

Once you have built your configuration, you can test it on the 'Test' tab.

Make sure your pattern matches the files you want to test and press 'Add from Folder'. This will default to the source path and will bring in the first 7 files that match your pattern into the 'Pattern Source Replacements' list. You can then amend the pattern and/or destination filename and see the results in real-time.

You can also perform a dry-run, which will perform the same actions as in processing mode, without making any changes or sending any e-mails. The log from the dry-run is displayed in the box at the bottom.

The dry run log includes the command executed to perform the dry run. You can use this for your scheduled task by removing the '--no-log --dry-run -f "Filename.conf"'.