Read excel file with Matlab

Question

Sanley Guerrier 2023-8-18

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2010197-read-excel-file-with-matlab

评论： Star Strider 2023-8-18

采纳的回答： Star Strider

Shadedata.xlsx

在 MATLAB Online 中打开

Hello,

I have excel file with three columns "Time", "Altitude, And "Shadow length" that I want to read in Matlab. I tried this commands below, however, the column "Time" format change to decimal number. What can I do to keep the time format the same?

Thank you!

T = readtable('Shadedata.xlsx');
opts = detectImportOptions('Shadedata.xlsx');
preview('Shadedata.xlsx',opts)

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Star Strider 2023-8-18

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2010197-read-excel-file-with-matlab#answer_1289507

在 MATLAB Online 中打开

Shadedata.xlsx

One option is to use the datetime 'ConvertFrom' name-value pair. For the ‘value’ argument, both 'excel' and 'posixtime' work, with 'excel' appearing to be correct (in that it gives 15-minute intervals) —

% F = fileread('Shadedata.xlsx')
% C = readcell('Shadedata.xlsx')
T = readtable('Shadedata.xlsx', 'VAriableNamingRule','preserve')
T = 40×3 table
TimeAltitude [degree celcius]Shadow length [m]_________________________________________________

    0.32292               1.34                   2135.8      
    0.33333               2.34                   808.83      
    0.34375               3.34                   495.25      
    0.35417               4.34                   357.42      
    0.36458               5.34                   280.44      
      0.375               6.34                    231.5      
    0.38542               7.34                   197.77      
    0.39583               8.34                   173.22      
    0.40625              17.92                   154.65      
    0.41667              19.63                   140.22      
    0.42708              21.22                   128.79      
     0.4375              22.68                   119.62      
    0.44792              24.02                   112.22      
    0.45833               25.2                   106.24      
    0.46875              26.24                   101.45      
    0.47917              27.11                    97.68      
T1 = T;
T1.Time = datetime(T1.Time, 'ConvertFrom','excel')
T1 = 40×3 table
TimeAltitude [degree celcius]Shadow length [m]______________________________________________________________

    31-Dec-1899 07:45:00               1.34                   2135.8      
    31-Dec-1899 08:00:00               2.34                   808.83      
    31-Dec-1899 08:15:00               3.34                   495.25      
    31-Dec-1899 08:30:00               4.34                   357.42      
    31-Dec-1899 08:45:00               5.34                   280.44      
    31-Dec-1899 09:00:00               6.34                    231.5      
    31-Dec-1899 09:15:00               7.34                   197.77      
    31-Dec-1899 09:30:00               8.34                   173.22      
    31-Dec-1899 09:45:00              17.92                   154.65      
    31-Dec-1899 10:00:00              19.63                   140.22      
    31-Dec-1899 10:15:00              21.22                   128.79      
    31-Dec-1899 10:30:00              22.68                   119.62      
    31-Dec-1899 10:45:00              24.02                   112.22      
    31-Dec-1899 11:00:00               25.2                   106.24      
    31-Dec-1899 11:15:00              26.24                   101.45      
    31-Dec-1899 11:30:00              27.11                    97.68      
T2 = T;
T2.Time = datetime(T2.Time, 'ConvertFrom','posixtime')
T2 = 40×3 table
TimeAltitude [degree celcius]Shadow length [m]______________________________________________________________

    01-Jan-1970 00:00:00               1.34                   2135.8      
    01-Jan-1970 00:00:00               2.34                   808.83      
    01-Jan-1970 00:00:00               3.34                   495.25      
    01-Jan-1970 00:00:00               4.34                   357.42      
    01-Jan-1970 00:00:00               5.34                   280.44      
    01-Jan-1970 00:00:00               6.34                    231.5      
    01-Jan-1970 00:00:00               7.34                   197.77      
    01-Jan-1970 00:00:00               8.34                   173.22      
    01-Jan-1970 00:00:00              17.92                   154.65      
    01-Jan-1970 00:00:00              19.63                   140.22      
    01-Jan-1970 00:00:00              21.22                   128.79      
    01-Jan-1970 00:00:00              22.68                   119.62      
    01-Jan-1970 00:00:00              24.02                   112.22      
    01-Jan-1970 00:00:00               25.2                   106.24      
    01-Jan-1970 00:00:00              26.24                   101.45      
    01-Jan-1970 00:00:00              27.11                    97.68      

Choose the result that makes the best sense.

.

6 个评论
显示 4更早的评论隐藏 4更早的评论

Les Beckham 2023-8-18

在 MATLAB Online 中打开

Shadedata.xlsx

One possible way to offset the data to that desired date is this:

T = readtable('Shadedata.xlsx', 'VariableNamingRule', 'preserve');
T1 = T;
offset = exceltime(datetime(2023, 2, 10));
T1.Time = T1.Time + offset;
T1.Time = datetime(T1.Time, 'ConvertFrom', 'excel')
T1 = 40×3 table
TimeAltitude [degree celcius]Shadow length [m]______________________________________________________________

    10-Feb-2023 07:45:00               1.34                   2135.8      
    10-Feb-2023 08:00:00               2.34                   808.83      
    10-Feb-2023 08:15:00               3.34                   495.25      
    10-Feb-2023 08:30:00               4.34                   357.42      
    10-Feb-2023 08:45:00               5.34                   280.44      
    10-Feb-2023 09:00:00               6.34                    231.5      
    10-Feb-2023 09:15:00               7.34                   197.77      
    10-Feb-2023 09:30:00               8.34                   173.22      
    10-Feb-2023 09:45:00              17.92                   154.65      
    10-Feb-2023 10:00:00              19.63                   140.22      
    10-Feb-2023 10:15:00              21.22                   128.79      
    10-Feb-2023 10:30:00              22.68                   119.62      
    10-Feb-2023 10:45:00              24.02                   112.22      
    10-Feb-2023 11:00:00               25.2                   106.24      
    10-Feb-2023 11:15:00              26.24                   101.45      
    10-Feb-2023 11:30:00              27.11                    97.68      

