How to merge Multiple Excel files.

[Khalilur Rahman]

I want to upload 242 Excel files into Stata and merge them in one dta file. [File names 001, 002, 003, 004, . . . . . . . . 241, 242]
All files are on the same data format, I mean same number of columns and the same variable names
My all excel files have two sheets named “Sheet” and “Results”.
I want to load the data from “Results” sheet
My data first row is variable names
Kindly provide me the Stata code to combine/merge them into one .dta file.
The path of my excel files is “D:\Research Pro\DataFile\Data_Companies\Data_Modified”

Thanks and Regards,

[Hemanshu Kumar]

Something like this should do the trick:

clear
tempfile master
save `master', emptyok

local folder D:/Research Pro/DataFile/Data_Companies/Data_Modified
forval i = 1/242 {
    local file_name `:dis %03.0f `i''.xlsx
    dis "Appending file `file_name'..."
    import excel using `"`folder'/`file_name'"', clear firstrow sheet("Results")
    append using `master'
    save `master', replace
}

use `master', clear

You can then save this data under whatever name and in whatever location you like.

(warning: untested code)

[Khalilur Rahman]

Facing this ISSUE
Please if you can guide

do "C:\Users\KHALIL\AppData\Local\Temp\STD0000000 0.tm p"

. global mainDIR `"D:\Research Pro"'

. set more off

. cd "$mainDIR"
D:\Research Pro

. set more off
. clear
. tempfile master

. save `master', emptyok
(note: dataset contains 0 observations)
file C:\Users\KHALIL\AppData\Local\Temp\ST_00000001.tmp saved

.
. local folder D:\Research Pro\DataFile\Data_Companies\Data_Modified

[will the orange text would be changed or will work as it is?]

. forval i = 1/242 {
2. local file_name `:dis %03.0f `i''.xlsx
3. dis "Appending file `file_name'..."
4. import excel using `"`folder'/`file_name'"', clear firstrow sheet("Results")
5. append using `master'
6. save `master', replace
7. }
Appending file 001.xlsx...
file C:\Users\KHALIL\AppData\Local\Temp\ST_00000001.tmp saved

Appending file 002.xlsx...
file D:\Research Pro\DataFile\Data_Companies\Data_Modified\002.xlsx could not be loaded
r(603);

end of do-file

r(603);

[Hemanshu Kumar]

Have you checked that that file exists?

Also, I don't think this is pertinent to your error, but it is almost never a good idea to run code by entering it line-by-line at the Command window, as you seem to be doing. This can create issues, especially related to macro substitution. Please save a do-file with the entire code, and run that instead.

Furthermore, when listing code on this forum, please use the CODE delimiters, as suggested in the forum FAQ #12.3. This tremendously improves its readability.

[Hemanshu Kumar]

[will the orange text would be changed or will work as it is?]

It will work as-is. Also: you seem to have changed my forward slashes (/) to backslashes (\); this is also inadvisable since it can create problems with macros (backslashes are escape characters in Stata. Please use forward slashes instead. And yes, forward slashes will work fine on Windows machines.