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

14 views (last 30 days)
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 Comments
Brian Gregory
Brian Gregory on 11 May 2021
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."

Sign in to comment.

Accepted Answer

Walter Roberson
Walter Roberson on 11 May 2021
%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 Comments
Walter Roberson
Walter Roberson on 12 May 2021
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 on 12 May 2021
Walter, this works fantastically. Thank you so much for all your help!

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!