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

SAMHDA produces a tab-delimited ASCII data file (*.tsv) that can be used to import data into Excel. An example of a tab-delimited ASCII data file name is "34481-0001-Data.tsv", which can be downloaded for the National Survey on Drug Use and Health (NSDUH), 2011 study.

NOTE: An error will occur if you attempt to read in a data file that exceeds Excel's maximum row and column limits. Prior to Excel 2007, the maximum number of rows and columns in a single spreadsheet could not exceed 65,536 rows and 256 columns. From Excel 2007 through Excel 2013, the number of rows and columns increased to 1,048,576 rows by 16,384 columns.

To import a tab-delimited ASCII data file into Excel, perform the following steps:

  1. Download the tab-delimited ASCII data file (*.tsv) from the SAMHDA site.
  2. Most of the files downloaded from the SAMHDA site are compressed, so decompress the files using decompression software, such as WinZip.
  3. Open the tab-delimited ASCII data file in Excel using an "Open file" dialog box. Excel's Text Import Wizard will open.
  4. In the Text Import Wizard, perform the following steps:
    1. Confirm that the "Delimited" checkbox is selected and that the "Start import at row" value is set to 1. Click "Next."
    2. Select "Tab" in the "Delimiters" option box. Click "Next."
    3. Leave all columns set to "General." SAMHDA studies do not contain string or date variables. Click "Finish."
  5. Review the imported data file. Row 1 will contain the names of the variables. Column A will be the CASEID variable.
  6. To confirm that the import worked properly, scroll across and down to check the number of variables and cases imported. Compare these numbers against those provided by SAMHDA in the file manifest included in your download.