how to import excel data as separate column vectors?

Hey guys,

can someone please tell me how to import excel data as separate column vectors? I know how to do it trough the interface but i need to use it in a script :/

Thank you for your help

Answer 1

[Star Strider]

The available functions inport Excel files as matrices (that are of course collections of column vectors) are readmatrix and xlsread. There are others such as readtable, however that imports them as table objects.

[Viktor G.]

But there is an option to import the table as column vectors where each vector has the name of the variable from the table. I wouldn't be able to do that if i imported the whole table as just one matrix. I need to be able to pick any variable(column vector) by its name and not by its number of column.

[Star Strider]

I have not found that to be an option with either function. They import matrices. You can then use ordinary array indexing to specify, and if necessary assign to separate variables, the columns you want.

EDIT —

I note than in a Comment to a different Answer, you wrote:

‘I have a table with a lot of variables as different columns like time, temperature etc. so what i am trying to do is for the be able to choose which variable they would like to see (which would be as a column vector) without me manualy creating each column as a vector. Is there any way to do this?’

If you actually have a table, tthat is straightforward.

Example —

T = array2table(randi(99,10,5), 'VariableNames',{'Time','Temperature','Humidity','BaroPres','DewPt'});
Choose = @(col) T(:,strcmp(T.Properties.VariableNames,col));

RH = Choose('Humidity')

[Viktor G.]

Ok i get how this works but the problem I have with this way is that I can't use the plot option as i would like. I am trying to plot multiple variables from an excel file and I can't get around with it the legend always stays the same. My idea was that if they were all seprate vectors then i could solve the legend easier. This is the code i have.

clc
clear all
T = readtable('Book1.xlsx');
Time=T.DateTime;
ColumnYouNeed = input('Which variable would you like to plot: ','s');
mask = ismember(T.Properties.VariableNames,ColumnYouNeed);
NewTable = table2array(T(:,mask));
plot(Time,NewTable)
hold on
legend(ColumnYouNeed)
a=input('Would you like to plot another variable? ','s');
while a=='yes'
    ColumnYouNeed = input('Which variable would you like to plot: ','s');
    mask = ismember(T.Properties.VariableNames,ColumnYouNeed);
    NewTable = table2array(T(:,mask));
    plot(Time,NewTable)
    legend(ColumnYouNeed)
    a=input('Would you like to plot another variable? ','s'); 
end

[Star Strider]

Using my code (since I do not have your Excel file):

T = array2table(sortrows(randi(99,10,5),1), 'VariableNames',{'Time','Temperature','Humidity','BaroPres','DewPt'});
Choose = @(col) T(:,strcmp(T.Properties.VariableNames,col));

ColumnYouNeed = 'Humidity';
C = Choose(ColumnYouNeed)

figure
plot(T.Time, table2array(C))
grid
legend(ColumnYouNeed)

That worked when I tried it, including the legend.

[Viktor G.]

But this is the easy way, as i said you wouldn't know how many of variables or which ones would the user want to see on the plot. The X axsis would always be time, but he may want to see the temperature druing the time, but may want to see the temperature, humidity and BaroPres during the time. This is where the problem comes from with the legend. I am really thankfull for all the help :)

[Viktor G.]

check the problem with legend here please, i did this with the table you used

T = array2table(sortrows(randi(99,10,5),1), 'VariableNames',{'Time','Temperature','Humidity','BaroPres','DewPt'});
Choose = @(col) T(:,strcmp(T.Properties.VariableNames,col));
ColumnYouNeed = input('which column do you need? ' ,'s');
C = Choose(ColumnYouNeed);
plot(T.Time, table2array(C))
grid
legend(ColumnYouNeed)
hold on

answer=input('Would you like to also plot another column? ','s');
if answer=='yes'
    ColumnYouNeed = input('which column do you need? ' ,'s');
    C = Choose(ColumnYouNeed);
    plot(T.Time, table2array(C))
    grid
    legend(ColumnYouNeed)
