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

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 .

Answer 1

[Kirby Fears]

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.

[ahmed obaid]

thanks sir , but i got the following error what i have to do please ? i have used Matlab 2013 is this function work on it ?

Undefined function 'readtable' for input arguments of type 'char'.

[Walter Roberson]

Which MATLAB version are you using? readtable() has been present since R2013b.

[ahmed obaid]

i have used MATLAB version R2013a ? is this work on it ?

[Walter Roberson]

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),
    [~, ~, raw] = xlsread( fullfile(fileDir, fileNames{f}));
    xlswrite(outfile, raw, fileNames{f});
end

[ahmed obaid]

Thank you very much for your great job , this code work perfectly .. thanks a lot ...

[ahmed obaid]

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]

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!

Answer 2

[Vijal Gala]

I am getting a Warning:

Warning: Added specified worksheet. 
> In xlswrite>activate_sheet at 284
  In xlswrite>ExecuteWrite at 256
  In xlswrite at 214

[Walter Roberson]

https://www.mathworks.com/matlabcentral/newsreader/view_thread/159455

Answer 3

[Chakradhar Rao Tandule]

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