MATLAB Answers

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

249 views (last 30 days)
ahmed obaid
ahmed obaid on 22 Jan 2016
Commented: The_Rookie on 27 Jun 2018
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 .

  0 Comments

Sign in to comment.

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

Show 5 older comments
ahmed obaid
ahmed obaid on 23 Jan 2016
thank you for all participators .. really you are help me , thanks
Kasih Ditaningtyas Sari Pratiwi
Hi @Walter. I want to ask about how to merge multiple xls files into a single file with the same worksheet. I read some questions which also have the same problem, but mostly the resulted file has separate worksheets. In my case, I want to merge the table so that it only has one worksheet. I try this code below:
[files,folder] = uigetfile('*.xls','Select Files','MultiSelect','on');
output = fullfile(folder,'rainfall.xls');
addpath(dir);
for i = 1:numel(files)
ftab = readtable(files{i});
writetable(ftab,output,'Sheet',files{i});
end
but of course it has separated worksheets. I want to make it in one worksheet as the files have also the same structure so it should be easily to merge. Do you know what should I modify in my code above? I really appreciate for your help.
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)


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,......
.
.
.
.
.

  0 Comments

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!