Import Directory of Excel Files into Stata

excel_dir.do

#delimit cr
version 13.1
clear all
set more off
/*******************************************************************************
* Import Directory of Excel Files into Stata
* 
* Author: Daniel Reeves
* https://github.com/dwreeves
* 
* This code is a simple, bare bones import of all worksheets across all 
* workbooks within one directory into .dta files in another directory. This
* framework is designed to be easily customizable to address any abnormalities
* caused by fussy Excel files. As is, the framework should operate without
* problems on a directory of "clean" Excel files.
*******************************************************************************/

/* Directories */
global INPUT_DIR "C:/put/input/dir/here"
global OUTPUT_DIR "C:/put/output/dir/here"

/******************************************************************************/

local files : dir "${INPUT_DIR}" files "*.xls*"
local totalWorkbooks : word count `files'

forvalues i = 1 (1) `totalWorkbooks' {
	
	local fn : word `i' of `files'
	
	/* Excel spreadsheet metadata */
	
	qui import excel using "${INPUT_DIR}/`fn'", describe
	local totalWorksheets = `r(N_worksheet)'
	
	forvalues j = 1 (1) `totalWorksheets' {
		local worksheet`j' = "`r(worksheet_`j')'"
		local range`j' = "`r(range_`j')'"
	}
	
	/* Import data from each worksheet in a workbook */
	
	forvalues j = 1 (1) `totalWorksheets' {  
		
		import excel using "${INPUT_DIR}/`fn'", ///
			sheet("`worksheet`j''") cellrange("`range`j''") ///
			firstrow case(lower) clear
		
		gen str filename = "`fn'"
		gen str worksheet = "`worksheet`j''"
				
		tempfile temp_`i'_`j'
		save `temp_`i'_`j''
	}
	
	clear
	
	forvalues j = 1 (1) `totalWorksheets' {
		capture noisily append using `temp_`i'_`j''
	}
	
	/* Save workbook as .dta file */
		
	// Stata ver. 13
	local dtafile = strreverse(substr(strreverse("`fn'"), ///
		strpos(strreverse("`fn'"), ".") + 1, .)) + ".dta"
		
	// Stata ver. 14+
	* local dtafile = substr("`fn'", 1, strrpos("`fn'", ".") - 1) + ".dta"

	save "${OUTPUT_DIR}/`dtafile'"
}

clear

/* Append all workbooks and worksheets together */

local dtafiles : dir "${OUTPUT_DIR}" files "*.dta"

foreach fn in `dtafiles' {
	capture noisily append using "${OUTPUT_DIR}/`fn'"
}