How can I merge 100 .xlsx files into one file?

9 次查看（过去 30 天）
Brian Gregory
Brian Gregory 2021-5-11
评论： Brian Gregory 2021-5-12
How can I merge 100 .xlsx files into one master file while keeping the headings? My goal is to do this task without having to individually call each excel file. There are a total of 18 headings; 1 per column. There are also roughly 10k samples in each .xlsx file. i.e. the goal here is to merge all 1 million samples to one excel sheet.
  7 个评论
显示 5更早的评论
Brian Gregory
Brian Gregory 2021-5-11
Here is my code so far.
fileDir = 'defined path (not listed for privacy reasons)';
outfile = 'C:\MASTER.csv';
addpath(fileDir);
fileNames = dir(fileDir);
fileNames = {fileNames.name};
fileNames = fileNames(cellfun(...
@(f)~isempty(strfind(f,'.csv')),fileNames));
for f = 1:numel(fileNames)
fTable = readtable(fileNames{f});
writetable(fTable,outfile,fileNames{f});
end
The only issue with this code right now is that the last line, "writetable()" is returning an error "Wrong number of arguments. A filename must be provided when supplying additional parameters, and each parameter name must be followed by a value."

请先登录，再进行评论。

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

采纳的回答

Walter Roberson
Walter Roberson 2021-5-11
%the below code does NOT assume that the input is numeric.
%the code DOES assume that all files have the same datatype for
%corresponding columns
%output file should be in different place or different extension than the
%input file, unless you add extra logic to filter the file names. For
%example if there is a constant prefix you could add that into the dir()
%call
outputname = 'all_data.xlsx';
make_sure_on_order = false;
ext = '.csv'; %according to comment, but .xlsx according to Question
dinfo = dir(['*' ext]);
filenames = {dinfo.name};
%if the name prefixes are all the same then given yyyy-MM-dd format, you
%should be able to just sort the names. But there might be reasons you need
%to process the time strings and sort based on them. For example at some
%point in the future or for some other person reading this code, the time
%component order might be different
if make_sure_on_order
[~, basenames, ~] = fileparts(filenames);
timestrings = cellfun(@(S) S(end-15:end), basenames, 'uniform', 0);
dt = datetime(timestrings, 'InputFormat', 'yyyy-MM-dd HH-mm', 'format', 'yyyy-MM-dd HH:mm');
[~, idx] = sort(dt);
filenames = filenames(idx);
else
filenames = sort(filenames);
end
nfile = numel(filenames);
datacell = cell(nfiles,1);
for K = 1 : nfile
datacell{K} = readtable(filenames{K});
end
all_data = vertcat(datacell{K});
writetable(all_data, outputname);
  10 个评论
显示 8更早的评论
Walter Roberson
Walter Roberson 2021-5-12
When you test, if the order of output looks acceptable, then you can remove the logic that deals with timestrings and sorting based on time, using just
filenames = sort(filenames);
However if you think at some point in the future that you might need to use filenames with inconsistent prefixes, then keep the logic.
Brian Gregory
Brian Gregory 2021-5-12
Walter, this works fantastically. Thank you so much for all your help!

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by