Thread Subject:

xlsread multiple xls files?

I currently use xlsread to read data into my program. I have about a
hundred Excel files that need to be read. Is there any way to tell
my program to go into the folder with all the Excel files and
sequentially read each file, run the program on the file and then go
to the next file? thanks in advance, Jorie

J favreau wrote:
>
>
> I currently use xlsread to read data into my program. I have about
> a
> hundred Excel files that need to be read. Is there any way to tell
> my program to go into the folder with all the Excel files and
> sequentially read each file, run the program on the file and then
> go
> to the next file? thanks in advance, Jorie

You can try something like:

%use cd to change to the directory you want

FileNames=dir('*.xls);
%this gets all the file names in current director that have extension
.xls, as a structure

for i=1:length(FileNames)
FileToLoad=FileNames(i).name;
%this is a xls file, load it, run code and loop
end

HTH!!

%use cd to change to the directory you want
>
> FileNames=dir('*.xls);
> %this gets all the file names in current director that have
> extension
> .xls, as a structure
>
> for i=1:length(FileNames)
> FileToLoad=FileNames(i).name;
> %this is a xls file, load it, run code and loop
> end
>
> HTH!!

small error in this code

FileNames=dir('*.xls');

I forgot the ' in the first post.

great, thanks!

a. mosher wrote:
>
>
> %use cd to change to the directory you want
>>
>> FileNames=dir('*.xls);
>> %this gets all the file names in current director that have
>> extension
>> .xls, as a structure
>>
>> for i=1:length(FileNames)
>> FileToLoad=FileNames(i).name;
>> %this is a xls file, load it, run code and loop
>> end
>>
>> HTH!!
>
> small error in this code
>
> FileNames=dir('*.xls');
>
> I forgot the ' in the first post.

"J favreau" <jmfavrea@unity.ncsu.edu> wrote in message <eed426f.2@webx.raydaftYaTP>...
> great, thanks!
>
> a. mosher wrote:
> >
> >
> > %use cd to change to the directory you want
> >>
> >> FileNames=dir('*.xls);
> >> %this gets all the file names in current director that have
> >> extension
> >> .xls, as a structure
> >>
> >> for i=1:length(FileNames)
> >> FileToLoad=FileNames(i).name;
> >> %this is a xls file, load it, run code and loop
> >> end
> >>
> >> HTH!!
> >
> > small error in this code
> >
> > FileNames=dir('*.xls');
> >
> > I forgot the ' in the first post.

Hi Favreau,

I tried something similar before, until I found this thread an hoped to get a solution for my problem. But it still doesn't work for me.

When I tell matlab to search all files with endings .xlsx, I get a matrix with all information about my excel file, including their filenames.
As you explained, I decalare a variable called FileToLoad, with the name of my excelfile.
When I then try to use xlsread('FileToLoad') it doesnt work cause matlab can't find a file called FileToLoad.
Thats right so far, but why doesnt matlab simply use the information from the variable 'FileToLoad' instead... for example (mydata.xlsx).
Seemingly it worked for you, but I cant figure out why, so I would appreciate if you can help me with my problem.

Thanks

Johan

"Johan" wrote in message <k6om7t$30b$1@newscl01ah.mathworks.com>...
> "J favreau" <jmfavrea@unity.ncsu.edu> wrote in message <eed426f.2@webx.raydaftYaTP>...
> > great, thanks!
> >
> > a. mosher wrote:
> > >
> > >
> > > %use cd to change to the directory you want
> > >>
> > >> FileNames=dir('*.xls);
> > >> %this gets all the file names in current director that have
> > >> extension
> > >> .xls, as a structure
> > >>
> > >> for i=1:length(FileNames)
> > >> FileToLoad=FileNames(i).name;
> > >> %this is a xls file, load it, run code and loop
> > >> end
> > >>
> > >> HTH!!
> > >
> > > small error in this code
> > >
> > > FileNames=dir('*.xls');
> > >
> > > I forgot the ' in the first post.
>
> Hi Favreau,
>
> I tried something similar before, until I found this thread an hoped to get a solution for my problem. But it still doesn't work for me.
>
> When I tell matlab to search all files with endings .xlsx, I get a matrix with all information about my excel file, including their filenames.
> As you explained, I decalare a variable called FileToLoad, with the name of my excelfile.
> When I then try to use xlsread('FileToLoad') it doesnt work cause matlab can't find a file called FileToLoad.
> Thats right so far, but why doesnt matlab simply use the information from the variable 'FileToLoad' instead... for example (mydata.xlsx).
> Seemingly it worked for you, but I cant figure out why, so I would appreciate if you can help me with my problem.
>
> Thanks
>
> Johan

Sorry, I mixed it up, i meant A. Mosher... but if you have some hints for me too, I wouldn't reject them

On 10/30/2012 8:55 AM, Johan wrote:
> "J favreau" <jmfavrea@unity.ncsu.edu> wrote in message
> <eed426f.2@webx.raydaftYaTP>...
>> a. mosher wrote:
...
>> >> FileNames=dir('*.xls);
...
>> >> for i=1:length(FileNames)
>> >> FileToLoad=FileNames(i).name;
>> >> %this is a xls file, load it, run code and loop
>> >> end
...

> I tried something similar before, until I found this thread an hoped to
> get a solution for my problem. But it still doesn't work for me.
> When I tell matlab to search all files with endings .xlsx, I get a
> matrix with all information about my excel file, including their
> filenames.

More specifically, DIR returns a structure...

>... As you explained, I decalare a variable called FileToLoad,
> with the name of my excelfile.
> When I then try to use xlsread('FileToLoad') it doesnt work cause matlab
> can't find a file called FileToLoad.
> Thats right so far, but why doesnt matlab simply use the information
> from the variable 'FileToLoad' instead...

Well, that's because 'FileToLoad' isn't a variable but a character string...

If you really did write

FileToLoad='mydata.xlsx';

then use the variable name not a string equal to the variable name.

> Seemingly it worked for you, but I cant figure out why, so I would
> appreciate if you can help me with my problem.

Well, it worked for the poster because he used the .name field in the
structure returned by DIR() instead of a string of that name...

Which I'd recommend you do as well--much more flexible coding style than
hardcoding a filename into a script or function. Or, use uigetfile() to
select a file interactively or somesuch.

--


> Thanks
>
> Johan

"Johan " <etk50505@stud.uni-stuttgart.de> wrote in message
news:k6om7t$30b$1@newscl01ah.mathworks.com...

*snip*

> I tried something similar before, until I found this thread an hoped to
> get a solution for my problem. But it still doesn't work for me.
> When I tell matlab to search all files with endings .xlsx, I get a matrix
> with all information about my excel file, including their filenames. As
> you explained, I decalare a variable called FileToLoad, with the name of
> my excelfile.
> When I then try to use xlsread('FileToLoad') it doesnt work cause matlab
> can't find a file called FileToLoad.

That's correct. xlsread('FileToLoad') attempts to read in a file with the
name FileToLoad and a "supported Excel extension." xlsread(FileToLoad)
attempts to read in the file whose name is stored in the variable
FileToLoad.

> Thats right so far, but why doesnt matlab simply use the information from
> the variable 'FileToLoad' instead... for example (mydata.xlsx).

Because MATLAB does what you tell it to do. We try to limit the
circumstances under which MATLAB does what it _thinks you meant_ instead of
what you _told it to do_ because it's easy to get the "mind reading" part of
that wrong. [Maybe once the Mind Reading Toolbox is operational ...]

--
Steve Lord
slord@mathworks.com
To contact Technical Support use the Contact Us link on
http://www.mathworks.com

"Steven_Lord" <slord@mathworks.com> wrote in message <k6ooc8$c75$1@newscl01ah.mathworks.com>...
>
>
> "Johan " <etk50505@stud.uni-stuttgart.de> wrote in message
> news:k6om7t$30b$1@newscl01ah.mathworks.com...
>
> *snip*
>
> > I tried something similar before, until I found this thread an hoped to
> > get a solution for my problem. But it still doesn't work for me.
> > When I tell matlab to search all files with endings .xlsx, I get a matrix
> > with all information about my excel file, including their filenames. As
> > you explained, I decalare a variable called FileToLoad, with the name of
> > my excelfile.
> > When I then try to use xlsread('FileToLoad') it doesnt work cause matlab
> > can't find a file called FileToLoad.
>
> That's correct. xlsread('FileToLoad') attempts to read in a file with the
> name FileToLoad and a "supported Excel extension." xlsread(FileToLoad)
> attempts to read in the file whose name is stored in the variable
> FileToLoad.
>
> > Thats right so far, but why doesnt matlab simply use the information from
> > the variable 'FileToLoad' instead... for example (mydata.xlsx).
>
> Because MATLAB does what you tell it to do. We try to limit the
> circumstances under which MATLAB does what it _thinks you meant_ instead of
> what you _told it to do_ because it's easy to get the "mind reading" part of
> that wrong. [Maybe once the Mind Reading Toolbox is operational ...]
>
> --
> Steve Lord
> slord@mathworks.com
> To contact Technical Support use the Contact Us link on
> http://www.mathworks.com

Thank you guys!

I could swear, I tried this before...
Really stupid of me, when i really forget to try the simplest solution.

Read All Files in a Folder:

Data Import and Export:
http://www.mathworks.com/help/pdf_doc/matlab/import_export.pdf


What is MATLAB?:
http://matlab.wikia.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F



"Johan" wrote in message <k6op5k$fhj$1@newscl01ah.mathworks.com>...
> "Steven_Lord" <slord@mathworks.com> wrote in message <k6ooc8$c75$1@newscl01ah.mathworks.com>...
> >
> >
> > "Johan " <etk50505@stud.uni-stuttgart.de> wrote in message
> > news:k6om7t$30b$1@newscl01ah.mathworks.com...
> >
> > *snip*
> >
> > > I tried something similar before, until I found this thread an hoped to
> > > get a solution for my problem. But it still doesn't work for me.
> > > When I tell matlab to search all files with endings .xlsx, I get a matrix
> > > with all information about my excel file, including their filenames. As
> > > you explained, I decalare a variable called FileToLoad, with the name of
> > > my excelfile.
> > > When I then try to use xlsread('FileToLoad') it doesnt work cause matlab
> > > can't find a file called FileToLoad.
> >
> > That's correct. xlsread('FileToLoad') attempts to read in a file with the
> > name FileToLoad and a "supported Excel extension." xlsread(FileToLoad)
> > attempts to read in the file whose name is stored in the variable
> > FileToLoad.
> >
> > > Thats right so far, but why doesnt matlab simply use the information from
> > > the variable 'FileToLoad' instead... for example (mydata.xlsx).
> >
> > Because MATLAB does what you tell it to do. We try to limit the
> > circumstances under which MATLAB does what it _thinks you meant_ instead of
> > what you _told it to do_ because it's easy to get the "mind reading" part of
> > that wrong. [Maybe once the Mind Reading Toolbox is operational ...]
> >
> > --
> > Steve Lord
> > slord@mathworks.com
> > To contact Technical Support use the Contact Us link on
> > http://www.mathworks.com
>
> Thank you guys!
>
> I could swear, I tried this before...
> Really stupid of me, when i really forget to try the simplest solution.

I just tried this; it works:
folders = {'C:\Users\Excel\Desktop\Ryan_Folder\'};
DATA = cell(numel(folders),1);
 
for fo = 1:numel(folders)
    files = dir(folders{fo}); %# get array fo all files in a folder
    files([files(:).isdir]) = []; %# remove directories from the structure array
    DATA{fo} = cell(numel(files),1);
    for fi = 1:numel(files)
        filename = fullfile(folders{fo},files(fi).name);
        disp(filename)
        [status,sheets] = xlsfinfo(filename);
        if ~isempty(status)
            lastsheet = sheets{end};
            [num,txt,raw] = xlsread(filename,lastsheet);
            DATA{fo}{fi} = num; %# or txt, or raw
        end
    end
end


"Ryan" wrote in message <k71ft0$og2$1@newscl01ah.mathworks.com>...
> Read All Files in a Folder:
>
> Data Import and Export:
> http://www.mathworks.com/help/pdf_doc/matlab/import_export.pdf
>
>
> What is MATLAB?:
> http://matlab.wikia.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F
>
>
>
> "Johan" wrote in message <k6op5k$fhj$1@newscl01ah.mathworks.com>...
> > "Steven_Lord" <slord@mathworks.com> wrote in message <k6ooc8$c75$1@newscl01ah.mathworks.com>...
> > >
> > >
> > > "Johan " <etk50505@stud.uni-stuttgart.de> wrote in message
> > > news:k6om7t$30b$1@newscl01ah.mathworks.com...
> > >
> > > *snip*
> > >
> > > > I tried something similar before, until I found this thread an hoped to
> > > > get a solution for my problem. But it still doesn't work for me.
> > > > When I tell matlab to search all files with endings .xlsx, I get a matrix
> > > > with all information about my excel file, including their filenames. As
> > > > you explained, I decalare a variable called FileToLoad, with the name of
> > > > my excelfile.
> > > > When I then try to use xlsread('FileToLoad') it doesnt work cause matlab
> > > > can't find a file called FileToLoad.
> > >
> > > That's correct. xlsread('FileToLoad') attempts to read in a file with the
> > > name FileToLoad and a "supported Excel extension." xlsread(FileToLoad)
> > > attempts to read in the file whose name is stored in the variable
> > > FileToLoad.
> > >
> > > > Thats right so far, but why doesnt matlab simply use the information from
> > > > the variable 'FileToLoad' instead... for example (mydata.xlsx).
> > >
> > > Because MATLAB does what you tell it to do. We try to limit the
> > > circumstances under which MATLAB does what it _thinks you meant_ instead of
> > > what you _told it to do_ because it's easy to get the "mind reading" part of
> > > that wrong. [Maybe once the Mind Reading Toolbox is operational ...]
> > >
> > > --
> > > Steve Lord
> > > slord@mathworks.com
> > > To contact Technical Support use the Contact Us link on
> > > http://www.mathworks.com
> >
> > Thank you guys!
> >
> > I could swear, I tried this before...
> > Really stupid of me, when i really forget to try the simplest solution.

You can try this as well!!!

%# get input XLS files
dName = uigetdir('.', 'Select folder containing Excel XLS files');
if dName==0, error('No folder selected'); end
files = dir( fullfile(dName,'*.xls') );
files = strcat(dName, filesep, {files.name}'); %'
 
%# prepare output XLS file
[fName dName] = uiputfile({'*.xls' 'Excel (*.xls)'}, 'Output File', 'final.xls');
if dName==0, error('No file selected'); end
fOut = fullfile(dName,fName);
 
%# process
NUM_SHEETS = 5; %# number of sheets per file
for s=1:NUM_SHEETS
    %# extract contents of same sheet from all files
    numData = cell(numel(files),1);
    for f=1:numel(files)
        numData{f} = xlsread(files{f}, s);
    end
 
    %# rearrange data
    numData = cat(3,numData{:});
    numData = reshape(permute(numData,[3 1 2]), [], size(numData,2));
 
    %# write data to corresponding sheet of output XLS file
    xlswrite(fOut, numData, s);
end



"Ryan" wrote in message <k71ft0$og2$1@newscl01ah.mathworks.com>...
> Read All Files in a Folder:
>
> Data Import and Export:
> http://www.mathworks.com/help/pdf_doc/matlab/import_export.pdf
>
>
> What is MATLAB?:
> http://matlab.wikia.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F
>
>
>
> "Johan" wrote in message <k6op5k$fhj$1@newscl01ah.mathworks.com>...
> > "Steven_Lord" <slord@mathworks.com> wrote in message <k6ooc8$c75$1@newscl01ah.mathworks.com>...
> > >
> > >
> > > "Johan " <etk50505@stud.uni-stuttgart.de> wrote in message
> > > news:k6om7t$30b$1@newscl01ah.mathworks.com...
> > >
> > > *snip*
> > >
> > > > I tried something similar before, until I found this thread an hoped to
> > > > get a solution for my problem. But it still doesn't work for me.
> > > > When I tell matlab to search all files with endings .xlsx, I get a matrix
> > > > with all information about my excel file, including their filenames. As
> > > > you explained, I decalare a variable called FileToLoad, with the name of
> > > > my excelfile.
> > > > When I then try to use xlsread('FileToLoad') it doesnt work cause matlab
> > > > can't find a file called FileToLoad.
> > >
> > > That's correct. xlsread('FileToLoad') attempts to read in a file with the
> > > name FileToLoad and a "supported Excel extension." xlsread(FileToLoad)
> > > attempts to read in the file whose name is stored in the variable
> > > FileToLoad.
> > >
> > > > Thats right so far, but why doesnt matlab simply use the information from
> > > > the variable 'FileToLoad' instead... for example (mydata.xlsx).
> > >
> > > Because MATLAB does what you tell it to do. We try to limit the
> > > circumstances under which MATLAB does what it _thinks you meant_ instead of
> > > what you _told it to do_ because it's easy to get the "mind reading" part of
> > > that wrong. [Maybe once the Mind Reading Toolbox is operational ...]
> > >
> > > --
> > > Steve Lord
> > > slord@mathworks.com
> > > To contact Technical Support use the Contact Us link on
> > > http://www.mathworks.com
> >
> > Thank you guys!
> >
> > I could swear, I tried this before...
> > Really stupid of me, when i really forget to try the simplest solution.