AFD Products: Mailsaver Manual

OPENING YOUR DATABASE

From the Mailsaver 'File' menu, click on 'Open Database' . Locate and select the database to be prepared for mailing and a pop-up menu will then allow selection of the specific database table containing the address data to sort.

Supported Database Formats

Mailsaver can operate with Microsoft Access databases, xBase files (dBase, Foxpro, Clipper, etc), and Paradox tables. It also has native support for delimited (e.g. quoted comma separated) and fixed record length ASCII Text files.

Most other databases can be accessed through Mailsaver's ODBC support, providing a DSN for that data source has been set-up on your system.

Opening Non-ODBC Databases

Use the 'File' Menu 'Open Database' (Ctrl+D) option to open non-ODBC databases. Select the database file you wish to work on and Mailsaver pops up a menu. From this menu choose whether to work on a straight table of data (an Access database may offer several tables within the database file), or whether to use an SQL query. If you select to use SQL Query, a simple SQL Query is provided for access to the data in the first table. This is a starting point and the Query can be modified to suit your needs.

Opening Delimited Text Files

Mailsaver includes very flexible delimited text file support for working with data in text format. This is particularly useful if your data format is not supported, as you can convert a copy to one which is, so that it can be processed. Most databases can export to Comma Separated Text (sometimes known as CSV or ASCII). As delimited text has it's limitations, If your database supports exporting fixed format text you may prefer to use that instead (see next section). Mailsaver can only write back to fixed record length files it is reading data from - so if you process a delimited text file, a new file is created with the file suffix '.new', e.g. processing 'Address.Txt' would create 'Address.New'. It is helpful if the first 'Record' in your text file has the names of the fields in each field - like 'Street', 'Town'. Mailsaver can then display these during attachment. SQL cannot be used to work with Text Files - so queries and sorting should be carried out before processing through Mailsaver.

When you open a delimited text file you will be presented with the following options to specify the format of that text file:

The delimiter is the character which separates each field in the text file, e.g. a comma is the most common here. The qualifier is the character used to help prevent field cross-over as mentioned previously. If there is no qualifier present you can select None from the drop down list. Address list data is especially susceptible to spurious delimiter characters - like quote marks or commas. House names entered as "The Willows" or address lines like Rose Cottage, High Street, can mean that any data process thinks there are more fields than there really are. This is a problem. It's best to use Quoted Comma Delimited text which is less sensitive to errors. If there are problems in your data export Mailsaver protects against the address record cross-over that can occur in these circumstances - and limits damage, usually just to one record . It remains far better if these characters are removed before creation of your processing file. The use first record for field names option specifies that the first record in the text file contains the field names - it is recommended you include these when exporting your data to make identifying and mapping fields much easier.

As Delimited Text Files are more limited in functionality than other database formats Mailsaver also gives you the option to import the data into Access Format for processing. This allows more advanced functionality such as moving back through records, de-duplication etc. to be utilised which are not available when using delimited text files at the expense of speed and efficiency of processing. If you opt to use this option you can use the 'Export Data to CSV File' option on the File menu to export the data back to a text file once processing is complete.

Opening Fixed Record Length Text Files

Fixed record length text files are a very efficient file format and all Mailsaver features are supported when using them. If your database is not directly supported, or you want maximum speed and flexibility from Mailsaver this may be the best format for you. The only format which is as efficient as Fixed Record length text files for Mailsaver to process is dBase files, Paradox tables come a very close third.

When you open a fixed record length text file you will be presented with the following options to specify the format of that text file:

Mailsaver will attempt to automatically detect the format of the text file, but if you need to adjust this because there is no record separator so the record length cannot be determined or you need to tweak field positions then you can enter these directly here. You can also review some of the records in the file on the right using the navigation controls above the Cancel button to ensure the format is correctly specified.

Opening ODBC Databases

Use the 'File' Menu 'Open ODBC Connection' option to open a connection to an ODBC database that you have configured a DSN for on your system. The screen will change to allow you to select a DSN, and type your user identifier (uid) and password (pwd) if required and any additional parameters you may require to connect to that database through ODBC. You can then enter the SQL Query to access the data to be cleaned. Then click the 'Query' button to connect to your database.

Using SQL

Using SQL to query a database is a powerful, flexible way to control the cleaning process. To use it means learning some simple SQL statements. SQL can be used to restrict cleaning to just parts of the database (perhaps where postcodes are missing), to re-start a cleaning session where it was left-off - or as shown to speed up processing by presenting data in postcode order - which is far more efficient on disk access.

The precise form of these examples will vary with the name of the database and the name of the fields within it, but should be easy to translate:

Select * from [Addresses]

       Selects all fields from a table called 'Addresses'. Every address record will be processed.

Select * from [Addresses] where Postcode < 'H'

       As above - but only processes address records with a postcode beginning "A" to "G".

Select * from [Addresses] where Town="Ramsey"

       As above - but only processes addresses in Ramsey.

Select * from [Addresses] where Orders > 500

       As above - but only processes records where the Orders field contains a value greater than 500.

For full details of supported SQL statements please refer to your database engine's own documentation.

Address, Postcode and Bank solutions