[Clyde Schechter]

Re #14: I don't see anything wrong with your code, and when I tried it on my setup it ran with no problems and produced the expected results. I cannot replicate your problem.

[Olena Onishchenko]

Clyde

The problem was missing last line of code:

* Get a list of all the relevant files in your directory
clear all
cd "\\m\TWSE\20014_01"
local flist: dir "." files "*.csv"  

* Import each file in the list and save it to a tempfile
local nfile 0
foreach fname of local flist {
    local ++nfile     
    import delimited "`fname'", bindquote(strict) case(preserve) clear   
    tempfile temp`nfile'
    save "`temp`nfile''"
}    
* Append all of the nfile files
clear
forval i = 1/`nfile' {
  append using "`temp`i''"
}

save alldata, replace

Regards,
Olena

[Clyde Schechter]

Ah, I see. Thank you for closing the thread by posting your solution.

[Markos Kashiouris]

Local macros, such as the ones used to hold the names of the various tempfiles, and the file list, only exist while the program that contains them is running. If you run the first part of the code, up to (say) the part where I have the comment "// append all of the nfile files", Stata will have imported and saved as a temporary file each of your files. However, Stata "forgets" the names of all the temporary files that were created (`temp1', `temp2' ...) as soon as those lines of code are executed. (And, it drops those files as well.) So, neither the names nor the files exist, and any further code that needs that will no longer exist. This is a feature of Stata, namely that tempfiles and the content of local macros only exist until the current set of code that is running has finished. This code, and other Stata codes using locals, must be run all at once.

However, that aside, there are two mistakes in my code:

save using "`temp`nfile''" should be save "`temp`nfile''"
and
append using "`temp`nfile''" should beappend using "`temp`i''"
Sorry for the confusion.

Dear Mike, I think there is a problem with the code. The problem lies in two parts, at least in STATA 16.1

First, the local variable nfile cannot be passed to the next loop.
Second, the last temp file is left in memory, so i suggest to append it with backward numbers. Below, you can find a revised code that worked for me:

local flist: dir "." files "*.xlsx"

local nfile 0
foreach fname of local flist {
local ++nfile
import excel "`fname'", sheet("page_1") firstrow case(lower) clear
local datestring = subinstr("`fname'", ".xlsx", "", .) //
save "temp`nfile'", replace
global FilesLeft = `nfile'
}

local nfile = $FilesLeft - 1
forvalues i = `nfile'(-1)1 {
display "Appending file `i'"
append using "temp`i'"
}

local nfile = $FilesLeft
forvalues i = `nfile'(-1)1 {
. rm temp`i'.dta
}

Thank you,

Markos G. Kashiouris MD MPH

[Markos Kashiouris]

dup