Merge data from multiple Excel files in one Stata dataset

[Michael Windisch]

Hi! I am having a small issue with merging data from different Excel files in one Stata dataset. As far as I know this is only possible by loading and saving the data first in .dta format and then merge the .dta files via the -merge- command. I wanted to do the loading and saving with a loop but probably due to syntax issues, the code does not work as I want. I basically have Excel files called "1992.xlsx", "1993.xlsx" and so forth in this directory. I came up with this code:

local filelist 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
 
foreach x of local filelist  {
clear
import excel using "H:\Small scale industries\Data collection\Non-OECD\Value added\stata-readable colombia\Stata\`x'.xlsx", firstrow
save "`x'.dta"
}

This gives me an error message (invalid '1992'), so I thought it might be a problem that the local contains numbers instead of strings. I tried to solve that by writing:

local filelist `" "1992" "1993" "1994" "1995" "1996" "1997" "1998" "1999" "2000" "2001" "2002" "2003" "2004" "2005" "2006" "2007" "2008" "2009" "2010" "2011" "2012" "2013" "2014" "2015" "'
foreach x of local filelist  {
clear
import excel using "H:\Small scale industries\Data collection\Non-OECD\Value added\stata-readable colombia\Stata\"`x'".xlsx", firstrow
save ""`x'".dta"
}

but this again leads to the same problem. I am quite sure it is a syntax problem, but I cannot get my head around it. I would very much appreciate your help!

Thanks,
Michael

[Mike Lacy]

The backslash in your file name is biting you. See "Beware the backstabbing backslash"

[Michael Windisch]

I was indeed backstabbed by the blackslash. After controlling for this, the first code works perfectly fine. Thanks a lot, Mike!

[daniel klein]

Also see xls2dta (SSC).

// change to directory where Excel files are located
cd "H:\Small scale industries\..."

// convert the files to dta
xls2dta : import excel . , firstrow

// now merge
xls2dta , clear : merge master:using varlist

Note that I have skipped the rename part that, despite your claim, cannot possibly work because local x is numeric and Stata variable names cannot start with numeric characters. Perhaps you intended to say that the second code works after changing the backslash to a slash.

Best
Daniel

[Michael Windisch]

You are right, Daniel. The -rename- should be deleted (which I have done now in the above code)