Importing multiple excel worksheets using a loop

[Jessica Hummel]

Hello everyone,

I have a single excel file that has multiple worksheets, the worksheets are named Country 1, Country 2, Country 3 etc. I want to be able to import all these worksheets and save each one as a data file using a loop. I'm capable of doing it individually but it makes my code very long. Also, how would I be able to merge all these dta files into one file afterwards?

Thanks for the help!

[Jared Greathouse]

If this were my problem, and I had sheets like "Sheet1, Sheet2..."

I'd do

clear
tempfile building
save `building', emptyok
forv i = 1/2 {


import delim "data_name.csv", ///
clear sheet(Sheet`i')

append using `temp`i''

save `"`building'"', replace


}

Note that this isn't tested and I'm not at my computer, so forgive any small syntax errors.


EDIT: As expected, others have ran into similar issues. Clyde Schechter uses a macro in his syntax to get the list of sheets and loop over those, which of course is ideal for practical purposes/generality.

[William Lisowski]

You asked how to merge these worksheets.

If you have the same variables for each country, then what you want to do is to use the append command to stack the datasets end-to-end into a single dataset, not the merge command, which does something much different,

I second the advice from the the thread linked in post #2 to investigate the xls2dta community contributed command available from SSC. That should take care of most of the work for you,

ssc describe xls2dta.