Log In/Create Account

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.

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

Instructions

  1. Download a tab-delimited ASCII data file (*.tsv) from the SAMHDA site.
  2. Most of the files downloaded from the SAMHDA site are compressed. You will have to decompress the files using decompression software (e.g., WinZip). More information about decompressing files can be found in the FAQ: How do I decompress the files I download from your site?
  3. Open the tab-delimited ASCII data file in Excel using an Open file dialog box. This will open Excel’s text Import Wizard.
  4. In the Import Wizard, complete the following steps:
    1. Confirm that the button for Delimited is marked and the box for "Start import at row" is set to 1.
      Click on Next.
    2. Select Tab in the Delimiters option box.
      Click on Next.
    3. Leave all columns set to General. SAMHDA studies do not contain string or date variables.
      Click on Finish.
  5. Review the imported 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 the number of variables and cases imported. Compare these numbers against those provided by SAMHDA in the file manifest included in your download.