[~,~,raw] = xlsread('data.csv');
num_rows = size(raw,1);
time_idx = contains(raw(:,1),'TIM');
abc_idx = contains(raw(:,1),'ABC');
def_idx = contains(raw(:,1),'DEF');
ghi_idx = contains(raw(:,1),'GHI');
val_times = NaT(num_rows,1);
date = datetime([raw{time_idx,2}]','ConvertFrom','yyyymmdd');
time = datetime(cellstr(num2str([raw{time_idx,3}]','%06d')),'InputFormat','HHmmss');
val_times(time_idx) = date + timeofday(time);
val_times = fillmissing(val_times,'previous');
times = unique(val_times);
vals = raw(:,2);
output_data = cell(numel(times),4);
output_data(:) = {NaN};
output_data(:,1) = cellstr(datestr(times));
abc = vals(abc_idx);
abc_times = val_times(abc_idx);
[~,I] = intersect(times,abc_times,'stable');
output_data(I,2) = abc;
def = vals(def_idx);
def_times = val_times(def_idx);
[~,I]=intersect(times,def_times,'stable');
output_data(I,3) = def;
ghi = vals(ghi_idx);
ghi_times = val_times(ghi_idx);
[~,I]=intersect(times,ghi_times,'stable');
output_data(I,4) = ghi;
T = cell2table(output_data,'VariableNames',{'DATE','ABC','DEF','GHI'});
writetable(T,'data_reformat.csv');
Best Answer