Reading Multiple Excel Files, Analyzing and Indexing
7 views (last 30 days)
Show older comments
I am trying to make a program to read excel files, calculate averages and STDEV of specific parts of the table. I have the loop for reading the files written here but I don't know how to index my loop so I get an individual matrix of values for each spreadsheet read.
xlFiles = dir('*.csv') ;
N = length(xlFiles) ;
for i = 1:N
thisFile = xlFiles(i).name ;
T = readtable(thisFile) ;
average =
end
4 Comments
Stephen23
on 28 May 2024
"Would it technically change the process a lot if I were to actually use .xls/x files?"
If the CSV files are well formatted then the only change would be to change the file extension in the DIR call.
Answers (2)
Stephen23
on 28 May 2024
Edited: Stephen23
on 28 May 2024
Use indexing into the structure array that you already have (i.e. the output from DIR):
P = 'absolute or relative path to where the files are saved';
S = dir(fullfile(P,'*.csv'));
for k = 1:numel(S)
F = fullfile(P,S(k).name);
T = readtable(F);
.. your calculations here
S(k).avg = .. your average
S(k).std = .. your standard deviation
end
The data will be stored in the structure S and is easy to access using indexing. For example, the 2nd file:
S(2).name % filename
S(2).avg % average
S(2).std % standard deviation
Optionally (assuming compatible sizes and classes, suitable file order) you can easily concatenate the data together, e.g.:
avg_matrix = [S.avg]
std_matrix = [S.std]
0 Comments
Peter Perkins
on 28 May 2024
In a fairly recent (most recent?) version of MATLAB, you can do something like the following. First create some fake CSV files:
t1 = array2table(rand(10,2));
t2 = array2table(rand(10,2));
writetable(t1,"t1.csv");
writetable(t2,"t2.csv");
clear t1 t2
Now read them in and compute means and std devs. You don't give enough information, but perhaps something like this:
xlFiles = dir('*.csv');
emptyTable = table(zeros(0,1),zeros(0,1)); % this should look like whatever the CSV files contain
Tout = table(strings(0,1),emptyTable,emptyTable,VariableNames=["File" "Mean" "Std"]);
for i = 1:length(xlFiles)
thisFile = xlFiles(i).name;
T = readtable(thisFile);
Tout.File(i) = thisFile;
Tout.Mean(i,:) = mean(T);
Tout.Std(i,:) = std(T);
end
Tout
Notice that I've just called mean and std on the tables. In earlier versions, you could not do that, so you'd need to do something like mean(T{:,:}) and pre-allocate Tout to contain 0x2 numeric matrices, not emptyTable.
0 Comments
See Also
Categories
Find more on Data Import from MATLAB 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!