MATLAB: How to read excel file

date and timeexcelMATLABuigetfilexlsread

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

Best Answer

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.
Related Question