Pipe-Delimited Flat Files

Downloading & Using Pipe-Delimited Flat Files

These instructions are for users who cannot connect to the live PostreSQL database or who want the flexibility of manipulating this information in a raw, conventional text-based format. The flat files can also be used to populate a non-PostgreSQL database. Each set of flat files is created every day from the most current version of the live AACT database. Each daily set of flat files is made available for download until the end of the month. On the first of each month, a permanent copy of the flat files is created, archived and will remain available for download from this website.

General information about Flat Files

1. Structure of Flat Files: The files were created under a PC environment using UTF-8 encoding. Fields within each file are separated by the vertical bar character (“|”) commonly referred to as a “pipe”. A null or missing value for a field is delineated by consecutive “pipes” in the data stream. Records within each file are delimited by the line feed (LF) character. The first row of each file contains a delimited list of field names and the order of these names indicates the order of the data fields in the flat file. Most fields are character and are not enclosed within quotes, although single or double quotes may appear embedded within many of the descriptive fields.
2. Modifications to Source Content to Facilitate Use of Flat Files: In rare cases, the actual content of the study data contains an embedded pipe (“|”). To prevent software from interpreting the embedded pipe as a delimiter, the entire string containing the pipe has been enclosed in double quotes. Also,line feed and paragraph break characters within fields, which might otherwise be interpreted as end of record characters, have been removed from the flat file database extracts.
3. Users are encouraged to refer to the Schema Diagram and associated information to determine the relationships between the different data files that comprise AACT. These relationships determine how data sets may be merged together. There is one flat file for each database table indicated in the Schema Diagram. In addition, flat files containing meta data are available (data_definitions.txt, sanity_checks.txt, statistics.txt) are included. The Data Dictionary provides critical information about datasets and variables, including the expected number of records in a dataset, and the length of each variable.