import - Importing 300 Excel Files at once (Stata)

Question

I am trying to automate the importing process of 300 Excel files into Stata. The files represent a time of 25 years, with each month represented in one file. The files contain the same number and type of variables (i.e. all are string variables).

I would appreciate any tips. The string variables are as follows:

unemp_rate  gdp     gini       year  
11           .02     22        2002
22            2      30        2003

My code is below:

clear all
ssc install xls2dta
xls2dta, save("C:UsersxyzOneDrive - xyzDocumentsxyzxyz") : import excel "C:Usersxyzxyz- xyzDocumentsxyzxyz"

xls2dta, save ("C:Usersxyzxyz- xyzDocumentsxyzxyz") : append, force
use "C:Usersxyzxyz- xyzDocumentsxyzxyz2xyz.dta", clear

However, the variables have been imported as A,B,C, D, etc... rather than the actual variable names above.

Answer 1

There is a whole discussion at Statalist about it, including the usage of xls2dta; however, here is a solution that does not rely on any external command. Additionally, this way to proceed is particularly suitable for your problem as you mentioned that all the files preserve the same structure (i.e., same variable names).

clear all
global route = "C:
oot_to_your_files"
cd "$route"
tempfile building
save `building', emptyok
// List all the ".xlsx" files in folder "${route}"
local filenames : dir "${route}" files "*.xlsx"
// loop over all files, while appending them 
foreach f of local filenames {
    import excel using `"`f'"' ,firstrow allstring clear
    //it identifies the procedence of the rows (i.e., the .xlsx)
    gen source = `"`f'"'
    // append new rows 
    append using `building'
    save `"`building'"', replace
}
// save all the xlsx files in a single dta file
save "${route}all_xlsx_files" ,replace