Extraction of the third column in multiple excel files

Hi all I have a folder on my computer with 300 txt files and I don't want to manually extract the row of the minimum value in the third column with the header (CSA mm^2) from every excel file manually and combine into a single excel file.

I was wondering how I can do this on Matlab through reading every Excel file in that specific file and taking out all of the rows that contain the min CSA value in the excel and then combining all that into a excel file?

-----

note: the code for retrieving the rows is working - I cant figure out why the min function does not work

Thanks in advance for the help!

I have attached some files. I want to extract the row with the lowest CSA value.

[Benjamin Thompson]

Just for better cyber safety for the Community could you replace XLS files with XLSX?

Answer 1

[Image Analyst]

Try this:

CSA_cols = data(:,3); % Extract column 3
% Find min value
minimum_val = min(CSA_cols);
% Find all rows where the minimum occurs
minRows = find(CSA_cols == minimum_val);
% Get matrix with only those rows
extractedMatrix = data(minRows, :);

To process a sequence of workbook files, see the FAQ:

https://matlab.fandom.com/wiki/FAQ#How_can_I_process_a_sequence_of_files?

[Random User]

I've tried it but I keep getting this error.

[Random User]

% clear all the previous settings
clc;
clear;
close all;

% obtain data - put into the same pathway 
fileList = dir(fullfile('Results/*.txt'));
allFileNames = {fileList.name}.';
numFiles = numel(allFileNames);

for k = 1 : numFiles
    % Read in all the data.
    data = readtable(allFileNames{k});
    % Find out the number of rows.
    rows = size(data, 1);

    if rows == 1 ;

        minRows =1 ;

    else

    CSA_cols = data(:,3); % Extract column 3
% Find min value
minimum_val = min(CSA_cols);
% Find all rows where the minimum occurs
minRows = find(CSA_cols == minimum_val);

    end 

    fprintf('Extracting the middle row (%d) from workbook %s.\n', minRow, allFileNames{k});
    % Extract the min row ONLY (2:5 was used because 2 is sample and
    % some files have extra variables)
    tMin = data(minRows, 2:6);
    % Append min row to our output table.
    if k == 1
        % Instantiate new table with same fieldnames.
        tAllminRows = tMin;	
    else
        % Append to our growing table.
        tAllminRows = [tAllminRows; tMin];
    end
end
% Show the final output table values in the command window so we can verify
% them. DISREGARD THE WARNING.
disp(tAllminRows);

Unrecognized function or variable 'tAllminRows'.

% Write table to disk as a new Excel .xls file.
output = [allFileNames, tAllminRows];
writetable(output, 'MinRowOutput.xls');

[Image Analyst]

Attach at least two of the *.txt files. All you've attached so far are .xlsx files.

[Random User]

They are the same files but as txt

[Image Analyst]

The problem is you have this:

fileList = dir(fullfile('Results/*.txt'));

However, your txt files are not actually text files. They are binary files so readtable gets confused because the filename extension is wrong. Perhaps they are .xlsx files and you should use

fileList = dir(fullfile('Results/*.xlsx'));

[Random User]

All my other files xls are corrupted - so I had to convert them to txt to make it work.

Is there any way to do it for txt files?

When I tried the other code to find the midRow with the txt files it worked perfectly.

fileList = dir(fullfile('Results/*.txt'));

this line worked and I was able to in the end have a excel spreadsheet with all the middle rows.

So it is able to read the files on my end.

I'm just finding it hard to retrieve the minimum CSA row and collate in a excel spreadsheet.

[Image Analyst]

Your other text files were probably actually text files. The ones you attached were probably just .xlsx files with the extension changed. I suggest you fix all your files so that they are all of the same format/extension, and then proceed from there to get a solution that can read them all in.

[Random User]

All my files are the same extension of txt.

So if it were just excel files it would work with what I have?

[Image Analyst]

I don't know the format of your text files. Like I said, they're not text files. If you open them up in notepad, it looks like gibberish. What happened to all your original data - the original .xlsx files - such that they got corrupted? What happens if you try to oepn them in Excel?

[Random User]

Thats weird, when I open it on my end they look fine - try these files

[Image Analyst]

They do look normal in Notepad, but when reading into MATLAB they are gibberish

data = readtable(fullFileName)
data = readmatrix(fullFileName)

readtable() and importdata() give a bunch of nonsense, and readmatrix gives all nans. I think there is something wrong with them. Are they 16 bit unicode characters or something?

Call tech support and give them that file and ask