Instantly share code, notes, and snippets.

Embed
What would you like to do?
Import Directory of Excel Files into Stata
#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 inputdir "C:\put\input\dir\here"
global outputdir "C:\put\output\dir\here"
/******************************************************************************/
local files : dir "$inputdir" 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 "$inputdir\\`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 "$inputdir\\`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' {
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 "$outputdir\\`dtafile'"
}
clear
/* Append all workbooks and worksheets together */
local dtafiles : dir "$outputdir" files "*.dta"
foreach fn in `dtafiles' {
append using "$outputdir\\`fn'"
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment