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

6 次查看（过去 30 天）

Random User 2022-7-19

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1763270-extracting-specific-data-from-multiple-excel-files-to-create-a-single-excel-file-on-mac

编辑： Random User 2022-10-10

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!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Image Analyst 2022-7-19

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1763270-extracting-specific-data-from-multiple-excel-files-to-create-a-single-excel-file-on-mac#answer_1010425

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

13 个评论
显示 11更早的评论隐藏 11更早的评论

Image Analyst 2022-8-9

编辑：Image Analyst 2022-8-9

在 MATLAB Online 中打开

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.

Random User 2022-10-10

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 2022-10-10