How to read Excel files from Stata

By Marco Sandri


Create an Open Database Connectivity (ODBC) connection on Windows.
- Control Panel
- 32bit ODBC (Windows 95 or 98) or ODBC (Windows NT or 2000)
- System DSN
- Add
- Select the "Microsoft Excel Driver"
- Select the Excel file where you have your data (in our example, auto.xls)
- Specify a name for your source (for example, MyData)
- Finish

Loading data from an Excel file to Stata
The Stata command is:
odbc load, table("TableA$") dsn("MyData") clear
compress


Warning.
The name of columns (variables) in the Excel file should contain alphanumeric characters only.
For example, avoid the symbol % in the name of variables, otherwise Stata use a different name.
Suppose that the name of a variabile in the Excel file is tdx%. When loading the file, Stata writes:
tdx% invalid name
- converted tdx% to var16


Sometimes, when reading Excel files in Stata, you find additional empty variables
(named for example F12, F13, etc...)
Probably, you write something inside some cells of these columns of the Excel file
and subsequently you cancel it.
The best thing is to select in Excel these columns and to eliminate/remove them.
(Cancel is not sufficient).


Send comments and suggestions to Marco Sandri
Last update: June 6th, 2008