Info

This question is closed. Reopen it to edit or answer.

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

1 view (last 30 days)
Dustin Watson
Dustin Watson on 14 Sep 2016
Closed: MATLAB Answer Bot on 20 Aug 2021
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.

Answers (2)

Julian Hapke
Julian Hapke on 14 Sep 2016
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
  1 Comment
Stephen
Stephen on 14 Sep 2016
A quote from Steve Lord (MATLAB guru and worker at TMW, the maker of MATLAB):
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:
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:

Stephen
Stephen on 14 Sep 2016
Edited: Stephen on 14 Sep 2016
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:
The test file is attached below:

Community Treasure Hunt

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

Start Hunting!