Loop over import using global

[Neg Kha]

Hi,

I have hundreds of excel files and I would like to import each of them one by one and save them in Stata format. I put the names of the files in a global and then loop the importing and saving process over each value in the global. I use the following code:

global c AADA01 AADA05 AADA20 AADA25 AAHA60 AAHA60 AAHA60 AAHA60 AAHA60 AAHF01 AAHF10 AAHN02 AAHN02 AAHN15 AAHN25 AAHN25 AAHN40 AAHN45 AAKN20 AAKN20 AAMN01 ABAN06 ABAN11 ABAN15 ABFF01 ABKF10 ABKF10 ABMM01 ABMM01 ABMM02 ABMM03 ABMM08 ABMM50 ABMM55 ABMM70 ABVA05 AEBF10 AEBF15 AEBF25 AEBF30 AEBN10 AFOA15 AFOA20

foreach x in $c {
di "`x'"
import delimited using "C:\Users\39349\Documents\AF\Courses"`x'"- fu", clear
save "C:\Users\39349\Documents\AF\Courses- Stata"`x'"", replace
}

I get the following result and error:
AADA01
invalid 'AADA01'
r(198);

When I use the code without the quotation as follows:

foreach x in $c {
di "`x'"
import delimited using "C:\Users\39349\Documents\AF\Courses\`x'- fu", clear
save "C:\Users\39349\Documents\AF\Courses- Stata\`x'", replace
}

I get the following error:
file C:\Users\39349\Documents\AF\Courses`x'- fu.csv not found
r(601);

What am I doing wrong here?

[Mike Lacy]

The backslash is an escape character in Stata, so \`x' tells Stata *not* to expand ("dereference") the local x in your file names. Instead, use the forward slash "/" in file paths, and Stata will nevertheless properly communicate with the Windows file system. This is only necessary in a situation like yours, in which the backslash appears right before a dereferenced local, but it's a good general practice in file paths. See https://www.stata-journal.com/articl...article=pr0042 to learn about this.

[Clyde Schechter]

Well, you contradict yourself in your post in a way that may well be the source of your problem. On the one hand you say you want to import Excel files. But then your code uses -import delimited-, which imports CSV files, not Excel spreadsheets. So if your file is really named AADA01-fu.xlsx, it is no surprise that -import delimited- fails to find it, because -import delimited- expects the filename extension to be .csv, and it also expects the contents of the file to be delimited text, not a spreadsheet file. To import spreadhseets, use -import excel-.

Now, once you fix that (or if you really do have CSV files and the use of -import delimited- is therefore appropriate) you have another problem. Look closely at your error message:

file C:\Users\39349\Documents\AF\Courses`x'- fu.csv not found

Notice that the filename it puts out is not the filename you were trying to access. You were trying to reach C:\Users\39349\Documents\AF\Courses\AADAO1- fu.csv on the first iteration of the loop. Stata omitted the path separator (\) and failed to expand local macro `x' (the loop iterator). This is because in Stata, the sequence of characters \` is not parsed as the character \ followed by the character `. Instead, the digraph \` is understood as an "escape sequence" which represents the single literal ` character. By literal ` character, I mean that it is not the ` that opens a local macro reference; it is instead the actual character `, with no special meaning attached to it. So you have no path separator, and no local macro.

There are two solutions available. One is to use \\` instead of \`. This is kind of meta: \\ is interpreted as an escape sequence, too: it refers to a literal \ character that, itself, cannot be the start of an escape sequence. But I think the other solution is better: use /, not \, as your path separator. Although the standard path separator in Windows is \, Windows understands / as a path separator. And when you use / as the path separator, your code gains the advantage of being portable to Mac and Linux (which accept / but not \ as path separator). In fact, I routinely use / as my path separator all the time now, having been bitten by this particular problem several times.

Added: Crossed with #2, which gives the same advice more succinctly.

[Neg Kha]

The backslash is an escape character in Stata, so \`x' tells Stata *not* to expand ("dereference") the local x in your file names. Instead, use the forward slash "/" in file paths, and Stata will nevertheless properly communicate with the Windows file system. This is only necessary in a situation like yours, in which the backslash appears right before a dereferenced local, but it's a good general practice in file paths. See https://www.stata-journal.com/articl...article=pr0042 to learn about this.

It works now, thanks!