"Using required" error while importing excel sheets into Stata with loops

[Lorien Nair]

Hi All,

I have to import and save into .dta (before appending them) several thousand .xlsx files. The naming conventions for these files are slightly odd in that they have two components (or three depending on how you look at it): Name of the state and corresponding Month (which also has an alphabet associated with). For example for the state of Bihar in the month of April the name looks like A - Bihar_April.xlsx for May it becomes B - Bihar_May.xlsx and this continues for 35 territories over 12 months for 10 years. So I have thousands of file to import and I wrote a loop just to test for one year

foreach var in `" "A & N Islands" "Andhra Pradesh Old" "Arunachal Pradesh" "Assam" "Bihar" "Chandigarh" "Chhattisgarh" "Dadra & Nagar Haveli" "Daman & Diu" "Delhi" "Goa" "Gujarat" "Haryana" "Himachal Pradesh" "Jammu & Kashmir" "Jharkhand" "Karnataka" "Kerala" "Lakshadweep" "Madhya Pradesh" "Maharashtra" "Manipur" "Meghalaya" "Mizoram" "Nagaland" "Odisha" "Puducherry" "Punjab" "Rajasthan" "Sikkim" "Tamil Nadu" "Tripura" "Uttar Pradesh" "Uttarakhand" "West Bengal" "' { // these are the states
    foreach y in `" "A - `var'_April" "B - `var'_May" "C - `var'_June" "D - `var'_July" "E - `var'_August" "F -     `var'_September" "G - `var'_October" "H - `var'_November" "I - `var'_December" "J - `var'_January" "K -     `var'_February" "L - `var'_March" "' { // I add the local `var' to change per month
 
   import excel "$irds2008/`y'.xlsx", sheet("Sheet1") clear // importing for example A - A & N Islands_April.xlsx so on and so forth
    replace C = "distname" in 6
    replace B = "2.0" in 6
        
    drop A B D E
    
    *ssc inst sxpose
    sxpose, clear firstnames
    drop if distname == "distname"
    gen stname = "`var'"    // generating state tag
    replace stname = lower(stname)

      save "$irds2008/`y'2008.dta", replace
        
    }        
}

I keep getting the error "using required"

So Instead I decided to store everything in locals outside the loop and write the following:

local var `" "A & N Islands" "Andhra Pradesh Old" "Arunachal Pradesh" "Assam" "Bihar" "Chandigarh" "Chhattisgarh" "Dadra & Nagar Haveli" "Daman & Diu" "Delhi" "Goa" "Gujarat" "Haryana" "Himachal Pradesh" "Jammu & Kashmir" "Jharkhand" "Karnataka" "Kerala" "Lakshadweep" "Madhya Pradesh" "Maharashtra" "Manipur" "Meghalaya" "Mizoram" "Nagaland" "Odisha" "Puducherry" "Punjab" "Rajasthan" "Sikkim" "Tamil Nadu" "Tripura" "Uttar Pradesh" "Uttarakhand" "West Bengal" "'
local month `" "A - `var'_April" "B - `var'_May" "C - `var'_June" "D - `var'_July" "E - `var'_August" "F - `var'_September" "G - `var'_October" "H - `var'_November" "I - `var'_December" "J - `var'_January" "K - `var'_February" "L - `var'_March" "'


foreach y in `month' {
import excel "$irds2008/`y'.xlsx", sheet("Sheet1") clear

drop A B D E

*ssc inst sxpose
sxpose, clear firstnames
drop if distname == "distname"
gen stname = "`x'" // generating state tag
replace stname = lower(stname)


save "$irds2008/`y'2008.dta", replace

}

And I get no output for this.

[Daniel Feenberg]

This is hard to explain. The documentation for -import excel- and -save- does not suggest the need for a -using- clause. Nor do any of the other statements deal with files. When Stata detects an error in a foreach or forvalues loop, it doesn't tell you which statement caused the error unless you have trace on. So try again after setting:

set trace on
set tracedepth 1

and you will at least get an indication of which command is causing the problem. My guess is that it will turn out to be related to quoting values with spaces.

[Lorien Nair]

Thanks, Daniel Feenberg. I ran the trace commands and the error comes from the file path.

file /$dropbox/project7/data/A -  .xlsx not found

So the error stems from the locals not being put together in the loop. Is there a way to correct the loop where it combines the locals var and month (y) to generate file names for example A - A & N Islands_April.xlsx (which inserts local var into local month (y)). Thanks!

[Hemanshu Kumar]

In the first version of your code, either remove the outer compound quotes in both the foreach statements, or instead do something like

local states `" "A & N Islands" "Andhra Pradesh Old" "Arunachal Pradesh" "Assam" "Bihar" "Chandigarh" "Chhattisgarh" "Dadra & Nagar Haveli" "Daman & Diu" "Delhi" "Goa" "Gujarat" "Haryana" "Himachal Pradesh" "Jammu & Kashmir" "Jharkhand" "Karnataka" "Kerala" "Lakshadweep" "Madhya Pradesh" "Maharashtra" "Manipur" "Meghalaya" "Mizoram" "Nagaland" "Odisha" "Puducherry" "Punjab" "Rajasthan" "Sikkim" "Tamil Nadu" "Tripura" "Uttar Pradesh" "Uttarakhand" "West Bengal" "'

foreach var of local states  {
    local filenames `" "A - `var'_April" "B - `var'_May" "C - `var'_June" "D - `var'_July" "E - `var'_August" "F -     `var'_September" "G - `var'_October" "H - `var'_November" "I - `var'_December" "J - `var'_January" "K -     `var'_February" "L - `var'_March"  "'
    foreach y of local filenames  { 

        ...

    }
}

[Lorien Nair]

Thanks a lot Hemanshu Kumar , this was super helpful and fixed the error I was running into.