Why Stata’s `import excel` Is the Right Choice for Quick Data Loads – and When to Avoid It

Problem: One‑off vs. Repeatable Data Loads

Data analysts often face a simple decision: do they export a spreadsheet to CSV and then use insheet, or do they load the workbook directly with Stata’s import excel command? The choice hinges on speed, reproducibility, and the nature of the data. Below, I walk through a concrete example, highlight the practical trade‑offs, and give you a checklist for making the right decision.

Thesis: `import excel` is ideal for ad‑hoc, exploratory work but can be fragile in scripted pipelines.

When you’re in a notebook or a quick analysis session, import excel saves time and eliminates an intermediate file. In a production environment, however, the lack of explicit control over variable types and the dependence on the exact workbook layout can introduce subtle bugs. Understanding the command’s options and limitations lets you choose the right tool for the job.

Section 1: Getting Started with `import excel`

Check Your Stata Edition

Not all Stata versions support import excel equally. Run:

about

to confirm you’re on Stata/SE, MP, or Basic. The command is available in all editions, but row/column limits differ: Stata/SE can read up to 10,000 rows by default, while Small Stata caps at 2,000. Adjust maxrows() if you need more.

Prepare a Sample Workbook

Create a simple sample.xlsx with the following layout:

• Sheet name: Data
• Headers in row 1: ID, Name, Score, ExamDate
• Data rows 2–5 with mixed types (numeric, string, date)

Save the file in a known directory, e.g., C:\Data\sample.xlsx on Windows or /Users/you/Data/sample.xlsx on macOS.

Run the Import

Open Stata and execute:

import excel using "C:\\Data\\sample.xlsx", sheet("Data") firstrow clear

Explanation of options:

• using "…" – path to the workbook.
• sheet("Data") – read from the named sheet.
• firstrow – treat the first row as variable names.
• clear – drop any data already in memory.

After the command runs, Stata will display a summary such as:

  4 vars   4 obs

Use describe and list to verify the import:

describe
list

Check that Score is numeric, Name is string, and ExamDate is numeric (Excel serial date). Convert dates with:

gen exam_date = date(ExamDate, "YMD")
format exam_date %td

Section 2: Handling Mixed Data Types

Excel columns can contain both numbers and text. import excel will promote the entire column to string if any cell is non‑numeric. If you want to preserve numeric values, pre‑clean the sheet or use cellrange() to import only numeric columns. Example:

import excel using "C:\\Data\\sample.xlsx", sheet("Data") cellrange(A1:C4) firstrow clear

This limits the import to columns A–C (ID, Name, Score) and rows 1–4, avoiding the date column entirely.

Section 3: Subsetting Large Workbooks

When dealing with multi‑sheet reports or millions of rows, you can limit the import:

import excel using "C:\\Data\\big.xlsx", sheet("Sheet2") cellrange("B5:G5000") firstrow clear

Here cellrange() tells Stata to read only the specified rectangle, reducing memory usage and speeding up the load. Verify the subset by checking r(N) (the number of observations imported) and comparing it to the expected count.

Section 4: Limitations and Trade‑offs

• Header Assumptions: If the first row contains data rather than variable names, omit firstrow. Otherwise, the first row becomes a variable name and the data shift down.
• String Truncation: Stata truncates string variables to the longest observed length. If you need longer strings, use stringvar() or pre‑define variable types with varlabels.
• Date Conversion: Excel stores dates as serial numbers. After import, use date() or daily() to convert to Stata dates.
• Edition Limits: Small Stata supports fewer rows and columns. Verify about before importing large files.
• Reproducibility: In automated pipelines, reliance on the workbook layout can break if column order changes. CSV export (export excel, firstrow(variables)) produces a flat file that is less sensitive to layout shifts.

Section 5: Practical Checklist for Decision‑Making

1. Are you loading data once for exploration? import excel is fastest.
2. Will the data be re‑imported in a production script? Consider exporting to CSV first.
3. Does the workbook contain mixed‑type columns? Pre‑clean or use cellrange() to avoid unwanted string promotion.
4. Is the dataset large? Use maxrows() and cellrange() to subset.
5. Do you need to preserve exact column order? Verify with describe after each import.

Conclusion: Use the Right Tool for the Right Stage

For quick, one‑off analyses, import excel is a powerful shortcut that eliminates the need for a CSV intermediary. It reads worksheets directly, handles headers, and respects cell ranges. In contrast, for repeatable, production‑grade workflows, exporting to CSV (or using an ODBC driver) offers more explicit control over variable types, eliminates hidden dependencies on workbook layout, and makes the process easier to audit.

Always test your import on a small sample, confirm the variable types with describe, and check the observation count. Once you’re comfortable, you can embed the command in a do‑file or macro, but keep an eye on the limitations noted above. Happy coding!