How to read excel file?

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)));

Answer 1

[dpb]

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.

[Sarlota Duskova]

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]

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]

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.