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

24 ビュー (過去 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 件のコメント
Brian Gregory
Brian Gregory 2021 年 5 月 11 日
year month day hour minute
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 件のコメント
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 ExchangeSpreadsheets についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by