Error 601: File path exists in isolation, but returns error in loop

[Sean Strunk]

This import runs smoothly.

import excel "/Users/Sean/Dropbox/Mac/Documents/Research/DataSets/Environmental_Accounts/AIR_may12/LVA_AIR_May12.xls", sheet("1995") firstrow clear

This loop tells me that the above file doesn't exist (which of course it does). This file is halfway through the directory.

** 1995 **

clear
tempfile enviro
save `enviro', emptyok

local filenames: dir "/Users/Sean/Dropbox/Mac/Documents/Research/DataSets/Environmental_Accounts/AIR_may12/" files "*.xls*"

foreach f of local filenames {
import excel `"`f'"', sheet("1995") firstrow clear
gen source = `"`f'"'
display `"Appending `f'"'
append using `enviro'
save `"`enviro'"', replace
}

I have restarted stata, my computer, confirmed all files were in 'offline' mode of Dropbox.

Any ideas why it is upset with me? I should be appending .dta files at each step so that shouldn't be the issue. Similar code has run in the past, but also had this issue and I forget what I did to get it to work or if it just decided to work for me.

Thank you,

Sean

[Clyde Schechter]

When you create local macro filename, the resulting macro does not include the full pathname: it only includes the actual filename and extension. So it does include "LVA_AIR_May12.xls" But it does not include "/Users/Sean/Dropbox/Mac/Documents/Research/DataSets/Environmental_Accounts/AIR_may12/LVA_AIR_May12.xls".

In your standalone command, you do provide the full pathname. In your loop, implicitly you make no mention of the path, only the filename. With no filename specified, Stata will look for this file in whatever the current working directory happens to be. (You can find out what it is by running -pwd-.) I will assume that your current working directory is not /Users/Sean/Dropbox/Mac/Documents/Research/DataSets/Environmental_Accounts/AIR_may12. So Stata is correctly pointing out to you that the file LVA_AIR_May12.xls does not exist in the current working directory. There are a couple of ways you can fix this problem

1. Before you enter the loop, change the current working directory to /Users/Sean/Dropbox/Mac/Documents/Research/DataSets/Environmental_Accounts/AIR_may12. by using the -cd- command.* (See -help cd- if you aren't familiar with it.) Or,
2. You can change the -import excel- command so that the path is included. -import excel `"/Users/Sean/Dropbox/Mac/Documents/Research/DataSets/Environmental_Accounts/AIR_may12/`f'"', sheet("1995") firstrow clear-.

*Once you get past the loop, don't forget to change back to the original working directory so that whatever files you read/write there will be findable.