Star Strider 2023-8-18

在 MATLAB Online 中打开

Shadedata.xlsx

As always, my pleasure!

I don’t see anything that directly addresses that problem ('PivotYear' would only change the year) and dateshift doesn’t appear to provide the necessary options.

So, I improvised —

T = readtable('Shadedata.xlsx', 'VAriableNamingRule','preserve')
T = 40×3 table
TimeAltitude [degree celcius]Shadow length [m]_________________________________________________

    0.32292               1.34                   2135.8      
    0.33333               2.34                   808.83      
    0.34375               3.34                   495.25      
    0.35417               4.34                   357.42      
    0.36458               5.34                   280.44      
      0.375               6.34                    231.5      
    0.38542               7.34                   197.77      
    0.39583               8.34                   173.22      
    0.40625              17.92                   154.65      
    0.41667              19.63                   140.22      
    0.42708              21.22                   128.79      
     0.4375              22.68                   119.62      
    0.44792              24.02                   112.22      
    0.45833               25.2                   106.24      
    0.46875              26.24                   101.45      
    0.47917              27.11                    97.68      
T.Time = datetime(T.Time, 'ConvertFrom','excel')
T = 40×3 table
TimeAltitude [degree celcius]Shadow length [m]______________________________________________________________

    31-Dec-1899 07:45:00               1.34                   2135.8      
    31-Dec-1899 08:00:00               2.34                   808.83      
    31-Dec-1899 08:15:00               3.34                   495.25      
    31-Dec-1899 08:30:00               4.34                   357.42      
    31-Dec-1899 08:45:00               5.34                   280.44      
    31-Dec-1899 09:00:00               6.34                    231.5      
    31-Dec-1899 09:15:00               7.34                   197.77      
    31-Dec-1899 09:30:00               8.34                   173.22      
    31-Dec-1899 09:45:00              17.92                   154.65      
    31-Dec-1899 10:00:00              19.63                   140.22      
    31-Dec-1899 10:15:00              21.22                   128.79      
    31-Dec-1899 10:30:00              22.68                   119.62      
    31-Dec-1899 10:45:00              24.02                   112.22      
    31-Dec-1899 11:00:00               25.2                   106.24      
    31-Dec-1899 11:15:00              26.24                   101.45      
    31-Dec-1899 11:30:00              27.11                    97.68      
[h,m,s] = hms(T.Time);                                          % Get Time Data
T.Time = datetime([repmat([2023 2 10],numel(h),1) h m s])       % Create New Dates & Times
T = 40×3 table
TimeAltitude [degree celcius]Shadow length [m]______________________________________________________________

    10-Feb-2023 07:45:00               1.34                   2135.8      
    10-Feb-2023 08:00:00               2.34                   808.83      
    10-Feb-2023 08:15:00               3.34                   495.25      
    10-Feb-2023 08:30:00               4.34                   357.42      
    10-Feb-2023 08:45:00               5.34                   280.44      
    10-Feb-2023 09:00:00               6.34                    231.5      
    10-Feb-2023 09:15:00               7.34                   197.77      
    10-Feb-2023 09:30:00               8.34                   173.22      
    10-Feb-2023 09:45:00              17.92                   154.65      
    10-Feb-2023 10:00:00              19.63                   140.22      
    10-Feb-2023 10:15:00              21.22                   128.79      
    10-Feb-2023 10:30:00              22.68                   119.62      
    10-Feb-2023 10:45:00              24.02                   112.22      
    10-Feb-2023 11:00:00               25.2                   106.24      
    10-Feb-2023 11:15:00              26.24                   101.45      
    10-Feb-2023 11:30:00              27.11                    97.68      
DI = cumsum([0; diff(hour(T.Time))<0]);                         % Check For Midnight Rollover
T.Time = T.Time + days(DI)                                      % Increment Days For Midnight Rollovers
T = 40×3 table
TimeAltitude [degree celcius]Shadow length [m]______________________________________________________________

    10-Feb-2023 07:45:00               1.34                   2135.8      
    10-Feb-2023 08:00:00               2.34                   808.83      
    10-Feb-2023 08:15:00               3.34                   495.25      
    10-Feb-2023 08:30:00               4.34                   357.42      
    10-Feb-2023 08:45:00               5.34                   280.44      
    10-Feb-2023 09:00:00               6.34                    231.5      
    10-Feb-2023 09:15:00               7.34                   197.77      
    10-Feb-2023 09:30:00               8.34                   173.22      
    10-Feb-2023 09:45:00              17.92                   154.65      
    10-Feb-2023 10:00:00              19.63                   140.22      
    10-Feb-2023 10:15:00              21.22                   128.79      
    10-Feb-2023 10:30:00              22.68                   119.62      
    10-Feb-2023 10:45:00              24.02                   112.22      
    10-Feb-2023 11:00:00               25.2                   106.24      
    10-Feb-2023 11:15:00              26.24                   101.45      
    10-Feb-2023 11:30:00              27.11                    97.68      

All the times are the same day here, however I included code that will increment the days in the event other data sets would have a midnight rollover. (I have used this approach with other problems.)

.

Sanley Guerrier 2023-8-18