import many sheets from an excel file into Stata? xls2dta or others?

[River Huang]

Dear All, I have an "Controls.xlsx", in which, there are many sheets (names = 2020, 2019, ..., 2009) with same variables. I know that I can use

cd "E:\@CIE\"

forvalues i = 2009(1)2020 {
  import excel "raw\Controls.xlsx", sheet("`i'") firstrow case(lower) clear
  ren 期刊名 Journal
  gen year = `i'
  save "dta\Controls_`i'.dta", replace
}  

use "dta\Controls_2009.dta", clear
forvalues i = 2010(1)2020 {
    append using "dta\Controls_`i'.dta"
}

to import the data in Stata format. I wonder if there are alternative ways (say, xls2dta or others) ways to do so. Thanks.

[daniel klein]

With xls2dta (SSC),

xls2dta , clear sheets(2009/2020) generate(fn year) ///
          importopts(firstrow case(lower)) :        ///
          append using "E:\@CIE\raw\Controls.xlsx"

rename 期刊名 Journal
drop fn
destring year , replace

should produce the same results as the code in #1. I find the code in #1 easier to read and easier to follow. I would probably rename once after the loops(s) but that is a matter of personal preference.

[River Huang]

Dear daniel, Thanks for for the suggestion. I will give it a try ASAP.