How to Use Access to Create Access or Excel Files with Less Than 65536 Observations from ASCII Data Files

Microsoft Excel 97/2000/XP is limited to 65536 observations and 256 columns of data. (Versions of Excel 2003 on have a limit of 1,048,576 rows. Access 2007 on has a 2 Gb limit). Large datasets are best analyzed with statistical software designed to handle big datasets such as Stata or SPSS, but especially SAS. Computer languages such as Perl or C++ can be used as well. But the price or learning curve may be too high if only a small number of observations or variables are necessary and a familiar software package such as Excel of Access will suffice. Jean Roth created the directions below for extracting data from large databases into Access and Excel, familiar and readily available software packages.

Microsoft also offers a sample macro for opening the long file and automatically breaking the text into multiple worksheets.

Read the Large Data File

  • Start Access (this example uses Access 2000)
  • Choose 'File', 'Open', and select the ASCII data file
  • Choose 'Delimited' for delimited data, such as comma-separated variables or tabs and select the appropriate delimiter, or
    choose 'Fixed Width' to select only specific columns from the data file
  • Check 'First Row Contains Field Names', if applicable
  • 'Skip' undesired fields, if necessary, to select fewer than 256 Fields (Excel's limit)
  • Affirm that Access has assigned Data Types to the Fields correctly
  • Choose 'Next' to name the table and 'Finish'

    Extract a Subset of Data

  • Click the 'Queries' tab
  • Double-click 'Create Query in Design View'
  • Choose 'Add' to add the table and 'Close'
  • Double-click '*' to select all fields, or select only desired fields individually
  • If the dataset has more than 65536 observations, then make one or more subsets (if exporting to Excel)
  • Double-click on the subsetting field
  • Under 'Criteria', enter criteria, such as >1999 for a year variable or 'MA' for a state variable (Search on 'criteria' in Access' Help to see more examples of criteria)
  • Uncheck the "Show" box on the criteria variable(s). This will prevent a 'Cannot define field more than once' error when exporting data.
  • Click "!" to run the query
  • When complete, the number of records will be displayed in the gutter labeled "Record:". Large files will take some time.
  • If the number of records is not less than 65536 and exporting to Excel, add more criteria:
    Click on the 'View' button (upper lefthand corner, below 'File', has a triangle, ruler, and pencil.) to return to 'Design View'.
    Add sufficient criteria to bring the number of observation below 65536.
  • Choose 'File' and 'Save' to name and save the Query
  • Choose 'File', 'Export' and 'Microsoft Excel 97/2000' under 'Save as type:' to export the Query as an Excel file

    Last Update: September 9, 2009 Created by Jean Roth June 11, 2002