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

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.

[Walter Roberson]

Do I understand correctly that there are 18 rows per xlsx file, each of which starts with a row header? And that there are on the order of 560 columns in each file?

Are the row headers different for each file? If not then should the output only have a single row header (so strip out the row header from 2nd file onward) ? Can we assume that the row headers are all in the same order? Is the output to be 18 rows x 1+((560-1) * 100) columns -- if so then you going to exceed Excel column limits (maximum 16384)? Or should it be (18*100) x 560 ?

[Brian Gregory]

Hi Walter,

No, that's my mistake. There are 18 COLUMNS and each COLUMN has a different header. There are roughly 10,000 rows per excel file and roughly 100 files in the folder.

[Walter Roberson]

Okay, so are you looking for (10000 * 100) x 18 output? Or are you looking for 10000 x (18 * 100) output?

Also, what order of files is to be used, or is the source file to be written in as part of the data?

I ask about order of files because it is common for people to have numbered files with no leading zeros, such as data1, data2, data3, ... data9, data10, data11, ... But the default order you would get back if you used dir() would probably be data1, data10, data100, data1000, data101, data102, ... data199, data2, data20 ...

[Brian Gregory]

I am looking for an output of 18 columns by 1 million rows. As of right now, each file has 18 columns by 10,000 rows. So basically I want to put each file one after the other in the master sheet. Files should be implemented from first to last in the data file. Right now the files are labeled by date and time. I want to put these files in order respective to time within the same sheet. An example name is "Data 2021-05-05 00-00.csv". This is on an hours timescale vs. AM/PM.

[Walter Roberson]

2021-05-05 00-00 ... is that year month day hour minute ? Or is it year day month hour minute ?

[Brian Gregory]

year month day hour minute

[Brian Gregory]

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."

Answer 1

[Walter Roberson]

%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);

[Brian Gregory]

Okay, thank you so much for this.

So I ran this code and there was an error message that said to change "writetable" to "writematrix". I did this and it created this excel file. However, the file is completely empty when opened in excel. How can I fix this?

[Walter Roberson]

Please check filenames; I have a suspicion that no files were found, and so that all_data is coming out empty.

Please be sure to modify the assignment to ext according to what you have; your comment differed from your original question so I cannot tell which one is correct for you.

[Brian Gregory]

I am sorry but I am confused. How do I check filenames? If no files were found, how can I point the code in the direction of the files to search? Also, what do you mean by "modify the assignment to ext"?

[Walter Roberson]

My code has

ext = '.csv';    %according to comment, but .xlsx according to Question

This tells the code to search for .csv files . If your files are actually xlsx files change to

ext = '.xlsx';

To check filenames, look at

size(filenames)

and see if the variable named filenames ends up empty, which would indicate that no files were found by the dir() call.

[Brian Gregory]

The files are .csv files. The size(filenames) returns empty.

I guess I am just confused about how the program knows where to pull the data from if the file is not called. This might help fix the empty filenames error. I am running the exact same code you sent and nothing added.

[Walter Roberson]

In the below, assign to file_directory the name of the folder that the files are stored in. You can use a relative path if you like.

%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

file_directory = '/location/of/files';   %can be '.' if in current directory

outputname = 'all_data.xlsx';
make_sure_on_order = false;

ext = '.csv';    %according to comment, but .xlsx according to Question
dinfo = dir( fullfile(file_directory, ['*' ext]) );
filenames = fullfile({dinfo.folder}, {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);

if nfile == 0
   error('No *%s files found in "%s"', ext, file_directory);
end

datacell = cell(nfiles,1);

for K = 1 : nfile
  datacell{K} = readtable(filenames{K});
end

all_data = vertcat(datacell{K});

writetable(all_data, outputname);

[Brian Gregory]

Okay, thank you so much. All worked smoothly now.

One issue: This code is only creating the data file with 1 of the 100 excel files in the file folder. How can I merge all of the excel files into the "all_data.csv" master sheet?

[Walter Roberson]

Opps,

all_data = vertcat(datacell{K});

should be

all_data = vertcat(datacell{:});

[Walter Roberson]

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]

Walter, this works fantastically. Thank you so much for all your help!