Extracting specific data from multiple excel files to create a single excel file on MAC

Hi all I have a folder on my computer with 300 excel files and I don't want to manually extract the middle row from every excel file manually and combine into a single excel file. The rows differ in each file ranging from 4-6, if the row amount is 4 I would like to get the second row.

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 columns from the middle row and then combining all that into a excel file?

I feel like some of the fucntions on a mac are limited compared to a PC.

Thanks in advance for the help!

Answer 1

[Image Analyst]

Try the FAQ:

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

In the middle of the loop, use readmatrix to read in the file and then extract the row(s) you want into a new array. Then after the loop, write out the new array with writematrix

Write back if you can't figure it out and attach a couple of your workbooks with the paperclip icon after reading this:

TUTORIAL: How to ask a question (on Answers) and get a fast answer

[Walter Roberson]

or readtable() if you have mixed data types

[Walter Roberson]

dinfo = dir('*.xls');
filenames = {dinfo.name};
numfiles = length(filenames);
all_row2 = [];
for K = 1 : numfiles
    data = readtable(filenames{K});
    if height(data) == 4
        all_row2(end+1,:) = data(2,:);
    end
end

[Walter Roberson]

dinfo = dir('*.xls');
filenames = {dinfo.name}.';
numfiles = length(filenames);
all_row2 = [];
for K = 1 : numfiles
    data = readtable(filenames{K});
    if height(data) == 4
        all_row2(end+1,:) = data(2,:);
    end
end

output = [table(filenames), all_row2];
writetable(output, 'NameOfFileGoesHere.xlsx')

[Walter Roberson]

dinfo = dir('*.xls');
filenames = {dinfo.name}.';
numfiles = length(filenames);
all_row2 = [];
for K = 1 : numfiles
    data = readtable(filenames{K});
    if height(data) == 4
        if isempty(all_row2)
            all_row2 = data(2,:);
        else
            all_row2(end+1,:) = data(2,:);
        end
    end
end

if isempty(all_row2)
    error('No files with 4 rows found');
end

output = [table(filenames), all_row2];
writetable(output, 'NameOfFileGoesHere.xlsx')

[Image Analyst]

Try this to extract the middle row of all .xls workbooks:

fileList = dir('data *.xls'); % Or *.xls* to get both .xls and .xlsx extensions.
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);
    % Get the middle row of the table.
    midRow = ceil(rows/2);
    fprintf('Extracting the middle row (%d) from workbook %s.\n', midRow, allFileNames{k});
    % Extract the middle row ONLY.
    tMiddle = data(midRow, 2:end);
    % Append middle row to our output table.
    if k == 1
        % Instantiate new table with same fieldnames.
        tAllMiddleRows = tMiddle;	
    else
        % Append to our growing table.
        tAllMiddleRows = [tAllMiddleRows; tMiddle];
    end
end
% Show the final output table values in the command window so we can verify them.
tAllMiddleRows
% Write table to disk as a new Excel .xlsx file.
% writetable(tAllMiddleRows, 'NameOfFileGoesHere.xlsx')

tAllMiddleRows =

2×5 table

Sample CSA_mm_2_ Minor_mm_ Major_mm_ Circularity

____________________________ _________ _________ _________ ___________

{'image_36003148480018.png'} 7.897 3.032 3.317 0.662

{'image_71793960610725.png'} 7.587 2.459 3.928 0.669

>>

Don't worry about the warnings. They're just saying that spaces in the column headers are converted into underlines because variable names can't have spaces in them. If you really want to suppress the warning, see attached file and figure it out.

[mpz]

hi @Image Analyst I have a very similar question but having a hard time figuring out based on answers here. Question found at this link (https://www.mathworks.com/matlabcentral/answers/1777970-extract-specific-rows-and-columns-from-excel-and-store-in-a-matrix?s_tid=prof_contriblnk)

[Random User]

Hi Image Anlayst, I was wondering if I need to retract a row from the column CSA with the minmum value how could I do this. I tried to use the min function however I am finding it difficult to retract the column and row number to store this to then use it to retract that row with the lowest CSA value. Thank you once again :)

[Random User]

CSA_cols = data(:,3);

minimum_val = min(CSA_cols);

[rows, cols] = find(data ==minimum_val);

I tried this however there was an error of using min. invalid data type. first argument must be numerical or logical

[Image Analyst]

What does "retract" mean to you? To me it means to pull back or withdraw. Do you mean remove/delete or extract into a new vector) or something else?

[Random User]

In the intial question I was retracting the middle row of each excel speadsheet, now instead of the middle row I want to take out the row with the lowest CSA value. Then save this in an excel spreadsheet.

I am finding it hard to find the minimum value in the CSA through code.

[Image Analyst]

Still not sure what you mean. Please explain with an example in a new discussion thread. Please explain how the verb definition below applies to a matrix.

retract

1

[ ri-trakt ]

See synonyms for: retract / retracted / retractation on Thesaurus.com

verb (used with object)

to draw back or in: to retract fangs.

verb (used without object)

to draw back within itself or oneself, fold up, or the like, or to be capable of doing this: The blade retracts.

[Random User]

Sorry to clarify - what I mean is to take out not to draw back.

In the orginal question we took out the middle row and now instead of the middle row I would like to retrieve the row with the lowest CSA and combining this for all the files in another excel spreadsheet.

When we open data 1. these are the headings

Sample CSA (mm^2) Minor (mm) Major (mm) Circularity

for the third column CSA I would like to find the minimum value and then save it into another excel file. In the same manner that we answered this question.

[Random User]

https://au.mathworks.com/matlabcentral/answers/1821185-extraction-of-the-third-column-in-multiple-excel-files