Excel sheets

[Esteban Jara]

Hello. I'm importing a workbook containing many sheets from Excel. I'd like to define a local or a global that allows me to save the names of all the sheets in the Excel file. Could someone please tell me how to do this? Thank you very much.

[Andrew Musau]

See the -describe- option of import excel. You want something like:

qui import excel "myfile.xlsx", describe
local worksheets
forval i=1/`=r(N_worksheet)'{
    local worksheets "`worksheets'  `r(worksheet_`i')';"
}
display "`worksheets'"

[Clyde Schechter]

clear*

import excel using spreadsheet_filename, describe

local n_sheets `r(N_worksheet)'

local sheet_names
forvalues i = 1/`n_sheets' {
    local sheet_names `"`sheet_names' `"`r(worksheet_`i')'"'"'
}

display `"`sheet_names'"'

Added: Crossed with #2, which offers an almost identical solution.

[Esteban Jara]

Thank you. I'm now trying to save a file for each sheet in the Excel file, using the data saved in local, and it's not working. Can you help me again, please? Thanks!

[Clyde Schechter]

In what way is it "not working." There are so many possibilities. So it's impossible to advise based only on that. What commands are you using to save the files and what exactly is going wrong. If there are any error messages, show them alongside the commands that are leading to them as well.

[Esteban Jara]

What I'm doing is testing based on my limited knowledge of Stata, using the while and foreach commands, and I can't figure out how to generate a separate .dta file for each sheet in the Excel file I import. In other words, nothing I'm doing is working.

[Clyde Schechter]

That really isn't a very helpful response. You don't show any specific commands: the details are all-important. "Nothing I'm doing is working" carries no information when you give only vague hints at what you are doing and say nothing at all about the way in which it is not working.

That said, here is the gist of how one would do that, starting from the code shown earlier that creates the local macro sheet_names:

foreach s of local sheet_names {
    import excel using spreadsheet_filename, sheet(`"`s'"') clear
    // MAYBE SOME CODE TO RENAME VARIABLES OR OTHER MINOR DATA MANAGEMENT
    save `"`s'"', replace
}

That said, there are important details that need to be dealt with in this code but that cannot be specified in the absence of more information. The -import excel- command has numerous options, some of which may be necessary, and others helpful even if not strictly necessary, for your purposes. Also, it may be important to do some minor degree of data management on each file before you save it. That depends on how the data comes in after -import excel- and how you plan to ultimately use the .dta files you are creating.

Finally, it is important to emphasize that Excel is very permissive about what names a sheet can have. Some legal sheet names may not be legal data file names in your operating system. And if you try to save a file with a name that your OS will not allow, you will get an error message and execution will halt. Hopefully it will be an error message that describes the problem. But be warned that error messages relating to input/output operations, especially when they are mediated through Stata, are often very vague, saying things like "........ could not be saved" without offering any clues why.

[Esteban Jara]

Thank you very much, Mr. Schechter. I'll keep trying, guided by your comments. I took some precautions beforehand, such as naming the Excel sheets with legal names. I hope to get to the right place. And you're right to say I didn't phrase the question correctly. Best regards.