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