help xls2dta

Title

xls2dta -- Excel files to Stata datasets

Basic syntax overview

xls2dta : import excel
filename

xls2dta , {clear|save(filename)}
: append

xls2dta , {clear|save(filename)}
: merge mtype varlist

xls2dta , {clear|save(filename)}
: joinby

xls2dta : xeq command

xls2dta : {do | run}
dofile

xls2dta , eraseok : erase

Full syntax

Convert Excel files to Stata datasets

xls2dta
[, convert_options]
: import [excel]
[[extvarlist]
using] filename [,
import_excel_options]

Combine datasets

xls2dta
[, combine_options]
: combine_cmd
[using filename]
[, combine_cmd_options]

where filename is typically a directory and specifies one
or more Excel files to be converted to Stata datasets (see
filename).

combine_cmd is one of

append

merge mtype varlist

joinby [varlist]

and combine_cmd_options are
append_options,
merge_options or
joinby_options, respectively.

Execute commands on converted datasets

xls2dta
[, execute_options]
: xeq command
[; command [; ...]]

xls2dta
[, execute_options]
: {do | run}
dofile [arguments]
[, nostop]

Erase previously converted datasets

xls2dta , eraseok
: erase

convert options

save([directory] [, save_options]) save
converted Excel files in directory

sheets(sheetsspec [, not]) convert
worksheets sheetsspec

allsheets convert all worksheets

recursive[(maxdeep)] search
filename recursively

generate(newvar1 [newvar2]) create
variables with filename, sheetname

respectcase respect case in filename (Windows only)

nostop continue if import excel fails

combine options

clear replace data in memory

save(filename.dta [, save_options]) save
combined files as filename.dta

dta(numlist) combine
s(dta_#) only; not with using syntax

importopts(import_excel_options) options
for import excel; only with using syntax

convert options same as above; only with using syntax

one of clear or save() must be specified

execute options

dta(numlist) execute commands or
dofile for s(dta_#) only

noisily echo commands and output (xeq only)

save save unchanged datasets; seldom used

Description

xls2dta is a prefix command and converts multiple Excel
files and/or worksheets to Stata datasets. Names for Stata datasets are
derived from the names of Excel files and left behind in s().

Used with import excel, xls2dta converts all Excel files and/or
worksheets found in filename to Stata datasets. filename is
typically a directory and may be ./, meaning the current working
directory (). If no file extension is specified, xls2dta
converts all .xls and .xlsx files.

Used with append, merge or joinby, xls2dta combines
all previously converted datasets or, if using filename is
specified, all Excel files found in filename.

xls2dta : xeq and xls2dta : {do|run} execute the
specified commands or dofile on each previously converted dataset. Do
not specify use or save commands. Let xls2dta
load and save the modified datasets.

xls2dta , eraseok : erase erases previously converted
datasets. Option eraseok is required and files are
removed from disk, permanently.

Options for converting Excel files

save([directory] [, save_options])
specifies the directory where Stata datasets be stored. If not
specified, directory defaults to the current working
directory. The save_options are

replace allows Stata datasets to be replaced if they
already exist. Be warned: specifying replace will
usually overwrite more than one file if directory is
specified or implied.

save_options are options
allowed with save.

mkdir[(public)] creates new directory
directory on-the-fly. See mkdir.

sheets(sheetsspec [, not])
converts only worksheets sheetsspec to Stata datasets,
where sheetsspec is one of "pattern"
[...] or numlist. In pattern, wildcard
characters * and ? are allowed, where the former
means 0 or more characters and the latter means exactly one
character (see strmatch). In numlist, only
integer values greater 0 are allowed. Suboption not
converts all but the specified worksheets.

Excel worksheets are saved as
separate Stata datasets and named as
Excelfilename_worksheet#.dta.

allsheets converts all worksheets to Stata
datasets. allsheets is the same as sheets(*). See
above.

recursive[(maxdeep)] seraches filename
recursively, meaning all subdirectories are searched. This
option requires filelist (Picard, SSC) to be
installed. maxdeep specifies how many levels be
searched (cf. filelists maxdeep(#)
option).

generate(newvar1 [newvar2])
generates newvar1, holding the name of the Excel
file and, thus, marking the source of each observation.
If specified, newvar2 records the sheetname, additionally.
This option is espcially useful if datasets are to be appended,
because for each single Stata dataset both variables will be
constants.

respectcase specifies that case be respected in
filename. Uppercase characters in filename
imply respectcase. Option respectcase is
ignored if recursive is specified.

nostop prevents xls2dta from stopping if one of the
internal calls to import excel returns an error. This
option should rarely be specified.

Options for combining datasets

clear clears the data in memory and loads the combined dataset.

save(filename.dta [, save_options])
saves the combined files in filename. The save_options are the
same as described in Options for converting Excel files.

dta(numlist) combines the files, selected from s(). If
not specified, all s(dta_#) files are combined.

importopts(import_excel_options) are options
passed thru to import excel. This option may only be specified
with the using filename syntax.

convert_options are options for
converting Excel files. These options may only be specified with the
using filename syntax.

Options for executing commmands on converted datasets

dta(numlist)
executes commands or dofile on selected datasets only
(also see combine opions).

noisily echos commands and output and may only be specified
with xeq. Dafault is to execute the commands quietly.

save saves the datasets after commands or dofile were
executed even when the datasets have not been changed according
to c(changed). This option is seldom
used.

Old syntax

As of version 2.0.0, xls2dta has a new
syntax. Old syntax
continues to work.

Examples

Convert the first worksheet in all Excel files stored in
c:/my_xls_files to Stata datasets and save the files
in c:/my_dta_files.

. xls2dta , save(c:/my_dta_files) : import excel c:/my_xls_files

Append the Stata datasets converted above and save the resulting
dataset as c:/my_dta_files/complete.dta.

. xls2dta , save(c:/my_dta_files/complete.dta) : append

Merge all worksheets in c:/my_xlsx_files/foo.xlsx
to one Stata datasets and load this file. Perform a
one-to-one merge, using A as the key variable.

. xls2dta , clear allsheets : merge 1:1 A using c:/my_xlsx_files/foo.xlsx

Convert the first ten worksheets in any Excel file stored
in c:/my_xls_files or its subdirectories to Stata
datasets and save the files in the current working
directory.

. xls2dta , sheets(1/10) recursive : import excel using c:/my_xls_files

In each Stata dataset converted above, convert string variable
A to numeric variable numeric_A

. xls2dta : xeq destring A , generate(numeric_A)

Saved results

xls2dta, used with import excel, saves the following in
s():

Macros

s(cmd) xls2dta

s(n_dta) number of saved .dta files

s(dta_#) filename of .dta file #

Used with append, merge or joinby, xls2dta
saves in s():

Macros

s(cmd_dta) filename of combined .dta file

Author

Daniel Klein, University of Kassel, klein.daniel.81@gmail.com

Also see

Online: import excel, append, merge,
joinby, foreach

if installed: filelist, xls2stata