Importing multiple excel sheets into one Stata file

[Lea Birm]

Dear Stata users,

I have an excel spreadsheet with 3 sheets. Every sheet displays the date in the first column and then three price variables in the following columns. It looks the following way:

Sheet 1:

Date Price Overnight-Price Forecasted Price
2019-1 1,23 1,78 3,46
2019-2 1,44
...
...
...
2019-12 1,78

Sheet 2:
2020-1 1,79
2020-2 1,82
....
...
....


Sheet 3:
...

Now I want to append the data of sheet 3 and 2 to sheet 1 but unfortunately it is not working with my Stata code.
I tried it the following way but I do not know how to append it properly:

forvalues i = 1(1)3 {
import excel using "data.xlsx",
      sheet (Sheet`i') first row clear 
      if `i' ==1 {
      save Data2019, replace }
}
else {
       append }

Thank you very much in advance!

[Jared Greathouse]

Why do you need to specify the sheet? I'm not understanding.


Assuming all variable names are the same and there's only one sheet, you're better off saving them as tempfiles and appending them or saving them into separate data frames and appending them, the loop you've written looks too complicated.

For me to really help you, I need dataex examples of all 3 of your files. Reply back with those, and we'll get this appended.


EDIT: Nevermind i see why you needed to specify the sheets. Either way, still please provide a real example dataset, not by typing the variable names but with dataex

[Justin Niakamal]

You could also try Daniel Klein's xls2dta (SSC). In your case something like

xls2dta,  allsheets clear  import(firstrow): append using "data.xlsx"

Worked example

webuse "nlswork.dta", clear

forvalues x = 1/3 {
    preserve 
    keep if id == `x'
    export excel using "my_excel_file.xlsx", firstrow(variables) sheet("Sheet `x'") sheetreplace 
    restore
}

xls2dta,  allsheets clear  import(firstrow): append using "my_excel_file.xlsx" 
erase my_excel_file.xlsx

exit