Datamanager.dk

How to combine Stata and Excel files into a multi-sheet spreadsheet


/*
The code below will read all *.dta and *.xls* files in the working directory and add each file to a single spreadsheet.
Note that 31 characters is the limit for Excel sheet names.
*/

local output = "AllTables_" + string(d(`c(current_date)'), "%dCY-N-D" ) + ".xlsx"

clear
local filesDTA : dir "`c(pwd)'" files "*.dta"
local filesXLS : dir "`c(pwd)'" files "*.xls*"

foreach file in `filesDTA' `filesXLS' {
	if substr("`file'",length("`file'")-3,length("`file'"))==".dta" use "`file'", clear
	if substr("`file'",length("`file'")-3,length("`file'"))==".xls" | substr("`file'",length("`file'")-4,length("`file'"))==".xlsx" import excel "`file'", firstrow clear	
	noi di "Adding `file'"
	
	local filename = substr(subinstr(subinstr(subinstr("`file'",".dta","",.),".xlsx","",.),".xls","",.),1,31)
	export excel using `output', firstrow(variables) sheet("`filename'") sheetmodify
	}
	noi di "Saved as `output'"

How to compare differences between multiple Stata files


/*
This code will read all Stata files in a folder (input1), compare them to the Stata files in a different folder (input2)
and display the differences found (if any). Note that the files to compare must have identical names.
*/

local folder1 = "C:\input1\"
local folder2 = "C:\input2\"

local files1 : dir "`folder1'" files "*.dta"
local files2 : dir "`folder2'" files "*.dta"

foreach file in `files1' {
	use "`folder1'\`file'", clear
	cap cf _all using "`folder2'\`file'"
	if `r(Nsum)' > 0 {
		noi di in red "`r(Nsum)' differences in `file'"
		}
		else {
		noi di in blue "`r(Nsum)' differences in `file'"
		}
	}