Import multiple excel files into multiple variables (or matrices)

Hi,

I'm trying to import multiple excel files inside a folder into multiple variables which means that I'm creating my variables in a loop & grabbing my "filename" of my excel sheet & declare that name as my new variable name in Matlab.

Each variable is a matrix (all numeric).

I have attached 2 example excel sheets for you to see how I setup my excel sheets.

I have looked at several codes online & this is one of them which does not work for me (I'm guessing their Matlab version is different than mine & that could possibly be the reason why but, I can't tell). I also don't understand the ".name" part in here. Also, this code does not assign the excel file name as the variable name (but I wouldn't know even if it did since the code does not work for me) but, I'm interested in seeing one example to know how to do this in the future. Please don't forget the 2 excel sheets that I provided for the format of the excel sheet. Any help is appreciated.

    folder='C:\Users\JohnK\Desktop\Research\Simulation homeworks\Import_Multiple_xls'; 
    filetype='*.xls'; 
    f=fullfile(folder,filetype); 
    d=dir(f); 
    for k=1:length(d)
        filename = d(k).name ;
        [X,TXT,RAW] = xlsread(filename);
        % Apply the modifications
    end

[madhan ravi]

yildirim kocoglu's comment moved here

what I'm trying to do is very similar but, not exactly the same as your guide (although your guide will probably work as well but, ... read the below please).

The 2 files attached were only examples so, instead of name.xlsx & name2.xlsx assume I have permeability.xlsx & porosity.xlsx which are 2 totally different file names (also the data inside each one is different) & I will probably have more than 2 xlsx files in this folder with different names.

I want to read these xlsx files into matlab & grab the filenames (permeability & porosity) to declare as variables in matlab.

In my case, it is important that I do that to avoid confusion in the future.

Also, I want to specify the path to this folder which can be different than my current directory in Matlab which I thought the given code will do.

Any thoughts on how to achieve this?

[Stephen23]

"...& declare that name as my new variable name in Matlab."

"...& grab the filenames (permeability & porosity) to declare as variables in matlab. In my case, it is important that I do that to avoid confusion in the future."

Do NOT do this. Magically defining/accessing variable names is one way that beginners force themselves into writing slow, complex buggy code that is hard to debug. Writing complex code with magical variable names will increase the confusion in future, and should be avoided. Read this to know why:

https://www.mathworks.com/matlabcentral/answers/304528-tutorial-why-variables-should-not-be-named-dynamically-eval

In your case it is important to learn simpler, neater, more efficient ways to write code, like using indexing (as my answers shows you). Indexing is how experinced MATLAB users access their data, and is the correct way to "avoid confusion in the future."

Note that even if you do want to continue forcing yourself into writing pointlessly complex, slow, buggy code that magically uses filenames to define variable names, you will have to consider that this concept is very fragile: there are many filenames that are not valid variable names, for example 1.txt or a.2.txt or a-b.txt or a b.txt or millions of others. Your code would simply break with all of those. You would be much better off learning how to write code properly rather than wasting your time on this approach.

"I also don't understand the ".name" part in here."

The dir help explained that it returns a (possibly non-scalar) structure array, which has fields. One of the fields is .name. You can use it to access the names of the files/folders that dir found.

Answer 1

[Stephen23]

Your code has a bug in it: you use an absolute file path with dir but not with xlsread. But the biggest problem is with your concept: magically defining/acessing variable names is one way that beginners force themselves into writing slow, complex, buggy code that is hard to debug. Using filenames to define variable names is very fragile (your code will break unexpectedly).

It is much simpler and much more efficient to use indexing, like this:

D = 'C:\Users\JohnK\Desktop\Research\Simulation homeworks\Import_Multiple_xls';
S = dir(fullfile(D,'*.xls')); 
for k = 1:numel(S)
      F = fullfile(D,S(k).name);
      [S(k).num,S(k).txt,S(k).raw] = xlsread(F);
end

All of the imported data and the file information (filenames, etc), is in the structure S. You can trivially refer to them simply using indexing, e.g. the second file:

S(2).name
S(2).num
S(2).txt

and also using some of the convenient format for accessing fields of non-scalar structures:

C = {S.name} % all of the filenames in one cell array

This means you can easily sort the filenames later, of use indexing to select the elements of S that you want to work with:

X = strncmpi('poros',C,5)

Read more here:

https://www.mathworks.com/help/matlab/matlab_prog/comma-separated-lists.html

https://www.mathworks.com/matlabcentral/answers/320713-how-to-operate-on-comma-separated-lists

[Stephen23]

Yildirim Kocoglu's "Answer" moved here:

Thank you Stephen. I will definetely look into this and get back to you tomorrow. Thank you for the detailed explanation and your suggestions.

[Yildirim Kocoglu]

