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.
1

Download Zip File Containing Pipe-Delimited Files

Current Month's Daily Copies

[{:name=>"20210514_pipe-delimited-export.zip", :date_created=>"05/14/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210514_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210513_pipe-delimited-export.zip", :date_created=>"05/13/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210513_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210512_pipe-delimited-export.zip", :date_created=>"05/12/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210512_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210511_pipe-delimited-export.zip", :date_created=>"05/11/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210511_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210510_pipe-delimited-export.zip", :date_created=>"05/10/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210510_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210509_pipe-delimited-export.zip", :date_created=>"05/09/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210509_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210508_pipe-delimited-export.zip", :date_created=>"05/08/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210508_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210507_pipe-delimited-export.zip", :date_created=>"05/07/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210507_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210506_pipe-delimited-export.zip", :date_created=>"05/06/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210506_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210505_pipe-delimited-export.zip", :date_created=>"05/05/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210505_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210504_pipe-delimited-export.zip", :date_created=>"05/04/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210504_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210502_pipe-delimited-export.zip", :date_created=>"05/02/2021", :size=>"1.24 GB", :url=>"/static/exported_files/daily/20210502_pipe-delimited-export.zip", :time_created=>nil}]
Downloadable File Date File Was Created Size
20210514_pipe-delimited-export.zip 05/14/2021 1.24 GB
20210513_pipe-delimited-export.zip 05/13/2021 1.24 GB
20210512_pipe-delimited-export.zip 05/12/2021 1.24 GB
20210511_pipe-delimited-export.zip 05/11/2021 1.24 GB
20210510_pipe-delimited-export.zip 05/10/2021 1.24 GB
20210509_pipe-delimited-export.zip 05/09/2021 1.24 GB
20210508_pipe-delimited-export.zip 05/08/2021 1.24 GB
20210507_pipe-delimited-export.zip 05/07/2021 1.24 GB
20210506_pipe-delimited-export.zip 05/06/2021 1.24 GB
20210505_pipe-delimited-export.zip 05/05/2021 1.24 GB
20210504_pipe-delimited-export.zip 05/04/2021 1.24 GB
20210502_pipe-delimited-export.zip 05/02/2021 1.24 GB

Monthly Archives

