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

148 ビュー (過去 30 日間)
ahmed obaid
ahmed obaid 2016 年 1 月 22 日
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 .

採用された回答

Kirby Fears
Kirby Fears 2016 年 1 月 22 日
編集済み: Kirby Fears 2016 年 1 月 22 日
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 件のコメント
The_Rookie
The_Rookie 2018 年 6 月 27 日
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!
Mobolaji Aduramo Sodunke
Mobolaji Aduramo Sodunke 2022 年 5 月 22 日
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.

サインインしてコメントする。

その他の回答 (2 件)

Vijal Gala
Vijal Gala 2017 年 8 月 11 日
編集済み: Walter Roberson 2017 年 8 月 12 日
I am getting a Warning:
Warning: Added specified worksheet.
> In xlswrite>activate_sheet at 284
In xlswrite>ExecuteWrite at 256
In xlswrite at 214
  2 件のコメント
Ojo Olusola
Ojo Olusola 2021 年 8 月 7 日
can these be resolve.
Error using xlswrite (line 211)
Invalid data range: ABEOKUTA.XLSX.
Error in mres1 (line 14)
xlswrite(outfile,fTable,'Sheet',fileNames{f});

サインインしてコメントする。


Chakradhar Rao Tandule
Chakradhar Rao Tandule 2017 年 9 月 21 日
編集済み: Walter Roberson 2017 年 9 月 21 日
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,......
.
.
.
.
.

カテゴリ

Help Center および File ExchangeSpreadsheets についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by