How do I have matlab read through an excel data sheet?

Hello all,

I am fairly new to matlab and I am wondering, how do I have matlab run a loop through an excel data sheet?

Example, I have an excel data sheet and suppose the first row represents date and time starting at 00:00 to 23:59, incrementing by 00:01 minutes and it repeats. Suppose there was a skip in the time, and it went from 00:01, 00:02, 00:44. There is a 00:42 time difference. How do I fill in the row values of 00:03 to 00:43 with NaN values?

Regards, Chris

Answer 1

[Roy Veldhuizen]

I've found a method which works for the case you're describing, don't know if this is the actual situation or a simplification. Either way:

%load data from xls called Book1.xlsx, which contains: [1 2 44]
data=xlsread('Book1.xlsx')
%determine datastep
datastep=data(2)-data(1);
%Find size of emtpy matrix
EmptyLength=((max(data)-min(data))/datastep)+1
%Generate emtpy matrix
EmptyData=ones(EmptyLength,1).*NaN
%Fill matrix
EmptyData(data)=data

Possibly it can be done faster although this one is hopefully easy to understand for the beginner. Hope it helps, and if you have any problems or questions, let me know.

Roy

Answer 2

[Chris]

Thank You,

however that didn't help my case much :/

Perhaps I should reword it.. Basically, I imported a time stamp into matlab from excel giving me a (n x m) matrix.

Problem 1: the time that is read on excel such as 10:09AM reads 0.4229 and it just doesn't make sense to me.

Question: How do I make matlab read it as either military time or AM/PM time?

Problem 2: suppose row 1,2,3,4,5,6 reads the time 00:00, 00:01, 00:02, 00:03, 02:53, 02:54 respectively. notice row(5) had a time gap of 2hrs and 50 mins from row(4).

Question: how would I make row(5) take the value of 00:04? and row(6) the value of 00:05 and so on until 02:53 is in the proper row, and constantly checks for time gaps.

I started my m-file with:

data=xlsread('data.xlsx') %reads the excel data and creates (104737x26) matrix for i=1:length(data) if data(i)-data(i-1)>@ %I'm not sure what to put in the place of the @ since the time read on matlab is in decimals (refer to problem 1)

now... after the "if" statement, i have absolutely no idea how to create a new empty row matrix to take the place of the row that is in the wrong place, and pushes it down the list until it is in the correct place.

I do apologize if this is a long read... I am a beginner in matlab :/

Regards, Chris

Answer 3

[Roy Veldhuizen]

The number you get is the time expressed as fraction of the day, thus beginning of the day = 0:00 =0, middle of the day = 12:00, means 0.5 end of the day is 24:00 means 1. I'm not sure if you want to use the timestamps as labels only, or if you want to use them for referencing as well. I think the best thing to do is, to perform all your calculations using the numeric values of time as labels, and then transform them to string timestamps for display at the end. If you want to do so, this might be a way to do it.

clear all
close all
clc
%load data from xls [1 2 44]
data=xlsread('Book1.xlsx')
%convert to number of seconds
data=round(data*(3600*24))
datasec=data
%convert to hours (divide by 3600 and round to lower)
hours=(floor(data/3600))
%Subtract from total
data=data-hours*3600
%convert to minutes (divide by 60 and round to lower)
minutes=(floor(data/60))
%subtract from total
seconds=(data-minutes*60)
%determine datastep
datastep=seconds(2)-seconds(1);
%Find size of emtpy matrix
EmptyLength=round(((max(datasec)-min(datasec))/datastep)+1)
%Generate emtpy matrix
EmptyData=cell(EmptyLength,1)
%Fill matrix with values
EmptyData(data-(min(data))+1)=cellstr(strcat(num2str(hours),':',num2str(minutes),':',num2str(seconds)) )
%Fill empty cells with NaN
EmptyData(cellfun('isempty',EmptyData))=cellstr('NaN')

By the way, a general remakt concerning your piece: started my m-file with:

_"data=xlsread('data.xlsx') %reads the excel data and creates (104737x26) matrix for i=1:length(data) if data(i)-data(i-1)>@ %I'm not sure what to put in the place of the @ since the time read on matlab is in decimals (refer to problem 1)"___

It's best to avoid loops if possible, as Matlab has to go through every cell of matrix individually, which takes a lot of time. As your matrix is quite big, this might give rise to long running times. Matlab is much quicker on matrix operations. For example, compare the following two operations:

clear all
close all
clc
A=rand(1000000,1)*100;
B=rand(1000000,1)*100;
Clong=zeros(length(A),1);
Cshort=zeros(length(A),1);
%loop
tic;
for i=1:length(A)
    Clong(i,1)=A(i)-B(i);
end
%log running time 1   
t1=toc;
%matrix
tic;
    Cshort=A-B;
%log running time 2   
t2=toc;
%Comparison of answers
Comparison=Clong==Cshort;
%Comparison of running times
tcompare=t1/t2
_

Even for this simple operation, the running time of the loop is 4 times larger than that of the matrix operations. Think about the difference for more complex operations..

Hope this helps.

Roy

Answer 4

[Leonardo]

Hi Chris,

I am not sure if you are aware but matlab and excel use different time formats. This brings lots of headaches for us multi-platform users but there are ways around. You can either make the time-stamp a string in Excel import it into matlab and covert the string into matlab format, or recalculate the excel values once in matlab using one of the many techniques described.

I recommed: http://www.mathworks.com/matlabcentral/answers/21830-excel-dates

Regarding your data-gap problem the best is to make a time vector in matlab containing the whole time range in the time step you need. Then you can use the "find" command to compare it with your original time vector and obtained the "locations" where the gaps are. One you have the "Locations" you can easily re-organize your data matrix to include the NaNs.

check

http://www.mathworks.nl/help/techdoc/ref/find.html

to learn how to use the find command.

There are more sophisticated ways to do this but this one is more comprehensive for new users.

Give it a try and let us know how it went.

Leonardo