[Stephen Jenkins]

Remember that locals really are local. If you were running separate code excerpts from the do file editor with macros defined in one excerpt but referred to in a later excerpt, then the local will appear undefined (empty), and that can trigger a syntax error.

[Maris Isidro]

After appending all the excel data sets, has anyone experience getting a missing data in one of the variables in the combined data set in Stata when it is not missing in the original Excel file?

[Clyde Schechter]

If you have a variable which gets imported to Stata as a string in one variable set and as numeric in another, when you try to -append- those together, Stata will throw an error message. You can block that behavior with the -force- option, but if you do that, the variable is set to missing values in the data from the using data set.

So you may need to go back and examine that variable to understand why it is sometimes string and sometimes numeric when imported to Stata, and then revise your code so that, in the append loop, it gets adjusted to be just one or the other before reaching the -append- command.

[Maris Isidro]

Thanks Clyde! I used the option allstring in the import command so that I don't get the problem with the change from numeric to string problem. The data in each of my variables was a combination of string (e.g., <10) and numeric (e.g., 11.745643)

[Fernando Eguren]

Hi Clyde,

I'm trying to use your code, which looks like the cleanest/most intuitive in the thread. In my case it looks like:

local allfiles : dir "C:\Users\324112\Desktop\trial" files "*.xlsx"
tempfile alldata
clear
save `alldata', emptyok

foreach f of local allfiles {
import excel using `f',sheet("Data for import") firstrow clear
gen bank = "`f'"
append using `alldata'
save `alldata', replace
}

However, I get the following error:

file barb.xlsx not found

which is one of my files in the directory!
Any clue on what might be wrong?

Thanks a lot in advance,
Fernando

[daniel klein]

Have you changed into the respective directory? If so, consider the respectcase option of extended function dir if you are on a Windows machine (which you probably are as you are using Excel files and use backslashes as separators).

As for cleanest/intuitive approach, here is the xls2dta (SSC) solution for this

xls2dta , clear generate(bank) importopts(sheet("Data for import") firstrow) : append using "C:\Users\324112\Desktop\trial\*.xlsx"

Best
Daniel

[Fernando Eguren]

Solved, thanks Daniel!

[Arvind Ramasamy]

I am a Beginner in stata, I want to append different excel files from different subfolders .

My current working directory is

cd "S:\06 Data\Data\Sector information\CSM\CSM\"

The working directory contains 4 subfolders with the name

CSM A1 2014 - RI
CSM A1 2015 - RI
CSM A1 2016- RI
CSM A1 2017 - RI


In each subfolder there are 4 excel files named
subfolder (CSM A1 2014 - RI )
CSM-Q1 2014 FR
CSM-Q1 2014 DE
CSM-Q1 2014 UK
CSM-Q1 2014 DK


Same for all the other three subfolders

I want to import all these into single stata file. How can I do it ?.

The discussions here made me to understand to append files from single subfolder. I have 4 subfolders. So please help me out.

Thanks in Advance