Clear Filters
Clear Filters

Reading Multiple Excel Files, Analyzing and Indexing

7 views (last 30 days)
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
Correy
Correy on 28 May 2024
@Stephen23 These are excel spreadsheets that are saved as .CSV by a software package that I used to collect data. Would it technically change the process a lot if I were to actually use .xls/x files?
Stephen23
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.

Sign in to comment.

Answers (2)

Stephen23
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]

Peter Perkins
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
Warning: The assignment added rows to the table, but did not assign values to all of the table's existing variables. Those variables are extended with rows containing default values.
Warning: The assignment added rows to the table, but did not assign values to all of the table's existing variables. Those variables are extended with rows containing default values.
Tout
Tout = 2x3 table
File Mean Std ________ __________________ __________________ Var1 Var2 Var1 Var2 _______ _______ _______ _______ "t1.csv" 0.38466 0.43901 0.28997 0.32914 "t2.csv" 0.35267 0.5149 0.23389 0.34535
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.

Categories

Find more on Data Import from MATLAB in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!