How can I combine multiple excel files in a single new excel file?

227 views (last 30 days)
ahmed obaid
ahmed obaid on 22 Jan 2016
Commented: Ojo Olusola on 7 Aug 2021
i have 100 excel files that are saved in specific folder ( "D:\excel_folder");with names are [ file1.xls , file2.xls , ..... file100.xls] , each file contain only single sheet contain data , i need to combine all these 100 files in one single file with name of ( master.xls) so each sheet contain the data of single merged file , MASTER.xls sheet1 name = File1.xls data with name of that file so in this master.xls sheet1 name is file1.xls , and second sheet name is file2.xls and contain the data of that second file (file2.xls ) and so on .. till file100.xls .

Accepted Answer

Kirby Fears
Kirby Fears on 22 Jan 2016
Edited: Kirby Fears on 22 Jan 2016
Is D a local disk or a remote network disk? This will go much faster if you save the files locally before trying to read them all.
fileDir = 'D:\excel_folder';
outfile = 'D:\MASTER.xls';
addpath(fileDir);
fileNames = dir(fileDir);
fileNames = {fileNames.name};
fileNames = fileNames(cellfun(...
@(f)~isempty(strfind(f,'.xls')),fileNames));
for f = 1:numel(fileNames),
fTable = readtable(fileNames{f});
writetable(fTable,outfile,'Sheet',filenames{f});
end
Each sheet of MASTER.xls should now have the same name as the file that contained the data.
Hope this helps.
  8 Comments
The_Rookie
The_Rookie on 27 Jun 2018
I have two quetions:
1)I am getting an error that fileNames is undefined, what is the best course of action? 2)could someone possibly explain the "cellfun(..." part of the code?
Thank you!

Sign in to comment.

More Answers (2)

Vijal Gala
Vijal Gala on 11 Aug 2017
Edited: Walter Roberson on 12 Aug 2017
I am getting a Warning:
Warning: Added specified worksheet.
> In xlswrite>activate_sheet at 284
In xlswrite>ExecuteWrite at 256
In xlswrite at 214

Chakradhar Rao Tandule
Chakradhar Rao Tandule on 21 Sep 2017
Edited: Walter Roberson on 21 Sep 2017
I too want such program but i have data in the different work sheets....
I want to combine the same name worksheets of different excel files into a single excel with different worksheets....
i.e.,
2004m0101.xls>>sheet1,sheet2,sheet3,PI,WCI,NWI,NI,NCI,NEI,NBOB,SBOB,NAS,SAS
2004m0102.xls>>sheet1,sheet2,sheet3,PI,WCI,NWI,NI,NCI,NEI,NBOB,SBOB,NAS,SAS
2004m0103.xls>>sheet1,sheet2,sheet3,PI,WCI,NWI,NI,NCI,NEI,NBOB,SBOB,NAS,SAS
.
.
.
.
.
TO
a single file like
PI.xls>>2004m0101,2004m0102,2004m0103,......
WCI.xls>>2004m0101,2004m0102,2004m0103,......
.
.
.
.
.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!