Best practices for your data file types and file formats
Direct mail is a data driven business and the secure, responsible and accurate use of that data is fundamental to the success of an industry that continues to thrive in the twenty first century. This information is designed to provide you with information detailing our preferences and recommended best practices for your data file types and file formats.
The information here should, if followed, ensure the management and sortation of your data will run as smoothly, and be as secure and accurate, as possible. Helping to guarantee that the timing and message of your direct mail will be at its most precise and effective.
Our preference is to receive your data in the form of a non-proprietary format text file. This can be as character-separated values (.csv, .txt, .tab etc.) or as fixed-length fields. We can also accept your data stored within an Excel file (.xls, .xlsx) or an Access database file (.accdb, .mdb), however, we would need to convert and save any worksheets or tables to a text file format.
Whichever file format you provide the data in we would also recommend only including the fields required for the mail piece, rather than outputting a large number of fields that aren’t required, as this can slow our data-processing as well as potentially sharing sensitive data that shouldn’t be supplied.
These are files where each data field is separated (or “delimited”) by a specific character. The most common example being a .csv file, where the data fields are typically separated by a comma. Other common separating characters can include a TAB, a pipe (|) or a semi-colon (;). The file extensions can also vary, and they can include .txt, .tab or .psv.
In many character-separated files the data fields are also surrounded by Text Qualifiers (“). These are sometimes added to the file upon save, and are there to ensure that each data field is explicitly defined. The prime example of this is when a comma-separated file has one or more data fields which contain a comma within the field value itself, for example as part of an address line. So, were Text Qualifiers not present to define the start and end of the fields, a program may incorrectly separate the data using any commas within the field values. A Text Qualifier placed at the start and end of a data field will indicate to a program to ignore any commas (or other specified separating character) contained within that field, and therefore split the data fields only using the separating commas between them.
It is also important that your data file does not contain any missing qualifiers. It is not necessary to surround every field in the data with text qualifiers (only fields that contain a separating character as part of its value), however, it is important to ensure that each start qualifier has a closing qualifier.
Another common form of text file is a fixed-length file. This is a file where each of the data fields, in the same position in every record, has a fixed number of characters. Typically, the text within the data fields is ‘padded’ with blank spaces at the end to ensure the field conforms to the set fixed length.
If you are supplying us with a fixed-length file, you must also include a field specification document that details the start and end positions of each field as well as well as the length of each field.
Our sortation software is designed for address data only. Any additional formatting and encoding should be done post sort and sanity checked for potential data loss. When supplying barcode data, this should be presented as non-encoded (eye readable text) and preferably not outside the ANSI “Western European” code page. For foreign characters, we request the data is presented in Unicode format.
Your data will be retuned in ANSI unless specifically requested in another format.
Editing Text files in Excel
If you need to edit your text files, you must ensure that your file is imported into Excel using the Data menu, rather than opening as standard via the Open menu or double clicking the file.
Excel is designed for calculation, and as text files do not contain any formatting information Excel will attempt to apply its own number formats to any text files opened. A very common example is Scottish addresses where you have a flat number / last flat number, or building number, e.g. 1/3 or 5/2 etc. If your text file contains these numbers in a separate field and you simply open the file in Excel, Excel will categorise these as dates and apply a date number format to them resulting in 01-Mar and 05-Feb. If the file is then saved these values will be changed permanently.
Another common example is telephone numbers and numbers that have leading or trailing zero’s. If you open a text file that contains the values like 0123456789 or 1234567890000, Excel will categorise these as integers and would apply a number format resulting in 123456789 or 1.23457E+11, so you would lose any zeroes at the start or end of those values and they may display in a truncated notation. Again, if the file is then saved these values will be changed permanently.
In order to prevent Excel from making these changes to text files, you must import your file via Excels Data menu, ensuring that all fields in your file are defined as containing ‘text’. This way Excel will not attempt to apply any other number formats, and the integrity of your data will be maintained.