Stata: looping and appending

Question

I have a situation where I need to import all the files in a directory and append them. My code is this

local files : dir "C:Usersxx" files "*.xls"

local n: word count `files'
tokenize ``files''

cd "C:Usersxx"

forval  k =1/`n'{

foreach file in `files' {
    import excel "`file'", sheet("Time Sheet") clear

drop in 3

if `k' == 1 {
    di in red `k'
        save "C:Usersxxmaster.dta", replace
    }
    else {
    append using "C:Usersxxmaster.dta"
    }
    save "C:Usersxxmaster.dta", replace
}
}

However when I use this code it seems to run an extra loop (* forval k =1/`n'*) that create duplicate entries. I cannot get rid of that code since I need it for the append command. I was wondering whether there is a way to mitigate this problem.

Answer 1

The double loop is causing the problem:

local files : dir "D:/Datos/rferrer/Desktop/statatemps" files "test*.xls"

cd "D:/Datos/rferrer/Desktop/statatemps"

local counter = 1
foreach file in `files' {

    import excel "`file'", sheet("Hoja1") firstrow clear

    if `counter' == 1 {
        di in red `counter'
        save "D:/Datos/rferrer/Desktop/statatemps/master.dta", replace
    }
    else {
        append using "D:/Datos/rferrer/Desktop/statatemps/master.dta"
        save "D:/Datos/rferrer/Desktop/statatemps/master.dta", replace
    }

    local counter = 0
}

list

You don't use the tokens created by tokenize, so you can drop it.

Shorter would be:

clear
set more off

local pathdir "D:/Datos/rferrer/Desktop/statatemps"

local files : dir "`pathdir'" files "test*.xls"

save "`pathdir'/master.dta", emptyok replace
foreach file in `files' {

    import excel "`file'", sheet("Hoja1") firstrow clear
    append using "`pathdir'/master.dta"
    save "`pathdir'/master.dta", replace

}

list

You might want to read help quotes if for some reason you have "weird" file names (and because it's a good read anyway).