insheeting excel files using loop and extracting filename as variable value

[Minnie Smith]

Hello,

I am trying to use insheet multiple .xlxs files and then extract filename. This is my code:

foreach x in country1 country2 country3{

local files : dir "${Data}/Raw/`x'" files "*.xlsx"

foreach f of local files {
import excel "`f'", sheet("Sheet1") cellrange(C11) firstrow
    local state=substr("`f'", 1, strlen("`f'") - 5)
    gen state = "`state'"
    gen country = "`x'"
    save "${Data}/Clean/`x'/`state'", replace
    }

}

However, I get the error that filename1.xlxs not found where filename1 is one of my files. I am not sure what is the issue. Any help would be appreciated! Please also let me know if my code to extract the filename should work.

Thank you.

[daniel klein]

Extended function dir will not store the full path but only the filenames. You have to make sure to change your directory to whatever is stored in ${Data}/Raw before proceeding.

Also see xls2dta (SSC) for related problems. For example,

xls2dta , generate(filename) : import excel "${Data}/Raw" , sheet("Sheet1") cellrange(C11) firstrow

will convert all .xlsx (and .xls) files in ${Data}/Raw and store the filename in variable filename.

Best
Daniel