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

193 views (last 30 days)
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.
  9 Comments
Mobolaji Aduramo Sodunke
Mobolaji Aduramo Sodunke on 22 May 2022
I have an excel folder name 'GOBIERNO' that has about 86 files inside.I have tried to write a code.
I came up with this code;
location=('C:\Users\User\Desktop\GOBIERNO');
dir(location);
NOTE: This code has fetched all the documents in the named folder into MATLAB.My main challenge is that how do i extract the data into a single worksheet using MATLAB.Please,this is very urgent.

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!

Translated by