Thank you very much Stephen. I highly appreciate your help. I now understand that I can't create variables on the fly due to inefficiency of the method & I can navigate through the structure created & work with only the values if I desire so...

% To call the value/matrix from the structure simply:
% Transpose using " ' " --> just a test
E = {S.num}';
F = E{1};

There was 1 problem with the code I have provided & that is .xls is supposed to be .xlsx & I now understand why the code would not work in the first place.

I have also done something like below to perform an operation in a loop for all the files (matrices in this case) after importing all of the files (name1,...namen).

% Specific to my case
% To subtract (name2 - name1, name4 - name3, name6 - name5, etc.)
for i = 1 : 2 : size(C,1)
% Pull the matrix from the structure
    Diff = E{i+1} - E{i};
    fprintf('\n\n');
    disp(Diff);
end

[Yildirim Kocoglu]

Well, it's kind of annoying but, I have one more question. I don't want to create a new question just for this question since it's sort of related to the previous task.

So, after the issue of importing the excel files & operating on them has been solved. I have tried to export excel output files (multiple) & at first it was successful. I exported it to the same directory as where I read the files & I believe if I avoid doing this, my problem will be probably solved but, I'm still curious to find out if I can work with the same exact directory as where I read my files.

Example:

If I have name1.xlsx, name2.xlsx and so on, I create Diff1.xlsx (by name1.xlsx - name2.xlsx), etc. & save it in the same path (no problem until here) but, when I try to rerun the code I start seeing issues due to the fact that there are now 3 files (name1.xlsx, name2.xlsx, Diff1.xlsx) instead of 2 within the same path & it picks the new excel files too.

What I wish I could do is: Avoid picking this new Diff1.xlsx & update it later (replace with the new result if I do something different instead of subtracting). I may be missing something simple here. Any help is appreciated.

Here is the full code I have re-written:

D = 'C:\Users\JohnK\Desktop\Research\Simulation homeworks\Import_Multiple_xls';
S = dir(fullfile(D,'*.xlsx'));

% Using the logical array to recognize the name1.xlsx & name2.xlsx & to avoid picking Diff1.xlsx
  G = strncmpi('name',C,3);

for k = 1:numel(S)
    if G(k) == 1
      F = fullfile(D,S(k).name);
      [S(k).num,S(k).txt,S(k).raw] = xlsread(F);
    else
      fprintf('File names do not match');
      break;
    end
end

E = {S.num}';
Diff = zeros(size(E(1),1),size(E(1),2)); 

for i = 1 : 2 : size(C,1)
    Diff = E{i+1} - E{i};
    fprintf('\n\n');
    disp(Diff);
end

for j = 1:(numel(S)/2)
    name = sprintf('Diff%d.xlsx',j);
    fileName = fullfile(D,name);
    xlswrite(fileName, Diff);
end

[Stephen23]

Here are two solutions:

1) change the dir filename matching string:

S = dir(fullfile(D,'name*.xlsx'));

This will only match the files whose names start with 'name'.

2) Use strncmpi and indexing:

S = dir(fullfile(D,'*.xlsx'));
X = strncmpi('name',{S.name},4);
S = S(X);

And then simply continue, just like before:

for k = 1:numel(S)
    F = fullfile(D,S(k).name);
    [S(k).num,S(k).txt,S(k).raw] = xlsread(F);
end

[Yildirim Kocoglu]

Got it. Will give this a try tomorrow. Thanks again.

[Yildirim Kocoglu]

I did try what you have suggested & it worked perfectly. I greatly appreciate all the detailed help you have given us. I'm not sure if I should or should not post my code but, I hope it helps others too in their project (However, I do not guarantee it will help anyone in any way but, it can be a start rather than starting from scratch...)

Here is the final product below:

D = 'C:\Users\JohnK\Desktop\Research\Simulation homeworks\Import_Multiple_xls';
J = fullfile(D,'name*.xlsx');
S = dir(J);
FileName = {S.name}';

%Below does not do anything special in this code (it maybe useful later for
%modifications to the code)
NAMECOMP = strncmpi('name',FileName,4);

for k = 1:numel(S)
        F = fullfile(D,S(k).name);
        [S(k).num,S(k).txt,S(k).raw] = xlsread(F);
end

E = {S.num}';
G = E{1};
Diff = zeros(size(G,1),size(G,2));

% Perform an operation on the matrices (from .xlsx files)

for i = 1 : 2 : size(FileName,1)
    Diff = E{i+1} - E{i};
    fprintf('\n\n');
    disp(Diff);
end

% Export multiple files

for j = 1:(numel(S)/2)
    name = sprintf('Diff%d.xlsx',j);
    fileName = fullfile(D,name);
    xlswrite(fileName, Diff);
end