Downloading & Using Pipe-Delimited Text Files

These instructions are for users who have trouble connecting to the live PostreSQL database, or who would like to create a local static copy of the AACT database from the pipe-delimited text file extracts. These text files are created monthly from the live AACT database.

General information about Text Files

  1. Structure of Text Files: The text 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 text 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 Text Files: In rare cases, the content within a field may contain an embedded pipe (“|”). To prevent software from interpreting the embedded pipe as a delimiter, the entire string has been enclosed in double quotes. Line feed and paragraph break characters within fields, which might otherwise be interpreted as end of record characters, have been removed from the text 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 text file for each database table indicated in the Schema Diagram. In addition, text 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.
1

Download Zip File Containing Pipe-Delimited Files

[{:name=>"20170906_pipe-delimited-export", :date_created=>"09/06/2017", :size=>"695 MB", :url=>"https://s3.amazonaws.com/aact-prod/csv_pipe_exports/20170906_pipe-delimited-export"}, {:name=>"20170811_pipe-delimited-export", :date_created=>"08/11/2017", :size=>"679 MB", :url=>"https://s3.amazonaws.com/aact-prod/csv_pipe_exports/20170811_pipe-delimited-export"}, {:name=>"20170618_pipe-delimited-export", :date_created=>"06/18/2017", :size=>"665 MB", :url=>"https://s3.amazonaws.com/aact-prod/csv_pipe_exports/20170618_pipe-delimited-export"}, {:name=>"20170508_pipe-delimited-export", :date_created=>"05/08/2017", :size=>"650 MB", :url=>"https://s3.amazonaws.com/aact-prod/csv_pipe_exports/20170508_pipe-delimited-export"}, {:name=>"20170416_pipe-delimited-export", :date_created=>"04/16/2017", :size=>"643 MB", :url=>"https://s3.amazonaws.com/aact-prod/csv_pipe_exports/20170416_pipe-delimited-export"}, {:name=>"20170218_pipe-delimited-export", :date_created=>"02/18/2017", :size=>"620 MB", :url=>"https://s3.amazonaws.com/aact-prod/csv_pipe_exports/20170218_pipe-delimited-export"}, {:name=>"20170201_pipe-delimited-export", :date_created=>"02/01/2017", :size=>"615 MB", :url=>"https://s3.amazonaws.com/aact-prod/csv_pipe_exports/20170201_pipe-delimited-export"}, {:name=>"20170105_pipe-delimited-export", :date_created=>"01/05/2017", :size=>"606 MB", :url=>"https://s3.amazonaws.com/aact-prod/csv_pipe_exports/20170105_pipe-delimited-export"}]
Downloadable File Date Created Size
20170906_pipe-delimited-export 09/06/2017 695 MB
20170811_pipe-delimited-export 08/11/2017 679 MB
20170618_pipe-delimited-export 06/18/2017 665 MB
20170508_pipe-delimited-export 05/08/2017 650 MB
20170416_pipe-delimited-export 04/16/2017 643 MB
20170218_pipe-delimited-export 02/18/2017 620 MB
20170201_pipe-delimited-export 02/01/2017 615 MB
20170105_pipe-delimited-export 01/05/2017 606 MB

2

Extract Contents of Zip File

In Windows, this can be done by right clicking on the file and selecting ‘Extract All…’ from the menu. You will be given the opportunity to choose or create a destination folder to contain the extracted text files. Once the file extraction has completed your destination folder will contain the extracted text files. Files are named according to the database table to which they correspond, for example studies.txt contains the records and variables from the studies database table.

3

Access Content with Favorite Analysis Tool

The text files can be read with many software tools. The files can even be opened in MS Excel (specify the pipe character as delimiter), however some of the files are very large and this is not recommended. Tips are provided for SAS and R software.