[Jurre Adrichem]

Hi,
I need to append 60-ish Excel-files with multiple sheets into one .dta-file. I have managed to append them for the first sheet in each Excel-file, however, I do not understand/manage to figure out how I can generate a loop so Stata includes all sheets in each Excel-file. Can anyone help me? Below is the code I have right now:

cd: dir/to/my/folder

local folder: dir "./" files "*.xlsx"

display `folder'

clear
tempfile new_data

/*capture frame drop import*/
frame create import

foreach file in `folder'{
frame import{
import excel "`file'", clear

save "`new_data'", replace
}
append using "`new_data'"
}

Thank in advance!

[Daniel Schaefer]

You can do it with this pattern.

import excel test.xlsx, describe
local sheetnumber = r(N_worksheet)
forv i = 1/`sheetnumber' {
    local sheetname =  r(worksheet_`i')
    display "`sheetname'"
}

So for your code, something along these lines should work.

cd: dir/to/my/folder
local folder: dir "./" files "*.xlsx"
display `folder'
clear
tempfile new_data
/*capture frame drop import*/
frame create import
foreach file in `folder'{
    import excel "`file'", describe
    local sheetnumber = r(N_worksheet)
    forv i = 1/`sheetnumber' {
        local sheetname = r(worksheet_`i')
        frame import{
            import excel "`file'", sheet("`sheetname'") clear
            save "`new_data'", replace
        }
    }
    append using "`new_data'"
}

Edit: fixed small mistake introduced while debugging. worksheet_1 changed to worksheet_`i' in second code block.

[daniel klein]

At the risk of repeating myself, the easiest way, if you don't mind using third-party software, seems to be

xls2dta , clear allsheets : append using dir/to/my/folder

xls2dta is from SSC.


Understanding the basic principles is still a good idea if only to customize the process beyond what xls2dta already implements.

[Jurre Adrichem]

The code works. Thank you Daniel Klein!