Running loop with import excel command and sheet names with spaces

[Astrid Pineda]

Dear all,

I am trying to run a loop that will import several sheets within a file. Each sheet has a different name and these names often have spaces within them. I am having trouble figuring out the correct syntax to use in the local and loop. This is what I am trying to do:

local municipio "Santo Domingo Este" "Santo Domingo Oeste" "Santo Domingo Norte" ///
"Boca Chica" "San Antonio De Guerra" "Pedro Brand" "Los Alcarrizos" "Azua"

foreach i of local municipio {

import excel "$raw/population_data.xlsx", cellrange(A7:G61) sheet("`i'") firstrow clear

}

Thank you in advance.

[Mike Lacy]

Using -set trace on- is a way to see what kind of problems are happening. In this case, it shows that you're running into problems with the quotes in the items in -local municipio-. You need Stata's "compound double quotes" to enclose a string that itself includes quotes. (See -help quotes-). This applies to the local and to the sheet(option). Also, you need to not split a string across lines in constructing the string for your local. Give this a try:

local municipio = `""Santo Domingo Este" "Santo Domingo Oeste" "Santo Domingo Norte" "Boca Chica" "San Antonio De Guerra" "Pedro Brand" "Los Alcarrizos" "Azua""'
foreach i of local municipio {
   import excel "$raw/population_data.xlsx", cellrange(A7:G61) sheet(`"`i'"') firstrow clear
}

[William Lisowski]

Following up on Mike's warning to not use line continuation in your circumstances, let me show a workaround that supports readability.

First, this is what happens if you use continuation lines.

local test `""Santo Domingo Este" ///
             "Santo Domingo Oeste" ///
             "Santo Domingo Norte""'

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gn/T//SD93404.000000"

. local test `""Santo Domingo Este" ///
invalid syntax
r(198);

end of do-file

But if you change the line delimiter character as described in help #delimit the multi-line definition works.

#delimit ;
local test `""Santo Domingo Este"
             "Santo Domingo Oeste"
             "Santo Domingo Norte""' ;
#delimit cr
macro list _test

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gn/T//SD93404.000000"

. #delimit ;
delimiter now ;
. local test `""Santo Domingo Este"
>              "Santo Domingo Oeste"
>              "Santo Domingo Norte""' ;

. #delimit cr
delimiter now cr
. macro list _test
_test:          "Santo Domingo Este" "Santo Domingo Oeste" "Santo Domingo Norte"

.
end of do-file

[Astrid Pineda]

Thank you so much Mike and William! Both of your comments were very very helpful.

Best,
Astrid