Q. Downloading and Aggregating Large Files from TranStats using Microsoft Access


Downloading and Aggregating Large Files from TranStats using Microsoft Access

Many downloads of airline Origin and Destination Survey and on-time data in TranStats are more than the number of rows allowed by older versions of Microsoft Excel. If you open the file in Excel, an error message will say "File not loaded completely." You will need to extract the file to your computer and open it in Microsoft Access (or similar program) to work with the data. You may also want to use Access queries to aggregate data.

Here are the instructions for Access 2010.

  • When you open your zip program, right-click on file and click "extract" (select a convenient location)
  • Open MS Access
  • Select File - Open
  • Change file type to "All files"
  • Find the file you just extracted, select, click "Open"
  • Link Text Wizard will open
  • Click "next"
  • Check the box next to "first row contains field names"
  • Click "next"
  • Click "next" again
  • Change file name if desired
  • Click "finish"
  • Click "OK"
  • Double-click on file you just created


To create a query (for example, to get just one row for each origin/destination pair):


  • Go to Create -> Query Wizard
  • Simple Query Wizard, click "Ok"
  • Add all the variables you want to add by clicking the arrow to move them to the selected fields box
  • Click "next"
  • Click the button next to "Summary"
  • Click "Summary options"
  • Click box under SUM for each of the variables that you like to add up (e.g. departures or pax)
  • Click "Ok"
  • Click "next"
  • Rename the file if desired
  • Finish (open the query to view selection)
  • Filter as needed

Note: If you are trying to calculate RPM's, ASM's, etc., you can't aggregate or you will get a total that's too large.

To further reduce the size of the file, filter data by right clicking on a cell that meets your criteria and clicking "Filter by selection" (or "Filter Excluding Selection" to eliminate a variable you don't want). For example, if you want passengers departing SFO only, right click on "SFO" in the Origin column and filter by that selection. Add more filters to make the set smaller

Copy that smaller set of rows to Excel to sort and sum.

For more information about accessing BTS airline traffic statistics, click here.

  • Last Updated Dec 14, 2016
  • Views 6
  • Answered By David Martin

FAQ Actions

Was this helpful? 0   0

Contact Us

Starting Friday, September 22nd the "Search Our Collection" function at www.ntl.bts.gov will not be available due to server maintenance. We expect the search function to be back up by Tuesday, September 26th.

How to Research During the Outage

  • The "Submit a Question" and Reference Librarian phone number below will remain open and operational¬†

  • ROSA P, our new public repository, will be available in beta version for searching