Appending multiple Excel files using script
显示 更早的评论
Hello everyone! I want to append 500 excel files. IS there any way through coding to complete the desired task. Any help or suggestion would be highly appreciated.
Thanks
5 个评论
Sugar Mummy
2022-1-16
Sugar Mummy
2022-1-16
Rik
2022-1-16
It didn't answer the entire question, but it did give you a hint where to start.
What have you tried?
Sugar Mummy
2022-1-16
采纳的回答
Walter Roberson
2022-1-16
input_dir = '/MATLAB Drive/Project11/xin'; %directory excel files are stored in
input_extension = '.xlsx'; %or .xls
output_file = '/MATLAB Drive/Project11/combined.xlsx'; %name of output file in a different directory
dinfo = dir( fullfile(input_dir, "*" + input_extension) );
filenames = fullfile({dinfo.folder}, {dinfo.name});
nfiles = length(filenames);
for K = 1 : nfiles
data = readcell(filenames{K});
if K == 1
writecell(data, output_file, 'writemode', 'overwritesheet');
else
writecell(data, output_file, 'writemode', 'append');
end
end
Assumptions:
- Only sheet 1 of the data is to be copied
- all of the sheet is to be copied every time. So if there is a common header for each file, the header will appear each time, not just at the top of the new file
- the code does not assume that the files have the same number or type of variables. If one has (say) 7 columns and the next has (say) 11 columns, then the code is fine with that
- any macros in the files will be copied, but no attempt is made to fix-up cell references
- the order copied will be internal directory order as returned by the operating system. If the files are named inconsistently such as Sand1.xlsx Sand2.xlsx ... Sand9.xlsx Sand10.xlsx Sand11.xlsx ... then the directory order would typically be Sand1.xlsx Sand10.xlsx Sand11.xlsx ... Sand19.xlsx Sand2.xlsx Sand20.xlsx ... This code makes no attempt to order the files numerically
- The code makes no attempt to add information into the composite file in order to mark the boundaries between files or in order to indicate which file the data was originally from
- As you asked about "append" rather than "merge", the data is just all put one file after another -- not, for example, using different sheets for each different file.
3 个评论
Image Analyst
2022-1-16
Do you know if MATLAB launches Excel, tosses in the data, and shuts down Excel every time you call writecell()? Or does it leave Excel running as a hidden server, which would be faster than if it had to launch it every time?
Walter Roberson
2022-1-16
writetable() was documented as caching the connection, so I would imagine that writecell() does as well.
Sugar Mummy
2022-1-17
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
产品
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
