[Kim Lam]

Thank you so much!

I have many Excel files named TRD_Dalyr; TRD_Dalyr1; TRD_Dalyr2···etc, all of them contain same columns(Code, Date & Return). I want to merge them into one. Any suggestion would be much appreciated. Thanks!

Best,
Kim

[Clyde Schechter]

One additional point, though it doesn't keep your code from producing useful results:

You initially declare -tempfile building-. But thereafter, you never use it. Instead you -save building- and -append using building-. But without the surrounding left and right quotation marks, you are not referring to that tempfile: you are instead saving and appending an ordinary file named building.dta.

This probably doesn't matter for your purposes: the only difference is that building.dta will be around when your do file finishes, whereas had you put the data in `building', it would be gone. (So, actually, you are probably better off for having made this "mistake.")

[Maris Isidro]

If you only have one level of subdirectories, it's not too hard.

clear
tempfile building
save `building', emptyok
local directories: dir "." dirs. "*" // Macro with name of all subdirectories
local directories "." `directories' // Stick the current directory in front

foreach d of local directories {
local files: dir "`d'" files "*.xlsx"
foreach f of local files {
import excel using "`d'/`f'", clear // Note use of /, not \ as separator.
gen source = "`d'/`f'"
append using `building'
save `building', replace
}
}

should do the trick. Again, you may need to specify some other options in the -import excel- statement. And use /, not \ as your filename separator, even if you are running in Windows. The backslash sometimes does funny things to the character that follows it, and Stata understands / as a path separator in all OSs.

If you have multiple levels of nesting, it gets more complicated. You could write a program that calls itself recursively to do this--but I suspect from your request that that would be more trouble than it's worth to you. Also, I think there is a user-written program out there somewhere that will build a list of files meeting a certain description in a directory and all embedded subdirectories--but I can't remember what it's called. Maybe somebody else on the forum remembers.

Hope this helps.

Hi Clyde,

I am currently in a situation where I am combining a large number of excel files saved in different folders and I want to "combine" or append them into one master file. I tried using your program above, however I got an invalid syntax message running this line:

local directories: dir "." dirs. "*" // Macro with name of all subdirectories

Just wondering what could possibly be wrong.

Thanks,
Maris

[Clyde Schechter]

There shouldn't be a dot after "dirs": just take that out and it'll be fine.

[Jimmy Yang]

The approach I've used everyday:
(Add another loop if there are sub-folders.)
Advise: Do not save and replace ordinary file in the loop, it might break your disk.
(If you deal with a large number of files like me.)
Use temporary file instead.
Noted: You can only read in limited number of file name in "local". Divide them.


local myfiles : dir "[path]" files "*.[filetype]"
local q=1
foreach f of local myfiles{
drop _all
[import file] using `f', clear
gen file=`"`f'"'
tempfile mybig`q'
save "`mybig`q''"
display `"`q'"'
local q=`q'+1
}

local obs=`q'-1
use "`mybig1'", clear
forvalues i=2/`obs' {
append using "`mybig`i''"
}
compress
save mybig, replace

[Maris Isidro]

Hi Clyde,I tried to generate a list of directories - list `directories' or display `directories' it comes out empty. Isn't it supposed to show or display the directories?

For example:

clear
cd "c:\OfficeData"
tempfile building
save `building', emptyok
local directories: dir "." dirs "*" // Macro with name of all subdirectories
local directories "." `directories' // Stick the current directory in front

list `directories'

[Stephen Jenkins]

Re #21: The "directories" macro may be empty if there are no subdirectories in your current directory.

Nonetheless, I am a bit puzzled too. Thinking about the OP's question, I looked at help extended_fn and specifically the section which states

 local list : dir . files "*" makes a list of all regular files in
            the current directory.  In list might be returned "subjects.dta"
            "step1.do" "step2.do" "reest.ado".

Now, look at the results of the following:

. dir u*
6137.0k   1/25/05 10:04  uscounty1.zip    
5410.9k   1/25/05 10:04  uscounty2.zip    
5354.4k  12/20/04 11:08  usmap.dta        
1900.5k  12/23/04 10:32  usmap.zip        
   0.8k  12/23/04 10:32  usmaps.do        
1900.5k  12/23/04 10:32  usw_mw_emap.zip  

. local list : dir . files "u*"

. di "`list'"
uscounty1.zip" "uscounty2.zip" "usmap.dta" "usmap.zip" "usmaps.do" "usw_mw_emap.zip"" invalid name
r(198);

.

Why the error message? ("Invalid syntax" of some kind.)

[Robert Picard]

Stephen, the files are returned enclosed in double quotes. When your list macro is expanded, the first two characters are 2 double quotes and then Stata complains that uscounty1.zip" ... is not a valid name of something to display. Try instead

dis `"`list'"'

[Stephen Jenkins]

That's it! [I should have thought of that.] Thanks, Robert

[Maris Isidro]

Good on you Stephen! I am not sure what's the bug in Clyde's codes, if I can only make this run. What I am trying to do is loop through 50 folders (US states) that has Excel files in it corresponding to MSAs in each state and i have to combine them into one file...

[Nick Cox]

Maris: I am not especially clear what you are trying to do but the purpose of list is to list data, not to display filenames.

Clyde didn't, and wouldn't, recommend it for that purpose so the reference to "the bug in Clyde's codes" is puzzling.

Otherwise your assignments to local macros look sound and display or macro list can be used to inspect their contents.

[Maris Isidro]

Finally it worked! I changed "directories" into "dirlist" and it did the trick! Somehow Stata did not like directories to be a name for a macro!

[Nick Cox]

There is absolutely no syntactic reason not to use the name directories for a macro.

[Imed Limam]

Hello everyone,
I very much appreciate the hints and suggestions about merging excel data files from the same directory/sub-directory. They are indeed extremely useful.I was wondering how these suggestions could be extrapolated for the case of merging multiple worksheets within the same workbook.
Thank you.

[Maris Isidro]

Hi Nick, thank you for your comments. I just got the chance to re-run Clyde's codes using the macro name directories and it worked. My apologies for the confusion, I was always getting an invalid syntax message before and tried several things. Anyways, here is the set of codes that I am using and runs perfectly well for my purpose:

clear
tempfile usdata
save `usdata', emptyok
local dirlist: dir . dirs "*" // Macro with name of all subdirectories
local dirlist . `dirlist' // Stick the current directory in front

foreach d of local dirlist {
local files: dir "`d'" files "*.xls"
foreach f of local files {
import excel using "`d'/`f'",sheet("Industries") firstrow clear // Note use of /, not \ as separator.
gen source = "`d'/`f'"
append using `usdata', force
save `usdata', replace
}
}