Do File / import excel

[CAPM]

Hey guys,

i am new to Stata and currently wrinting a Do-File to transform Excel files in the Stata format.

The code I am using is the following:

set more off
global workdir ""
cd "$workdir"
capture mkdir "$workdir/index dta"
capture mkdir "$workdir/index dta format"
cd "$workdir/index xls/"
! dir *.xlsx /b >"0filelistxls.txt"
file open myfile using "0filelistxls.txt", read
file read myfile line
while r(eof)==0 {
clear
import excel `line', firstrow
save "$workdir/index dta/`line'.dta", replace
file read myfile line
}
file close myfile
erase "$workdir/index xls/0filelistxls.txt"

It works without receiving an error message from Stata. However, the resulting dta file is not saved.

I would be very grateful for help. Thanks a lot!

[Mitch Abdon]

checkout Nick Cox's -fs- that lists files in a directory and stores them in r(files). your code would then look like:

* assuming the files are in your current directory
fs *.xlsx
foreach f in `r(files)' {
    import excel `f', clear firstrow
    loc newf = subinstr("`f'", ".xlsx", "", 1)
    save `newf'.dta, replace
}

best,
mitch