[{:name=>"20210501_pipe-delimited-export.zip", :date_created=>"05/01/2021", :size=>"1.24 GB", :url=>"/static/exported_files/monthly/20210501_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210401_pipe-delimited-export.zip", :date_created=>"04/01/2021", :size=>"1.15 GB", :url=>"/static/exported_files/monthly/20210401_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210301_pipe-delimited-export.zip", :date_created=>"03/01/2021", :size=>"1.13 GB", :url=>"/static/exported_files/monthly/20210301_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210201_pipe-delimited-export.zip", :date_created=>"02/01/2021", :size=>"1.11 GB", :url=>"/static/exported_files/monthly/20210201_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20210101_pipe-delimited-export.zip", :date_created=>"01/01/2021", :size=>"1.1 GB", :url=>"/static/exported_files/monthly/20210101_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20201201_pipe-delimited-export.zip", :date_created=>"12/01/2020", :size=>"1.09 GB", :url=>"/static/exported_files/monthly/20201201_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20201101_pipe-delimited-export.zip", :date_created=>"11/01/2020", :size=>"1.08 GB", :url=>"/static/exported_files/monthly/20201101_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20201001_pipe-delimited-export.zip", :date_created=>"10/01/2020", :size=>"1.07 GB", :url=>"/static/exported_files/monthly/20201001_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20200901_pipe-delimited-export.zip", :date_created=>"09/01/2020", :size=>"1.05 GB", :url=>"/static/exported_files/monthly/20200901_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20200801_pipe-delimited-export.zip", :date_created=>"08/01/2020", :size=>"1.04 GB", :url=>"/static/exported_files/monthly/20200801_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20200701_pipe-delimited-export.zip", :date_created=>"07/01/2020", :size=>"1.03 GB", :url=>"/static/exported_files/monthly/20200701_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20200601_pipe-delimited-export.zip", :date_created=>"06/01/2020", :size=>"1.02 GB", :url=>"/static/exported_files/monthly/20200601_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20200501_pipe-delimited-export.zip", :date_created=>"05/01/2020", :size=>"1.01 GB", :url=>"/static/exported_files/monthly/20200501_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20200401_pipe-delimited-export.zip", :date_created=>"04/01/2020", :size=>"1020 MB", :url=>"/static/exported_files/monthly/20200401_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20200301_pipe-delimited-export.zip", :date_created=>"03/01/2020", :size=>"1010 MB", :url=>"/static/exported_files/monthly/20200301_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20200201_pipe-delimited-export.zip", :date_created=>"02/01/2020", :size=>"997 MB", :url=>"/static/exported_files/monthly/20200201_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20200101_pipe-delimited-export.zip", :date_created=>"01/01/2020", :size=>"985 MB", :url=>"/static/exported_files/monthly/20200101_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20191201_pipe-delimited-export.zip", :date_created=>"12/01/2019", :size=>"973 MB", :url=>"/static/exported_files/monthly/20191201_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20191101_pipe-delimited-export.zip", :date_created=>"11/01/2019", :size=>"965 MB", :url=>"/static/exported_files/monthly/20191101_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20191001_pipe-delimited-export.zip", :date_created=>"10/01/2019", :size=>"953 MB", :url=>"/static/exported_files/monthly/20191001_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20190901_pipe-delimited-export.zip", :date_created=>"09/01/2019", :size=>"941 MB", :url=>"/static/exported_files/monthly/20190901_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20190801_pipe-delimited-export.zip", :date_created=>"08/01/2019", :size=>"930 MB", :url=>"/static/exported_files/monthly/20190801_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20190701_pipe-delimited-export.zip", :date_created=>"07/01/2019", :size=>"918 MB", :url=>"/static/exported_files/monthly/20190701_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20190601_pipe-delimited-export.zip", :date_created=>"06/01/2019", :size=>"907 MB", :url=>"/static/exported_files/monthly/20190601_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20190501_pipe-delimited-export.zip", :date_created=>"05/01/2019", :size=>"895 MB", :url=>"/static/exported_files/monthly/20190501_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20190401_pipe-delimited-export.zip", :date_created=>"04/01/2019", :size=>"883 MB", :url=>"/static/exported_files/monthly/20190401_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20190301_pipe-delimited-export.zip", :date_created=>"03/01/2019", :size=>"871 MB", :url=>"/static/exported_files/monthly/20190301_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20190201_pipe-delimited-export.zip", :date_created=>"02/01/2019", :size=>"860 MB", :url=>"/static/exported_files/monthly/20190201_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20190101_pipe-delimited-export.zip", :date_created=>"01/01/2019", :size=>"849 MB", :url=>"/static/exported_files/monthly/20190101_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20181201_pipe-delimited-export.zip", :date_created=>"12/01/2018", :size=>"839 MB", :url=>"/static/exported_files/monthly/20181201_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20181101_pipe-delimited-export.zip", :date_created=>"11/01/2018", :size=>"829 MB", :url=>"/static/exported_files/monthly/20181101_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20181001_pipe-delimited-export.zip", :date_created=>"10/01/2018", :size=>"817 MB", :url=>"/static/exported_files/monthly/20181001_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20180901_pipe-delimited-export.zip", :date_created=>"09/01/2018", :size=>"808 MB", :url=>"/static/exported_files/monthly/20180901_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20180801_pipe-delimited-export.zip", :date_created=>"08/01/2018", :size=>"795 MB", :url=>"/static/exported_files/monthly/20180801_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20180701_pipe-delimited-export.zip", :date_created=>"07/01/2018", :size=>"785 MB", :url=>"/static/exported_files/monthly/20180701_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20180601_pipe-delimited-export.zip", :date_created=>"06/01/2018", :size=>"775 MB", :url=>"/static/exported_files/monthly/20180601_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20180501_pipe-delimited-export.zip", :date_created=>"05/01/2018", :size=>"768 MB", :url=>"/static/exported_files/monthly/20180501_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20180401_pipe-delimited-export.zip", :date_created=>"04/01/2018", :size=>"758 MB", :url=>"/static/exported_files/monthly/20180401_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20180314_pipe-delimited-export.zip", :date_created=>"03/14/2018", :size=>"754 MB", :url=>"/static/exported_files/monthly/20180314_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20180201_pipe-delimited-export.zip", :date_created=>"02/01/2018", :size=>"739 MB", :url=>"/static/exported_files/monthly/20180201_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20180111_pipe-delimited-export.zip", :date_created=>"01/11/2018", :size=>"734 MB", :url=>"/static/exported_files/monthly/20180111_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20171217_pipe-delimited-export.zip", :date_created=>"12/17/2017", :size=>"727 MB", :url=>"/static/exported_files/monthly/20171217_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20171102_pipe-delimited-export.zip", :date_created=>"11/02/2017", :size=>"711 MB", :url=>"/static/exported_files/monthly/20171102_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20171017_pipe-delimited-export.zip", :date_created=>"10/17/2017", :size=>"706 MB", :url=>"/static/exported_files/monthly/20171017_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20170906_pipe-delimited-export.zip", :date_created=>"09/06/2017", :size=>"695 MB", :url=>"/static/exported_files/monthly/20170906_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20170811_pipe-delimited-export.zip", :date_created=>"08/11/2017", :size=>"679 MB", :url=>"/static/exported_files/monthly/20170811_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20170703_pipe-delimited-export.zip", :date_created=>"07/03/2017", :size=>"669 MB", :url=>"/static/exported_files/monthly/20170703_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20170613_pipe-delimited_export.zip", :date_created=>"06/13/2017", :size=>"658 MB", :url=>"/static/exported_files/monthly/20170613_pipe-delimited_export.zip", :time_created=>nil}, {:name=>"20170502_pipe-delimited-export.zip", :date_created=>"05/02/2017", :size=>"647 MB", :url=>"/static/exported_files/monthly/20170502_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20170403_pipe-delimited-export.zip", :date_created=>"04/03/2017", :size=>"630 MB", :url=>"/static/exported_files/monthly/20170403_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20170309_pipe-delimited-export.zip", :date_created=>"03/09/2017", :size=>"625 MB", :url=>"/static/exported_files/monthly/20170309_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20170218_pipe-delimited-export.zip", :date_created=>"02/18/2017", :size=>"622 MB", :url=>"/static/exported_files/monthly/20170218_pipe-delimited-export.zip", :time_created=>nil}, {:name=>"20170105_pipe-delimited-export.zip", :date_created=>"01/05/2017", :size=>"608 MB", :url=>"/static/exported_files/monthly/20170105_pipe-delimited-export.zip", :time_created=>nil}]
Downloadable File Date File Was Created Size
20210501_pipe-delimited-export.zip 05/01/2021 1.24 GB
20210401_pipe-delimited-export.zip 04/01/2021 1.15 GB
20210301_pipe-delimited-export.zip 03/01/2021 1.13 GB
20210201_pipe-delimited-export.zip 02/01/2021 1.11 GB
20210101_pipe-delimited-export.zip 01/01/2021 1.1 GB
20201201_pipe-delimited-export.zip 12/01/2020 1.09 GB
20201101_pipe-delimited-export.zip 11/01/2020 1.08 GB
20201001_pipe-delimited-export.zip 10/01/2020 1.07 GB
20200901_pipe-delimited-export.zip 09/01/2020 1.05 GB
20200801_pipe-delimited-export.zip 08/01/2020 1.04 GB
20200701_pipe-delimited-export.zip 07/01/2020 1.03 GB
20200601_pipe-delimited-export.zip 06/01/2020 1.02 GB
20200501_pipe-delimited-export.zip 05/01/2020 1.01 GB
20200401_pipe-delimited-export.zip 04/01/2020 1020 MB
20200301_pipe-delimited-export.zip 03/01/2020 1010 MB
20200201_pipe-delimited-export.zip 02/01/2020 997 MB
20200101_pipe-delimited-export.zip 01/01/2020 985 MB
20191201_pipe-delimited-export.zip 12/01/2019 973 MB
20191101_pipe-delimited-export.zip 11/01/2019 965 MB
20191001_pipe-delimited-export.zip 10/01/2019 953 MB
20190901_pipe-delimited-export.zip 09/01/2019 941 MB
20190801_pipe-delimited-export.zip 08/01/2019 930 MB
20190701_pipe-delimited-export.zip 07/01/2019 918 MB
20190601_pipe-delimited-export.zip 06/01/2019 907 MB
20190501_pipe-delimited-export.zip 05/01/2019 895 MB
20190401_pipe-delimited-export.zip 04/01/2019 883 MB
20190301_pipe-delimited-export.zip 03/01/2019 871 MB
20190201_pipe-delimited-export.zip 02/01/2019 860 MB
20190101_pipe-delimited-export.zip 01/01/2019 849 MB
20181201_pipe-delimited-export.zip 12/01/2018 839 MB
20181101_pipe-delimited-export.zip 11/01/2018 829 MB
20181001_pipe-delimited-export.zip 10/01/2018 817 MB
20180901_pipe-delimited-export.zip 09/01/2018 808 MB
20180801_pipe-delimited-export.zip 08/01/2018 795 MB
20180701_pipe-delimited-export.zip 07/01/2018 785 MB
20180601_pipe-delimited-export.zip 06/01/2018 775 MB
20180501_pipe-delimited-export.zip 05/01/2018 768 MB
20180401_pipe-delimited-export.zip 04/01/2018 758 MB
20180314_pipe-delimited-export.zip 03/14/2018 754 MB
20180201_pipe-delimited-export.zip 02/01/2018 739 MB
20180111_pipe-delimited-export.zip 01/11/2018 734 MB
20171217_pipe-delimited-export.zip 12/17/2017 727 MB
20171102_pipe-delimited-export.zip 11/02/2017 711 MB
20171017_pipe-delimited-export.zip 10/17/2017 706 MB
20170906_pipe-delimited-export.zip 09/06/2017 695 MB
20170811_pipe-delimited-export.zip 08/11/2017 679 MB
20170703_pipe-delimited-export.zip 07/03/2017 669 MB
20170613_pipe-delimited_export.zip 06/13/2017 658 MB
20170502_pipe-delimited-export.zip 05/02/2017 647 MB
20170403_pipe-delimited-export.zip 04/03/2017 630 MB
20170309_pipe-delimited-export.zip 03/09/2017 625 MB
20170218_pipe-delimited-export.zip 02/18/2017 622 MB
20170105_pipe-delimited-export.zip 01/05/2017 608 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 flat files. Once the file extraction has completed your destination folder will contain the extracted flat 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 flat 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.

Using R

Click here for hints about how to use R with the Pipe-delimited Files

Using SAS

Click here for hints about how to use SAS with the Pipe-delimited Files

Connect
Download
Learn
Shared Data