How can I assign data from multiple spreadsheets to the same vector using one of the columns as the variable name?

Hi everyone,

I'm currently having issues pulling data into MatLab in a way that allows me to use it. I'll just give you an example of my data so you get the idea.

What I want to do is pull this data into matlab, but all I care about is the data from the reference list. I also want to put the data in a vector named for each item (A1, A8, A12 in this case). So after its done I will have a vector A8 = [1, 6] etc. There is also the case in which the same item may appear in the list twice on a given day. At that point I would want to sum all of the amounts for said item, so in this case my A1 vector would look like A1 =[12, 3]

I've been looking through matlab answers for he last 2 days and have been unable to tailor anything I see to my specific problem, so thank you so much in advance for any help.

Answer 1

[Julian Hapke]

I copied your example sheet to example.xlsx and tested the code below. Please Note the remarks, I would not recommend the use of eval.

[~,b,c] = xlsread('example.xlsx');

% extract refs
refs = b(2:end,1);

% remove empty cells
refs(cellfun(@isempty,refs)) = [];

% get hightest ref number to preallocate cell array
refnums = str2double(cellfun(@(x) x(2:end),refs,'Uni',false));
A = cell(max(refnums),1);

% or just use a regular array
B = nan(numel(A),2);

% loop over refs
for ii = 1:numel(refs)
    % look for the ref in the other columns
    inc1 = strcmp(refs{ii},b(:,3));
    inc2 = strcmp(refs{ii},b(:,6));
    tmp = [sum([c{inc1,4}]) sum([c{inc2,7}])];
    % now the dirty eval part, if there are bad strings in your cell, you might destroy something
    eval([refs{ii} '=[' num2str(tmp) ']'])
    % much better would be, so the ref number in the index of the cell array
    A{refnums(ii)} = tmp;
    % or in a regular array
    B(refnums(ii),:) = tmp;
end

[Stephen23]

A quote from Steve Lord (MATLAB guru and worker at TMW, the maker of MATLAB):

"DO NOT USE EVAL"

eval is never the solution that beginners think it is. It is buggy and slow, and is not recommended by MATLAB makers themselves, they even have a whole page dedicated to telling everyone to avoid using this tool for and to use better programming methods:

http://www.mathworks.com/help/matlab/matlab_prog/string-evaluation.html

When you create lots of these variables it will just mean more bad, buggy, slow code to try and access them all. Instead of doing this, learn how to program using more reliable (and much faster) methods. See my answer, for an example of this.

And read this discussion of why magically creating variable names is a bad idea, no matter how much beginners love to dream it up:

https://www.mathworks.com/matlabcentral/answers/196952-how-to-create-new-variables-in-batches-with-strcat

Answer 2

[Stephen23]

Rather than using buggy and slow eval, simply place the data into a structure:

[~,txt,raw] = xlsread('temp0.xlsx');
ide = ~cellfun('isempty',txt);
idc = any(ide,1);
ide = ide(2:end,idc);
raw = raw(2:end,idc);
idm = 2:2:size(ide,2);
ref = raw(ide(:,1),1).';
ref(2,:) = {zeros(1,numel(idm))};
S = struct(ref{:});
for m = idm
    for n = 1:size(ref,2)
        fld = ref{1,n};
        idx = strcmp(fld,raw(ide(:,m),m));
        val = sum([raw{idx,m+1}]);
        S.(fld)(m/2) = val;
    end
end

And accessing the data in the structure is simple:

>> S.A1
ans =
    12     3
>> S.A8
ans =
     1     6
>> S.A12
ans =
    22     0
>>

More info on structure here:

http://www.mathworks.com/help/matlab/matlab_prog/access-data-in-a-structure-array.html

http://www.mathworks.com/help/matlab/matlab_prog/generate-field-names-from-variables.html

The test file is attached below: