Read Excel file with multiple sheets

6 次查看(过去 30 天)
i have a excel file with multiple sheets, each representing a load (kW) output for a location. i want to read the date, time and load, so that i am able to create things like a 24hr average load profile or season profile. 'csvread' worked fine but didn't allow me to choose a specific sheet and 'xlsread' didn't format the data right.

回答(1 个)

Walter Roberson
Walter Roberson 2019-7-28
filename = 'Loads .xlsx';
opts = detectImportOptions(filename,'range','A:D');
opts = setvartype(opts, 'Date', 'datetime' );
opts = setvartype(opts, 'Time', 'duration');
opts = setvaropts(opts, 'Time', 'InputFormat', 'hh:mm');
opts.SelectedVariableNames = {'Date', 'Time', 'Load_kW_'};
for S = 1:3
opts.Sheet = S;
T{S} = readtable(filename, opts);
end
T will then be a cell array of three table() objects. Each table will have fields Date (datetime), Time (duration), Load_kW_ (floating point)
  2 个评论
Daniel Charlton
Daniel Charlton 2019-7-28
Thanks Walter this helps, but i am getting an error for the time
"Error using matlab.io.ImportOptions/setvartype (line 279)
Unsupported type 'duration'.
Error in Untitled2 (line 7)
opts = setvartype(opts, 'Time', 'duration');"
Walter Roberson
Walter Roberson 2019-7-28
You will probably need to change that to 'datetime' . The 'InputFormat' may have to change to 'HH:mm'
To reconstruct the entire date you would then have to use
T{S}.Time - dateshift(T{S}.Time, 'start', 'day') + T{S}.Date

请先登录,再进行评论。

产品


版本

R2017a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by