Appending large numbers of csv files - computation failure in "levelsof"

[Marvin Hanisch]

Dear Statalist users,

I am trying to append close to a million csv files in Stata using the following code:

clear all
filelist, dir("I:\MyData") pattern("*.csv")
gen dirname_fixed = subinstr(dirname, "/", "\", .)
drop dirname
gen filepath = dirname_fixed + "\" + filename
levelsof filepath, local(csv_files)

tempfile building
qui save `building', emptyok

foreach f of local csv_files {
    import delimited using `"`f'"', bindquote(strict) maxquotedrows(1000) varnames(1) delimiters(",") clear
    gen file = `"`file'"'
    append using `building'
    save `"`building'"', replace
}

In principle, this code works fine as long as I am using a small number of files. However, once I apply it to the full dataset, I get the error "cannot compute" for the command:

levelsof filepath, local(csv_files)

I am not sure if this is because Stata is running out of memory due to the large number of strings (i.e., file directories) it is storing, or if this is some other problem. In any case, is there another workaround for appending many files?

Best regards,
Marvin

[Mike Lacy]

Among Stata's limits (-help limits-) is the maximum length for a macro, stored in c(macrolen) and modifiable somewhat, but perhaps not large enough for your purposes. You could do something like the following sketch, which might be more robust to failures along the way anyway

clear all
filelist, dir("I:\MyData") pattern("*.csv")
gen dirname_fixed = subinstr(dirname, "/", "\", .)
drop dirname
gen filepath = dirname_fixed + "\" + filename
// New
// Mark off the files into  about 100 parts
gen int part = ceil(_n/100)
summ part
forval i = 1/`=r(max)' {
   // "if" is New
   levelsof filepath if part == `i', local(csv_files) 
   tempfile building`i'
   qui save `building`i'', emptyok 
   foreach f of local csv_files {
   // do as before, etc.
...
   }
....
}
// Append all the building`i' files together
...

[Marvin Hanisch]

Thank you very much, Mike! I will try this solution and otherwise fall back on Python.