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

조회 수: 38 (최근 30일)
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!

채택된 답변

Voss
Voss 2022년 3월 23일
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'));

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by