Weird 'file not found' error while appending excel sheets in a folder

[Kartikeya Bhatotia]

Hey all,

I got some datasets in .xlsx format in a folder which I appended through the following:

clear
tempfile census
save `census', emptyok

local filenames: dir "C:\Users\abc\Desktop\folder" files "*.xlsx"
foreach f of local filenames {
import excel using `"`f'"', sheet("Sheet1") cellrange(A7) firstrow clear
gen source = `"`f'"'
    display `"Appending `f'"'
    append using `census'
    save `"`census"', replace
}

The code runs OK, and I get the appended dataset as required, HOWEVER, it throws up an error in the end:

Appending hlpca-23424-2011_h14_census.xlsx
file C:\Users\abc\AppData\Local\Temp\ST_1954_000004.tmp saved
Appending hlpca-23431-2011_h14_census.xlsx
(note: variable D was str6, now str9 to accommodate using data's values)
file C:\Users\abc\AppData\Local\Temp\ST_1954_000004.tmp saved
Appending hlpca-23435-2011_h14_census.xlsx
(note: variable D was str6, now str9 to accommodate using data's values)
(note: variable F was str9, now str10 to accommodate using data's values)
(note: variable I was str30, now str41 to accommodate using data's values)
file C:\Users\abc\AppData\Local\Temp\ST_1954_000004.tmp saved
Appending hlpca-23443-2011_h14_census.xlsx
(note: variable D was str7, now str9 to accommodate using data's values)
(note: variable I was str29, now str41 to accommodate using data's values)
file C:\Users\abc\AppData\Local\Temp\ST_1954_000004.tmp saved
Appending hlpca-23463-2011_h14_census.xlsx
(note: variable F was str9, now str10 to accommodate using data's values)
(note: variable I was str32, now str41 to accommodate using data's values)
file C:\Users\abc\AppData\Local\Temp\ST_1954_000004.tmp saved
file ~-23424-2011_h14_census.xlsx not found
r(601);

It's a little odd because the file that has not been found is actually the first file to be appended - how do I fix this?

[William Lisowski]

It's a little odd because the file that has not been found is actually the first file to be appended - how do I fix this?

The first file to be appended was

hlpca-23424-2011_h14_census.xlsx

The file in the error message is

~-23424-2011_h14_census.xlsx

I suspect this is not some sort of abbreviation, but an actual "temporary" file created by Excel in that directory, renamed to start with a tilde. Perhaps you have a file open in Excel while you are running this Stata code, or perhaps Excel once crashed and left this temporary file permanently in the directory.

So the first steps to avoiding this are (a) make sure that file doesn't exist in your directory and (b) exit Excel before running the Stata command.

Or, if your Excel workbooks all begin "hlpca-", try

local filenames: dir "C:\Users\abc\Desktop\folder" files "hlpca-*.xlsx"

to skip any files not beginning with that.

[Kartikeya Bhatotia]

Or, if your Excel workbooks all begin "hlpca-", try

local filenames: dir "C:\Users\abc\Desktop\folder" files "hlpca-*.xlsx"

to skip any files not beginning with that.

This worked - thank you!