end

[Star Strider]

Create a cell array of ‘ColumnYouNeed’ and the legend entries work correctly:

T = array2table(sortrows(randi(99,10,5),1), 'VariableNames',{'Time','Temperature','Humidity','BaroPres','DewPt'});
Choose = @(col) T(:,strcmp(T.Properties.VariableNames,col));
ColumnYouNeed{1} = input('which column do you need? ' ,'s');            % Create AS Cell Array Elements
C = Choose(ColumnYouNeed{1});
plot(T.Time, table2array(C))
grid
legend(ColumnYouNeed{1})                                      
hold on
answer=input('Would you like to also plot another column? ','s');
if strcmp(answer, 'yes')
    ColumnYouNeed{2} = input('which column do you need? ' ,'s');
    C = Choose(ColumnYouNeed{2});
    plot(T.Time, table2array(C))
    grid
    legend(ColumnYouNeed)
end

It might be best to just put all that in a loop, incrementing ‘ColumnYouNeed’, rather than having the first part outside the loop and the rest inside.

You could then exit the loop with:

if strcmp(answer, 'yes')
    ColumnYouNeed{2} = input('which column do you need? ' ,'s');
    C = Choose(ColumnYouNeed{2});
    plot(T.Time, table2array(C))
    grid
    legend(ColumnYouNeed)
    else
        return
end

I defer to you for that decision.

[Viktor G.]

Sorry for totally spamming you, but this would only work if you know that he would plot at most two options. I made an error with putting if instead of while

T = array2table(sortrows(randi(99,10,5),1), 'VariableNames',{'Time','Temperature','Humidity','BaroPres','DewPt'});
Choose = @(col) T(:,strcmp(T.Properties.VariableNames,col));
ColumnYouNeed = input('which column do you need? ' ,'s');
C = Choose(ColumnYouNeed);
plot(T.Time, table2array(C))
grid
legend(ColumnYouNeed)
hold on
answer=input('Would you like to also plot another column? ','s');
while answer=='yes'
    ColumnYouNeed = input('which column do you need? ' ,'s');
    C = Choose(ColumnYouNeed);
    plot(T.Time, table2array(C))
    grid
    legend(ColumnYouNeed)
end

[Star Strider]

Try this:

T = array2table(sortrows(randi(99,10,5),1), 'VariableNames',{'Time','Temperature','Humidity','BaroPres','DewPt'});
Choose = @(col) T(:,strcmp(T.Properties.VariableNames,col));
k = 1;
answer = 'yes';
while strcmpi(answer,'yes')
    answer=input('Would you like to plot a column? ','s');
    if strcmpi(answer,'no')
        break
    end
    ColumnYouNeed{k} = input('which column do you need? ' ,'s');
    C = Choose(ColumnYouNeed{k});
    Col(:,k) = table2array(C);
    k = k + 1;
end
figure
plot(T.Time, Col)
grid
legend(ColumnYouNeed)

The logic worked when I ran it. Experiment to get different results.

EDIT — (29 Feb 2020 at 23:52)

This is much better and more efficient:

T = array2table(sortrows(randi(99,10,5),1), 'VariableNames',{'Time','Temperature','Humidity','BaroPres','DewPt'});
Prompt = {'Choose columns to plot: ','(Hold down CTRL to choose more than one)'};
List = T.Properties.VariableNames;
Colsc = listdlg('PromptString',Prompt, 'ListString',List(2:end), 'ListSize',[220 300])
Col = table2array(T(:,Colsc+1));

figure
plot(T.Time, Col)
grid
legend(List(Colsc+1))

[Viktor G.]

Thank you so much :)

[Star Strider]

As always, my pleasure!

Answer 2

[Image Analyst]

Viktor, don't forget to look at my reply to you in this question of yours, which is pretty much the same.

[Viktor G.]

Thank you so much, I was just trying to get an answer :)