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?

3 views (last 30 days)
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)?

Accepted Answer

Image Analyst
Image Analyst on 31 Jul 2018

More Answers (1)

Nathan Jessurun
Nathan Jessurun on 17 Aug 2018
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};

Community Treasure Hunt

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

Start Hunting!