How do I extract each excel file from their subfolders and combine them into one?
39 views (last 30 days)
Show older comments
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!
0 Comments
Accepted Answer
Voss
on 23 Mar 2022
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'));
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!