exporting-​to-excel-s​preadsheet​s

Hello, I am trying to do what I feel is very basic task. I have numerous excel files. Parameter name across the top row, data point from row 2:xxxx.

What I want to be able to do is go through a number of files, grab the filenames, column x name for title, and say search for the highest poinit in that column, or calculate average of that column.

I'm struggling to really understand how to do this, I'm no programmer, that is 100% certain. If I could get something put together to do this I feel that it could help to me process a majority of the files I work with, not doing this exact task, but very similar. I've not had the best of luck finding responses by searching forums, etc to do this for people who don't understand programming, so I'm looking for an idiots guide to how to do basic tasks like this with matlab. If anyone has something they think would help, please post up.

Thanks!

Answer 1

[Cris LaPierre]

You will need to load each file into MATLAB in order to process the data. There are various techniques depending on how your data is organized.

I'd start with the answer recommended in this answer: https://www.mathworks.com/matlabcentral/answers/473494-using-readtable-to-load-excel-files-in-a-different-folder

folder_in='Formatted_Excel';                               % directory of interest
d=dir(fullfile(folder_in,'*.xls*'));                        % return the .xls files in the given folder
for i=1:numel(d)
  P=readtable(fullfile(folder_in,d(i).name)); 
  ...
  % do whatever w/ the i-th file here before going on to the next...
  ...
end    

[Cris LaPierre]

My approach would be to read in all the files first using a datastore, inserting the filename as a new table variable, then using groupsummary to compute the min, max, and average for the indicated variables, grouped by filename.

folder_in='./';                               % directory of interest
dsFiles = fileDatastore(fullfile(folder_in,'*.txt*'),"ReadFcn",@loadFiles,"UniformRead",true);

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

data = readall(dsFiles);

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

The final table will have one row for each file loaded containg a column for each stat for each variable.

• G = groupsummary(T,groupvars,method,datavars)

Tsum = groupsummary(data,"Filename",["min","max","mean"],["RPM", "Batt_Voltage", "Water_Temp"])

Tsum = 1x11 table
            Filename            GroupCount    min_RPM    max_RPM    mean_RPM    min_Batt_Voltage    max_Batt_Voltage    mean_Batt_Voltage    min_Water_Temp    max_Water_Temp    mean_Water_Temp
    ________________________    __________    _______    _______    ________    ________________    ________________    _________________    ______________    ______________    _______________

    {'Test_Results_MY1.txt'}       171           0       766.34      613.24          12.87               13.33               13.203              71.38             73.56             72.947     

% Function to load files to a table and add the filename to a new variable
function T = loadFiles(fname)
T=readtable(fname);
[filepath,name,ext] = fileparts(fname);
T.Filename(:) = {[name ext]};
end

Once you have the results you want, you can write them to a spreadsheet using writetable.

writetable(Tsum,'Result_stats.xlsx')

[my8950]

This looks like what I am trying to accomplish, so now I've got to figure out how to do what you're saying. :)

[Cris LaPierre]

Copy the code, update the folder path specified so that it points to the folder containing your files, update the file extension wildcard if necessary, and then run the code. Let us know if the results aren't what you expected.

[my8950]

Ok, so I gave this a shot and tried to piece it together. When I run, I am getting the error -

Error: File: excelprocessor.m Line: 19 Column: 1

Function definitions in a script must appear at the end of the file.

Move all statements after the "loadFiles" function definition to before the first local function definition.

I did see that I can run this with matlab online and it does work, so I feel like it's getting close.

I've attached a screenshot and the m-file to this message.

[Cris LaPierre]

Ah, you have an older version of MATLAB than me. The local function must be moved to the bottom of the script.

Also note that the path used here means the files to be loaded are in the current folder.

folder_in='./';                               % directory of interest
dsFiles = fileDatastore(fullfile(folder_in,'*.txt*'),"ReadFcn",@loadFiles,"UniformRead",true);
data = readall(dsFiles);
Tsum = groupsummary(data,"Filename",["min","max","mean"],["RPM", "Batt_Voltage", "Water_Temp"])
writetable(Tsum,'Result_stats.xlsx')

% Function to load files to a table and add the filename to a new variable
function T = loadFiles(fname)
T=readtable(fname);
[filepath,name,ext] = fileparts(fname);
T.Filename(:) = {[name ext]};
end

[my8950]

I am using R2023b, and yes, as of now I've got the m-file in the same location as the sample files to test with. At least I've got that part right. :)

[my8950]

Success...I appreciate your help and patience with this. It appears to be working as I need. Now I can take this and tune it up to fit my needs as they evolve. I really need that idiots guide to help me understand this stuff and use it better.

[Cris LaPierre]

We share an example of this type of workflow in this video from the Data Processing with MATLAB specialization on Coursera. You can enroll for free, and might find the rest of the content helpful as you try to learn MATLAB.

[my8950]

I'll check it out and see what I can find, thank you for this info!

Answer 2

[Walter Roberson]

datadir = '.';  %path to data files
dinfo = dir(fullfile(datadir, '*.xslx'));
filenames = fullfile({dinfo.folder}, {dinfo.names});
numfiles = numel(filenames);
results = table('Size', [numfiles, 4], 'VariableTypes', ["cell", "cell", "cell" "cell"], 'VariableNames', ["varnames", "maxval","maxidx","avg"]);
for K = 1 : numfiles
    thisfile = filenames{K};
    T = readtable(thisfile);
    data = T{:,:};
    [maxval, maxidx] = max(data);
    avg = mean(data,1);
    results.varnames = T.Properties.VariableNames;
    results.maxval(K) = {maxval};
    results.maxidx(K) = {maxidx};
    results.avg(K) = {avg};
end

This code does not assume that each file has the same number of columns or that the columns are in the same order.

This code does assume that the tables contain only numeric data.

This code does not assume that there is only one data column.

If I have coded correctly, then the end result should be a table with one row per file. The table should have four variables, "varnames", "maxval", "maxidx", and "avg". The entries in "varnames" should be the colum names for each file. The entries in maxval should be the maximum value for each row; the entries in maxidx should be the row index that the maximum value occurred; the entries in "avg" should be the average of each column.

The code would be more simple if it could be assumed that there is only one variable in each file and the one variable is always the same variable -- or if the it could be assumed that only one particular variable name is to be extracted from each file.

Answer 3

[my8950]

Thank you both for responding. I will have to take a hard look at these to try and get a better understanding. If there is a way to, I could probably post a sample for reviewing. I really need to figure this out though, as mentioned, I think it could be modified a touch and used across multiple file types which would make things much faster for processing.

[Cris LaPierre]

Use the paperclip icon to attach your file to your post.

[my8950]

@Cris LaPierre, found it, thank you!

Attached is an abbreviated sample file for reference. Example, I'd want to pull 3 names from Row1, RPM, Batt_Voltage and Water_Temp. Out of those, I'd want to find say, min, max and average. I'd have something like, "X"_Min, "X"_Max, "X"_Average to take from the data file and move to a seperate file, so that once I go through all of the data files to process, I could just look at my output file after MatLab processed and see the Filename, Min's, Max's, and Averages for each data file.

It seems so basic to do, but actually getting where I want to be is a little overwhelming. Thanks everyone for your help!