Join and handle multiple input (Excel) files together?
1 view (last 30 days)
Show older comments
Ana Castanheiro
on 26 Mar 2017
Answered: Eeshan Mitra
on 28 Mar 2017
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 = [];
0 Comments
Accepted Answer
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:
0 Comments
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!