How to read multiple Excel files by a certain name sequence?

Hi everyone,

I have hundreds of Excel files in one folder that I would like to read into Excel and do some other analyses. The files are named by dates, such as:

19951229.xls
19960131.xls
19960228.xls
19960330.xls
......
19970130.xls
19970226.xls
......

Basically the names are by the format 'YYYYMMDD'. However, 'DD's are different from month to month, like '19960131' and '19970130'. I would like to read the files one by one and conduct the same data analyses for each. So how can I write a program to read all the files by the time sequence without repeating the program?

Answer 1

[Azzi Abdelmalek]

Example

folder='E:\Matlab';
filetype='*.xls';  % or xlsx
f=fullfile(folder,filetype);
d=dir(f);
for k=1:numel(d);
  data{k}=xlsread(fullfile(folder,d(k).name));
end

[Zhe]

Hi Azzi. Thanks for your prompt reply! I have some further questions.

1) What if the files are not sorted by name but I want to read from the earliest? For example, the files are listed like:

19960228.xls 19960330.xls 19951229.xls 19960131.xls 19970226.xls ......

(2) I would like to make the code more generic. For example, I can choose the period within which the files are to be read, like from 199608 to 199809. How may I do that?

[Azzi Abdelmalek]

files={'19960228.xls' '19960330.xls' '19951229.xls' '19960131.xls' '19970226.xls'} 
names=sort(cellfun(@(x) str2double(regexp(x,'\d+','match')),files))
initial_date=19960131;
end_date=19960330;
file_to_read=files(names>=initial_date & names<=end_date)

[zack smith]

Hi Azzi

I used your code to read an excel sheet , actually one excel sheet in folder and I have many folders. I am attaching the code:

clc; clear ;

RT=200;

ST=40;

for GG=[13 19 21 23]

    for i=26:30;

folder=['C:\Folder \XXXX_XXX_XX_',num2str(GG),'_XX_X_XXX_',num2str(i)]; filetype='*.xlsx'; % or xlsx

        f=fullfile(folder,filetype);

        d=dir(f);

        for k=1:numel(d);

            data{k}=xlsread(fullfile(folder,d(k).name),1,'C3:C12');
        end

    end
           valueofA(i) = data;

end

my questions are:

1- I evaluated numel(d) and it gives 1, it looked strange to me since there is a loop. I know d is a structure and here where I miss it. 2- I want to save the output in a variable let call it valueiofA, How to do this for cell array. 3- When the loop from 26:30 is done I want to save the values in a mat file called as :

NUT(GG)_RTST, so for example :NUT21_20040.

Thank you

ZYS

[Hannah Chambles]

I have the same problem, and this solution worked for me as well! Is there a way to specify a range of cells to extract from all of the excel files? It's the same range for every file.