How can I upload an excel file with multiple sheets to stata?

[carmen]

Hi, how can I upload to stata an excel file(from mac) with multiple sheets?

Thanks in advance,

[Joe Canner]

Carmen,

You can import each sheet one at a time using the sheet() option for the import excel command. If you want to automate the process you can use a loop:

foreach sheet in "Sheet1" "Sheet2" ... {
  import excel using file.xls, sheet("`sheet'")
  save file_`sheet', replace
}

Regards,
Joe

[Joseph Coveney]

Doesn't import excel work on the Macintosh? I would imagine that if the worksheets are similarly arranged (same number of columns and same types of data in each), then you could set up a foreach loop with the names of the worksheets, and import each one in succession, saving to a tempfile and then import the next worksheet and append them. I believe that this general algorithm has been shown several times in the past. You might want to do a search of the list's archives to see the details.

[Joe Canner]

And if you really want to automate the process (i.e., without having to specify the name of each sheet):

import excel using file.xls, describe
forvalues sheet=1/`=r(N_worksheet)' {
  local sheetname=r(worksheet_`sheet')
  import excel using file_xls, sheet("`sheetname'")
  save "file_`sheetname'", replace
}

[daniel klein]

Also have a look at xls2dta from SSC, which is a wrapper for import excel that does exactly what has been suggested here.

The syntax could be something like

cd directoy_containing_xls_files
xls2dta ,allsheets

Best
Daniel

[Tobias Wendler]

Thank you guys, your suggestions were great. In my case the code by Joe did not work exactly as Stata said that data in memory would be lost.

With minor adjustment it worked:

 import excel using file.xls, describe
forvalues sheet=1/`=r(N_worksheet)' {  
local sheetname=r(worksheet_`sheet')  
import excel using file_xls, sheet("`sheetname'")  
save "file_`sheetname'", replace  
clear
}

[Bijesh Mishra]

I used code provided by Tobias and Joe. Both gave me an error "file_xls not found". I think this error is coming from
"import excel using file_xls, sheet("`sheetname'". I used original link to the data file, but does not work. How should I manipulate this section to make it work?

[Jason Pan]

I used code provided by Tobias and Joe. Both gave me an error "file_xls not found". I think this error is coming from
"import excel using file_xls, sheet("`sheetname'". I used original link to the data file, but does not work. How should I manipulate this section to make it work?

You need to replace file_xls with the Excel file name of yours. In addition, as a prerequisite for the codes to work, you will need to change the working directory in Stata to where your Excel file is located by using

cd "directory"

Other troubleshooting possibilities may include making sure whether your file is in .xls or .xlsx format; If the original link has spaces in name of file or folder, you need to put quotes to the full path such as


import excel "C:\path with spaces\excel file.xlsx" .

Hope that helps.

[Sanika Rr]

Hello all,

I am using Stata 16. I want to import data from the same excel file from 3 different sheets, all in the cell range A3:C22.
I want to obtain a single file where all the variables and observations appear from the 3 sheets. The sheets are in alternative order and not in consecutive order. I used the following

clear
cd "G:\revenue\dt\Oct 2011-Dec 2011"
import excel "firm_100" 
foreach sheet in "ANNEX1" "ANNEX3" "ANNEX5" {
import excel using "firm_100", sheet("`sheet'"), cellrange(A3:C22)
save file_`sheet', replace
}

I get invalid 'cellrange'
r(198);

and also when I tried it without the cell range, I only get the ANNEX1 sheet, not ANNEX3 and ANNEX5. Is there a problem with my loop?

Kindly suggest to me where I am going wrong. Thank you

[Øyvind Snilsberg]

first, import sheets,

foreach sheet in "ANNEX1" "ANNEX2" "ANNEX3" {
import excel "firm_100", sheet(`sheet') cellrange(A3:C22) clear
tempfile `sheet'
save ``sheet''
}

then, either merge sheets (varlist are the variables that the sheets will be matched on),

use `ANNEX1', clear
merge 1:1 varlist using `ANNEX2', nogen
merge 1:1 varlist using `ANNEX3', nogen

or, append sheets,

use `ANNEX1', clear
append using `ANNEX2'
append using `ANNEX3'

[Sanika Rr]

Thank you Sir, I got the temp files. I want to merge them, so while merging I used the above code you shared

use `ANNEX1', clear
merge 1:1 varlist using `ANNEX3', nogen
merge 1:1 varlist using `ANNEX5', nogen

and I got the following output --

do "C:\Users\INSPIRON\AppData\Local\Temp\STD2fe8_0000 00.tmp"

. use `ANNEX1', clear
invalid file specification
r(198);

end of do-file

r(198);

.

Am I making a mistake in saving?

[Øyvind Snilsberg]

is the sort order the same across sheets? i.e., does the first observation in ANNEX1 correspond to the first observation in ANNEX2 and ANNEX3, and so on?
if so, run the following code without interruption,

foreach sheet in "ANNEX1" "ANNEX2" "ANNEX3" {
import excel "firm_100", sheet(`sheet') cellrange(A3:C22) clear
gen id = _n
tempfile `sheet'
save ``sheet''
}

use `ANNEX1', clear
merge 1:1 id using `ANNEX2', nogen
merge 1:1 id using `ANNEX3', nogen

[Sanika Rr]

Thank you sir, for the code. I got the output but I can only see the annex1 observations.
The data in sheets corresponds to a firm name and expenditure for 3 different variables.
So I am trying to obtain where for var1 firm name, amount from Annex1, for var2 firm name amount from Annex3 and for var3 firm name and amount from annex5 and merge them together to see them in a single datasheet from that excel file.

Grateful for your help.

[Øyvind Snilsberg]

Yes, of course. Try,

foreach k in 1 2 5 {
import excel "firm_100", sheet(ANNEX`k') cellrange(A3:C22) firstrow clear
if `k'==5 {
keep firm var3
}
else {
keep firm var`k'
}
tempfile Sheet`k'
save `Sheet`k''
}

use `Sheet1', clear
merge 1:1 firm using `Sheet2', nogen
merge 1:1 firm using `Sheet5', nogen

[Rukman Mecca]

Thank you for this great query and replies!
I was following the thread for the same issue.
How should I sort the variable within each sheets (before merging)?
I have used the following command in Stata 16.1 (as mentioned above)

foreach sheet in "sheet 1" "sheet 2" "sheet 3" {
import excel "C:\Users\survey_data_02092022.xlsx", sheet(`sheet') firstrow clear 
tempfile `sheet'
save ``sheet''
}
use `sheet 1', clear
merge 1:1 SURVEY_ID using `sheet 2', nogen
merge 1:1 SURVEY_ID using `sheet 3', nogen

However, the command is not running without interruption,
The error message is

variable UNIT is str4 in master but byte in using data

Thanks in advance