loop for importing Excel files

[Guest]

Dear Stata users,

I wanted to right a loop to import a multiple Excel files. I have data on export for 26 countries. Each file starts from a given country name. I wrote a loop:

foreach country in AUS AUT BEL CAN CHE CHN CZE DEU DNK ESP FIN FRA GBR GRC ISR ITA JPN KOR NLD NOR NZL PRT RUS SWE TUR USA{
     import excel "`country'*", sheet("annual") firstrow case(lower)
    save "`country'*", replace
 }

I wanted that Stata takes every Excel file beginning from a given country name, eg. I have 9 files for Australia and every begins from "AUS_"

However, I see an error :file AUS* not found

I wanted to try something more general and tried a code:

global dir "U:\CEPII CHELEM\CHELEM ISIC\All"

local files: dir "$dir" files "*.xlsx", respectcase
  foreach file in 'files' {
               clear
               import excel using "$dir\'file'", firstrow
               save "$dir\'file'.dta", replace
   }

I don't know what's wrong with local but Stata doesn't see it and I have an error:

file U:/CEPII CHELEM/CHELEM ISIC/All/'file' not found

I don't know why Stata doesn't take file with xlsx

[Mike Lacy]

I see that your code includes in several places an apostrophe /'/ b before a local name (file and files) rather the "backtick" /`/ that is syntatically required.

foreach file in 'files' {
                ^
...
...
clear
import excel using "$dir\'file'", firstrow 
                         ^ etc.

[Clyde Schechter]

Mike Lacy is correct, but the problem does not end there. Once you get the backticks right, there is another problem arising from the use of the \ as a path separator. In Stata, \ also signifies the beginning of an "escape sequence," and the sequence \`, in particular, denotes a literal backtick character that is not the start of a local macro reference. The simplest solution is to just use / as the path separator, even in Windows. It works in Windows, and it is also the usual path separator in Mac and Linux operating systems, so it has the additional virtue of making your code portable to those other operating systems should you ever need that. So:

global dir "U:/CEPII CHELEM/CHELEM ISIC/All"
foreach file in `files' {
    ...
   clear
   import excel using "$dir/`file", firstrow
   // etc.
}

By the way, although the above is OK, better still would be to have dir as a local rather than a global macro. Global macros are inherently unsafe, because if some other program you run also has a global macro of the same name, this command overwrites it, which can wreak havoc in the other program--or the other program can change this one and wreak havoc here. While this kind of problem does not come up often, when it does, the resulting bug can be infuriatingly difficult to find. Global macros should be reserved for situations where there is no alternative way to store the information in a way that makes it accessible wherever it is needed. In general, if the information is needed in both the current program and in others called by it, the local should be passed as an argument to the called program(s), or defined in an -#include- file that the programs all reference. Situations where a global macro are truly needed are quite rare: I've been using Stata on a daily basis since 1994, and have only needed to use one once.

[Guest]

Thank you Clyde and Mike for your kind help! It saved my files in dta