Jun 17, 2009

How do I use Excel to import tab-delimited ASCII data?

SAMHDA produces and makes available for download ASCII data files in two formats. The first of these is a fixed-format data file (da99999-9999.txt) to be used in conjunction with a setup file for SAS, SPSS, or Stata. The second format is a tab-delimited data file (da99999-9999.tsv).

Note: The Import Wizard for SAS, SPSS, and Stata can read the tab-delimited file into the statistical package. However, if using one of these statistical packages SAMHDA encourages you to use the fixed-format (.txt) data file to read in the data with its' accompanying setup file.

Warning: An error will occur if you try to read in a data file with more than 65,536 cases or 256 variables. These are the maximum limits that an Excel spreadsheet can handle.


1. Download the tab-delimited ASCII data file from the SAMHDA Web site.

2. Most of the files downloaded from the ICPSR Web site will be compressed. You will have to decompress the files using WinZip or other decompression software. More information about decompressing files can be found at the help page, How do I decompress the files I download from your site?

3. Open Excel for Windows.

Screen Shot

4. Open the tab-delimited ASCII data file.

Screen Shot

  • Click on File and then Open to get an Open File dialog box.
  • At the top of the box, where it says Look In, choose the path where the tab-delimited ASCII data file is located.
  • At the bottom of the box, set Files of Type to All Files.
  • You will then see a list of all files in the directory you selected. Either double-click on the .tsv file or click once on the name of your chosen file (the name will appear after File Name) and then click on Open.

5. This will open Excel's text Import Wizard Step 1 of 3.

Screen Shot

  • Make sure the button for Delimited is marked and the box for "Start import at row" is set to 1.
  • Then click on Next.

6. Go to Import Wizard Step 2 of 3.

Screen Shot

  • Select Tab in the Delimiters option box.
  • Then click on Next.

7. Go to Import Wizard Step 3 of 3.

Screen Shot

  • Leave every column set to General. You do not have to do anything in this step. SAMHDA studies do not contain string or date variables.
  • Then click on Finish.

8. Review imported data file.

Screen Shot

You now have completed importing the data file. Row 1 will contain the names of the variables. Column A will be the CASEID variable. To confirm the import worked properly scroll across and down to check on the number of variables and cases imported. Compare these numbers against those provided by SAMHDA in the file manifest. This file can be accessed by going to the bottom of the study's Description and Citation or Browse Documentation pages.

Creative Commons License This work is licensed under a Creative Commons Attribution-Noncommercial 3.0 United States License.