Join and handle multiple input (Excel) files together?

2 views (last 30 days)
Hi!
I wrote a few scripts to perform some handling steps and data/plots generation, given my raw data (an Excel file with information on particle composition and morphology). This seems to be working fine (after a looong time working on it), meaning that I'm able to get initial results and illustrations from each Excel file individually.
The question is that I have at total 40 Excel files, and I'd like to be able to run the overall script for those multiple files together. For example, the files Forest_1 and Forest_2 (same location) could be selected together and considered as one dataset itself.
Overall script (the script calling the different scripts):
%%File handling and generating data
Datagenerator % file handling & generating corrected data | output: leaf sample details and particle size range
writetable(Sample_details,'Output.xlsx', 'Sheet',1,'Range','B2:I3')
Sizebin % generation of particle size graphs; refinement of particle size is hereby possible
writetable(PartSize,'Output.xlsx','Sheet',1,'Range','K2:P6')
saveas(f1,'Size_distribution.jpeg')
ElementalComposition
saveas(f2,'Piecharts%M.jpeg')
I use [FileIn,PathIn] = uigetfile(... to get the individual Excel files, but to put the 'Multiselect' option 'on' does not even work, because I wrote all the different scripts based on treating just 1 file. I put the beginning of the first script (Datagenerator) here so that you get an idea. I think that something like this should be possible to do, but I don't know from where to start. Any idea/suggestion is highly appreciated!
%%Importing/correcting Data
% Import the correct Excel sheet
[FileIn,PathIn] = uigetfile('C:\...\*.xlsx',...'Multiselect','off');
FilePath = strcat(PathIn,FileIn);
[tt,myheader] = xlsread(FilePath, 'Sheet1','A1:ZZ1');
T = [tt,myheader]';
% Replace non-numeric cells with 0.0
[~, ~, raw] = xlsread(FilePath, 'Sheet1');
raw = raw(2:end,:);
raw(~cellfun(@isnumeric,raw)) = {0.0};
% Create output variable called matrix
matrix = cell2mat(raw);
% Create output variable
raw = raw(:,[1,3,5,6,7:size(T,1)]);
data = reshape([raw{:}],size(raw));
% Allocate imported array to column variable names
Feature = data(:,1);
AspectRatio = data(:,5);
ECDm = data(:,10);
Shape = data(:,13);
%%Build up composition table, corrected for Oxygen
elem = myheader(1,(21:size(T,1)));
C = matrix(:,(21:size(T,1)));
comp = num2cell(C);
WtCol = not(cellfun('isempty', strfind(elem(1,:), '(Wt%)')));
elem = strrep(elem(1,WtCol),'(Wt%)','');
comp = vertcat(elem,comp);
Composition = cell2table(comp(2:end, :), 'VariableNames', comp(1, :));
Composition.O = [];

Accepted Answer

Eeshan Mitra
Eeshan Mitra on 28 Mar 2017
If I understood it right, you need to cycle through each of the excel files "Forest_1, Forest_2,...", and compile data in a different excel sheet. To do so, count the number of files N, and then use a loop to cycle through each file. This can be done by making the following modification at the start of the 'Datagenerator' script:
[FileIn,PathIn] = uigetfile('C:\...\*.xlsx','Multiselect','on');
for i=1:N
FilePath = strcat(PathIn,FileIn{i});
%rest of your code
end
Choosing multiple excel files at execution with the multiselect option 'on' will result in the variable 'FileIn' being created as a (1 x N) cell array, as opposed to a string when a single file is selected. Please note that this script works only when more than one file is selected. To be able to choose a single file with multiselect 'on' requires additional pre-processing.
You can find more information on 'uigetfile' here:
You can also find more information on accessing cell arrays here:

More Answers (0)

Community Treasure Hunt

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

Start Hunting!