Appending multiple Excel files using script

Question

Sugar Mummy 2022-1-16

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1629825-appending-multiple-excel-files-using-script

评论： Sugar Mummy 2022-1-17

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 个评论
显示 3更早的评论隐藏 3更早的评论

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

Hi @Rik, I have tried the first method given in the link but as I am using MATLAB online I have no idea how to process it further after uploading it on MATLAB drive and how to append the files. Do I have to create variable for each file.

Please guide. Thanks!

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Walter Roberson 2022-1-16

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1629825-appending-multiple-excel-files-using-script#answer_875580

在 MATLAB Online 中打开

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.