Merge multiple .dta files in a folder

[Du Nguyen]

I have an Excel file (demo.xlsx) with multiple sheets with unique sheet names (A,B,C, etc). I processed something on each and got a "filename.dta" file for each sheet, where "filename" is their Excel's sheet name (A,B,C, etc). Now I want to merge all the .dta together. Here's my code:

* I first import the Excel file (demo.xlsx) to get all the sheet names (the sheet names are A, B, C, etc.):
import excel using "demo.xlsx", describe
* I import the first .dta file as the master file (A.dta)
use "~/Desktop/A.dta", clear
* I then loop through the rest of the sheets to merge
forvalues sheet=1/(`=r(N_worksheet)'-1) {
    local sheetname=r(worksheet_`sheet')
    merge 1:1 earliest using "`sheetname'.dta"
    drop _merge
}

The problem is the "local sheetname" is gone after the first loop run, so the second time its run failed. I'd appreciate any help with this. Thanks.

[Sven-Kristjan Bormann]

You could use a different approach.

*Get all Stata datasets on the desktop
local sheetnames : dir "~/Desktop/" files "*.dta"
local exclude A
*Remove your initial dataset from the list
local sheetnames : list sheetnames - exclude
use "~/Desktop/A.dta", clear
foreach sheet of local sheetnames {
    merge 1:1 earliest using "`sheet'.dta"
    drop _merge
}

I did not test this code, but it should give you an idea how to proceed.

I see two problems with your code.

1. The r(worksheet_sheet) macros will disappear after the call to merge, because the values in r() are cleared when another program which returns something in r() is called.
2. "import excel" will return only one macro called r(worksheet_#), so that your loop will fail in the second iteration.

[daniel klein]

For a canned solution, see xls2dta (SSC).

Best
Daniel

[Du Nguyen]

Thank you all for your help! I figured a solution from this thread:

https://www.statalist.org/forums/forum/general-stata-discussion/general/1518146-using-a-nested-foreach-loop-to-merge-and-then-append-multiple-files-from-multiple-folders-into-one-dataset