How do I extract each excel file from their subfolders and combine them into one?

Hey All,

Let me preface this with, I am really new with Matlab--

I am woking with AIS shipping data... It is really tedious, but I have a folder with the year (ie 2018) and then those folders have a subfolder (one of each day of the year). Each of those sub folders has an excel file with A LOT of shipping data. I want to extract each of those excel files and combine them with only specific columms (ie B to D), one of which isn't next to the other columns (H) unfortunately. This way I can compare it to some other orginal data that I have.

Any help and advice is greatly apperciated.. To be honest I am not even fully sure how to start. I have been trying to google different methods for a couple days now.

thanks in advance!

Answer 1

[Voss]

Here is a general approach, but it may need to be modified depending on what exactly is in your files.

rootdir = 'C:\path\to\your\folder';

% get info about all xlsx files in directories directly under 
% rootdir (this matches the description of your situation):
filelist = dir(fullfile(rootdir,'*\*.xlsx'));

% do this instead if you want get info about all xlsx files in all 
% sub-directories within rootdir (including rootdir itself),
% i.e., search the entire directory tree of rootdir:
% filelist = dir(fullfile(rootdir,'**\*.xlsx'));

for ii = 1:numel(filelist)
    % read each file, store the results:
    filelist(ii).data = readcell(fullfile(filelist(ii).folder,filelist(ii).name));
    % keep only columns B, C, D, H:
    filelist(ii).data = filelist(ii).data(:,[2 3 4 8]);
end

% combine all the data, one after the other, vertically:
all_data = vertcat(filelist.data);

% it is a good idea to write the resulting combined dataset to a directory
% outside of where you searched for the data in the first place, so that 
% next time you run this, the combined dataset won't be found as one of 
% the xlsx files to be read and combined.
outputdir = 'C:\path\to\some\output\folder';
writecell(all_data,fullfile(outputdir,'all_data.xlsx'));

[Naomi Mathew]

Thank you!! I really apperciate all the comments for each line!