How can I read Excel files, extract the rows which have top 10% values for a specific column and then write those rows in a new Excel file?

1 次查看（过去 30 天）
Siddhant Chandra
Siddhant Chandra 2018-7-30
回答： Nathan Jessurun 2018-8-17
I have a folder that has multiple Excel files with names like xyz_1, xyz_2 and so on. I have to read each Excel file, extract the rows that have top 10% values for a column, and then write those rows into a new Excel file. I have been able to do the operation for 1 file. Now I am trying to do the operation for multiple files using a loop. But I am not sure how to go about that. For executing the operation for 1 Excel file, I did the following:
  1. I read the Excel file and stored in a matrix.
  2. I sorted the rows in descending order of values in one of the columns.
  3. I read the first 10% of the rows.
  4. I wrote a new Excel file with the rows obtained in #3.
Here is my code:
filename = fullfile(' _General Path_',' _Filename_.csv');
A = xlsread(filename,'A2:E31843'); %Read all cells if the file from A2 to E31843
B = sortrows(A,-5); % Sort the rows based on the descending order of column 5 values
C = B(1:3185,1:5); %Store the first 10% percent rows in a matrix
filename2 = fullfile(' _GeneralPath for new Excel files_',' _Filename_.xls');
xlswrite(filename2,C) %Write the first 10% rows to an Excel file
Can anyone please help me loop this above operation for multiple files (1090 files to be precise)?

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

采纳的回答

Image Analyst
Image Analyst 2018-7-31

更多回答（1 个）

Nathan Jessurun
Nathan Jessurun 2018-8-17
Moving my comment into an answer:
Simply move your existing code into a function. In this case, you could do the following:
function readSingleExcel(filename)
% Your code here
end
In another file (or an additional function in the same file, your choice), call that function:
filenames = {'xyz_1.xls', 'xyz_2.xls'}; % This cell array holds your files
for ii = 1:length(filenames)
readSingleExcel(filenames{ii});
end
If all files are in the same directory, you can make use of the 'dir' function:
fileList = dir('./TopLevelDirectory/*.xls'); % Gets all directory info
% We only want file names
fileList = {fileList.name};

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by