MATLAB Answers

How to read excel file?

9 views (last 30 days)
Sarlota Duskova
Sarlota Duskova on 10 May 2020
Edited: dpb on 14 May 2020
Hello,
I am using Matlab R2019a and I have this weird excel file. I want to skip first six header lines, then in first column I have date but is only in first row, then starts second day and again the date is only in one row and I want to have all rows contains that date till it starts second day and all rows will contains that day, second column is time but again first row contains date and time but others rows have only time. Then I have few columns with data but some columns are empty. How to create table with date and time and columns with data without empty columns and without the first six header lines? Is it possible? My code for example what I have, but it changed data. I want to compare this Excel with another data which I already processed and then I want to plot date and time and one column with data from this Excel file, so that is the reason why I need create new table with usable data. Thank you for your reply.
[filename,path] = uigetfile(...
{'*.xls; *.xlsx', 'Microsoft Excel file (*.xls, *.xlsx)'}, 'Load File', 'MultiSelect', 'on');
[num,txt,raw] = xlsread(fullfile(path,filename));
data = cellfun(@mean,raw);
A = data(:,~all(isnan(data)));

  0 Comments

Sign in to comment.

Accepted Answer

dpb
dpb on 10 May 2020
Edited: dpb on 14 May 2020
Reading isn't too hard; interpreting what what you have is lacking some information...
First, use the detectImportOptions function to set up a base import object and then pick only the columns with variable names to actually read in --
opt=detectImportOptions('test_file.xls','NumHeaderLines',4); % actually only 5, not six header lines
isGoodCol=find(cellfun(@isempty,regexp(opt.SelectedVariableNames,'Var*'))); % find defined variable names
opt.SelectedVariableNames=opt.SelectedVariableNames(isGoodCol); % import only those columns
T=readtable('test_file.xls',opt); % and read the table
[h,m,s]=hms(datetime(T.TIME_,"ConvertFrom",'excel')); % convert time from Excel
T.DATE_=dateshift(fillmissing(T.DATE_,'previous'),'start','day'); % get beginning of day
T.DATE_=T.DATE_+duration(h,m,s); % and add time
T.TIME_=[]; % now superfluous
Returns
T =
43×9 table
DATE_ M00__C M01__C M02__C M03__C M04__C M10__H M20__C M30_Gk
____________________ ______ ______ ______ ______ ______ ______ ______ ______
10-Mar-2020 14:05:36 24.59 23.50 23.43 23.58 -1.09 28.10 4.90 5.40
10-Mar-2020 14:10:36 24.51 23.48 23.44 23.61 -1.03 28.00 4.80 5.30
10-Mar-2020 14:15:36 24.43 23.42 23.43 23.60 -1.01 28.50 5.00 5.40
...
10-Mar-2020 16:10:36 24.29 23.27 23.38 23.57 -1.02 28.20 4.70 5.30
10-Mar-2020 16:15:36 24.29 23.27 23.38 23.56 -1.02 28.20 4.70 5.30
10-Mar-2020 16:20:36 24.29 23.27 23.38 23.56 -1.02 28.30 4.80 5.30
10-Mar-2020 16:25:36 24.29 23.27 23.38 23.56 -1.02 28.30 4.80 5.30
11-Mar-2020 00:00:36 24.28 23.24 23.34 23.53 -1.04 29.00 5.10 5.40
11-Mar-2020 00:05:36 24.29 23.24 23.34 23.53 -1.05 29.00 5.10 5.40
11-Mar-2020 00:10:36 24.28 23.24 23.33 23.53 -1.04 29.10 5.20 5.40
11-Mar-2020 00:15:36 24.26 23.24 23.34 23.53 -1.02 29.10 5.10 5.40
11-Mar-2020 00:50:36 24.26 23.24 23.34 23.53 -1.02 29.10 5.10 5.40
11-Mar-2020 00:55:36 24.28 23.24 23.34 23.53 -1.04 29.10 5.20 5.40
11-Mar-2020 01:00:36 24.26 23.24 23.34 23.53 -1.02 29.10 5.20 5.40
11-Mar-2020 01:05:36 24.28 23.24 23.33 23.53 -1.04 29.20 5.20 5.40
....
>>
where I elided data rows for brevity.

  3 Comments

Sarlota Duskova
Sarlota Duskova on 13 May 2020
Hello there, thank you for your reply, I used your code. Definition of the time in TIME_ column you can see in xls it has format hh:mm:ss.SS. Because the first row when it date change in TIME_ column has datetime and another row in TIME_colums does not I did this:
Time = days(T.TIME_);
findMeshTime = find(Time>1);
Time(findMeshTime,:) = [];
Time.Format = 'hh:mm:ss.SS'
meshTime = timeofday(T.DATE_);
NaN = isnan(meshTime);
meshTime(NaN) = Time(1:nnz(NaN));
Then because only first row in DATE_ column has date and time I want fill the empty rows with the previous date so I did this:
fillDate = fillmissing(T.DATE_,'previous');
fillDate.Format = 'dd-MMM-yyyy 00:00:00';
now I want to put together date and time and put it into one column so the second column with TIME_ is does not necessarily. So for I did this:
DateTime = fillDate + meshTime;
T{:,1} = DateTime(:);
T(:,2) = [];
But it is adds meshTime to exist time in first column DATE_ and create new date and time and that I dont want.
Sarlota Duskova
Sarlota Duskova on 14 May 2020
I would like to thank you. That's what I need. I don't know why I am making my life too difficult. If you can do it so easily. I was trying [y,m,d] with date in first column but it didn't occur to me to use it for the time like you. Thank you again for your help. I marked the question as answered.
dpb
dpb on 14 May 2020
No problem...easy to get lost in the weeds w/ newer features -- there are so many functions that until have seen one or more it's hard to know what tools are around...the dateshift is the key here; your picking up and using ismissing is/was very good start.
I hadn't gone back and looked at the actual file in Excel so whiffed on the form being Excel time the first go-round...didn't even think of it with there being the actual calendar date/time in first; figured it was some other relative counter...
The key thing to remember w/ datetime vis a vis venerable datenum is that a datetime cannot exist w/o a date component unlike a datenum in which the day is the integer number part and the time fractions of a day. Hence, you have to use a duration for times of day regardless of whether have a day reference or not -- sometimes you can mask visually by the output format but internally it will always have that reference day of either a real date or the current date if was omitted.
Hence the shift back to midnight/beginning of the day for the dates and then throwing away the date part of the time to be able to combine the pieces into a real date.

Sign in to comment.

More Answers (0)

Products


Release

R2019a

Translated by