Import mutilple excel files and save then individually as matlab tables, and finally synchronize the timetables

I am considering to import mutiple excel files using matlab, and these files will be saved individually as tables with the name corresponding to their excel files.

For example, excel file aberdeenshire_00150_aboyne-no-2.xlsx will be imported to matlab and save as aberdeenshire_00150_aboyne-no-2.mat; excel file aberdeenshire_00161_dyce.xlsx will be imported to matlab and save as aberdeenshire_00161_dyce.mat; excel file; antrim_01448_portglenone.xlsx will be imported to matlab and save as antrim_01448_portglenone.mat.

I have the following code but it save all the excel files in a single matlab table, could you teach me how to fix the code so that these files will be saved as table individually with the corresponding name?

Cheers!

source_dir = 'C:\Wind_Data_Rows_Deleted_2023\test';
dest_dir = 'C:\Wind_Forecast_Matlab';
source_files = dir(fullfile(source_dir, '*.xlsx'));
for i = 1:length(source_files)
  data {i} = readtable(fullfile(source_dir, source_files(i).name));
  
end

Answer 1

[Voss]

% specify source and destination directories
source_dir = 'C:\Wind_Data_Rows_Deleted_2023\test';
dest_dir = 'C:\Wind_Forecast_Matlab';

% get info about the relevant xlsx files,
% and construct xlsx full-path file names
F = dir(fullfile(source_dir, '*.xlsx'));
fn_xlsx = fullfile({F.folder},{F.name});

% construct the mat-file full-path file names
[~,fn] = fileparts(fn_xlsx);
fn_mat = fullfile(dest_dir,strcat(fn,'.mat'));

% if dest_dir doesn't already exist, create it
if ~isdir(dest_dir)
    mkdir(dest_dir);
end

% read each xlsx file, and save each mat file
for ii = 1:numel(fn_xlsx)
    T = readtable(fn_xlsx{ii});
    save(fn_mat{ii},'T');
end


% check the mat file results
S = load(fn_mat{1});
S.T

ans = 8759x24 table
        ob_end_time         id_type        id        ob_hour_count    met_domain_name    version_num    src_id    rec_st_ind    mean_wind_dir    mean_wind_speed    max_gust_dir    max_gust_speed    max_gust_ctime    mean_wind_dir_q    mean_wind_speed_q    max_gust_dir_q    max_gust_speed_q    max_gust_ctime_q    mean_wind_dir_j    mean_wind_speed_j    max_gust_dir_j    max_gust_speed_j       meto_stmp_time       midas_stmp_etime
    ____________________    ________    _________    _____________    _______________    ___________    ______    __________    _____________    _______________    ____________    ______________    ______________    _______________    _________________    ______________    ________________    ________________    _______________    _________________    ______________    ________________    ____________________    ________________

    01-Jan-2023 00:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            300                3               310               5                2305                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 00:06:00           0        
    01-Jan-2023 00:59:59    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            290                3               290               5                  30                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 01:03:59           0        
    01-Jan-2023 02:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            280                3               240               4                 145                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 02:04:00           0        
    01-Jan-2023 03:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            270                2               260               4                 150                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 03:03:00           0        
    01-Jan-2023 03:59:59    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            270                3               260               4                 312                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 04:03:00           0        
    01-Jan-2023 05:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            260                3               250               4                 442                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 05:03:00           0        
    01-Jan-2023 06:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            250                2               250               3                 518                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 06:05:00           0        
    01-Jan-2023 06:59:59    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            260                2               260               2                 628                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 07:03:59           0        
    01-Jan-2023 08:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            270                2               260               4                 712                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 08:04:00           0        
    01-Jan-2023 09:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            280                1               270               2                 757                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 09:06:00           0        
    01-Jan-2023 09:59:59    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            290                2               260               4                 947                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 10:03:00           0        
    01-Jan-2023 11:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            310                2               280               4                 951                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 11:03:00           0        
    01-Jan-2023 12:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            290                2               300               3                1144                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 12:03:00           0        
    01-Jan-2023 12:59:59    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            260                1               300               3                1155                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 13:03:59           0        
    01-Jan-2023 14:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            290                2               290               4                1324                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 14:04:00           0        
    01-Jan-2023 15:00:00    {'WIND'}    1.241e+05          1              {'HCM'}             1          150         1011            280                2               310               3                1427                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 15:03:00           0        

S = load(fn_mat{2});
S.T

ans = 8760x24 table
        ob_end_time         id_type        id        ob_hour_count    met_domain_name    version_num    src_id    rec_st_ind    mean_wind_dir    mean_wind_speed    max_gust_dir    max_gust_speed    max_gust_ctime    mean_wind_dir_q    mean_wind_speed_q    max_gust_dir_q    max_gust_speed_q    max_gust_ctime_q    mean_wind_dir_j    mean_wind_speed_j    max_gust_dir_j    max_gust_speed_j       meto_stmp_time       midas_stmp_etime
    ____________________    ________    _________    _____________    _______________    ___________    ______    __________    _____________    _______________    ____________    ______________    ______________    _______________    _________________    ______________    ________________    ________________    _______________    _________________    ______________    ________________    ____________________    ________________

    01-Jan-2023 00:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            340                5               180                9               2332                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 00:06:00           0        
    01-Jan-2023 00:59:59    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            340                6               340                9                  2                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 01:03:59           0        
    01-Jan-2023 02:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            330                3               320                6                115                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 02:04:00           0        
    01-Jan-2023 03:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            290                3                10                7                238                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 03:03:00           0        
    01-Jan-2023 03:59:59    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            350                5               330               10                317                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 04:03:59           0        
    01-Jan-2023 05:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011             10                3                70                6                445                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 05:03:00           0        
    01-Jan-2023 06:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            360                4                60                6                456                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 06:05:00           0        
    01-Jan-2023 06:59:59    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            340                3               320                6                556                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 07:03:59           0        
    01-Jan-2023 08:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            340                3               330                7                707                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 08:04:00           0        
    01-Jan-2023 09:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            270                2                50                4                844                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 09:06:00           0        
    01-Jan-2023 09:59:59    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            350                1               350                2                938                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 10:03:00           0        
    01-Jan-2023 11:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            220                2               180                5                956                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 11:03:00           0        
    01-Jan-2023 12:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            170                2               220                4               1052                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 12:03:00           0        
    01-Jan-2023 12:59:59    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            340                3               340                4               1242                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 13:03:59           0        
    01-Jan-2023 14:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            330                6               320                9               1326                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 14:04:00           0        
    01-Jan-2023 15:00:00    {'WIND'}    1.273e+05          1              {'HCM'}             1          161         1011            330                5               330                7               1355                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 15:03:00           0        

S = load(fn_mat{3});
S.T

ans = 8705x24 table
        ob_end_time         id_type        id       ob_hour_count    met_domain_name    version_num    src_id    rec_st_ind    mean_wind_dir    mean_wind_speed    max_gust_dir    max_gust_speed    max_gust_ctime    mean_wind_dir_q    mean_wind_speed_q    max_gust_dir_q    max_gust_speed_q    max_gust_ctime_q    mean_wind_dir_j    mean_wind_speed_j    max_gust_dir_j    max_gust_speed_j       meto_stmp_time       midas_stmp_etime
    ____________________    ________    ________    _____________    _______________    ___________    ______    __________    _____________    _______________    ____________    ______________    ______________    _______________    _________________    ______________    ________________    ________________    _______________    _________________    ______________    ________________    ____________________    ________________

    01-Jan-2023 00:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            330                3                70               9                2251                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 00:06:00           0        
    01-Jan-2023 00:59:59    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            320                3               340               5                2359                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 01:03:59           0        
    01-Jan-2023 02:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            330                3               330               5                 131                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 02:04:00           0        
    01-Jan-2023 03:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            340                2               340               4                 210                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 03:04:00           0        
    01-Jan-2023 03:59:59    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            330                3               340               4                 330                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 04:03:59           0        
    01-Jan-2023 05:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            310                2               330               4                 353                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 05:04:00           0        
    01-Jan-2023 06:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            270                2               270               4                 533                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 06:06:00           0        
    01-Jan-2023 06:59:59    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            250                2               300               4                 551                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 07:03:59           0        
    01-Jan-2023 08:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            220                2               230               3                 720                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 08:04:00           0        
    01-Jan-2023 09:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            180                3               190               5                 806                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 09:06:00           0        
    01-Jan-2023 09:59:59    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            170                1               130               3                 942                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 10:03:59           0        
    01-Jan-2023 11:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            150                1               120               2                 952                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 11:04:00           0        
    01-Jan-2023 12:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            280                1               270               2                1135                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 12:04:00           0        
    01-Jan-2023 12:59:59    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            270                1               240               2                1211                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 13:03:59           0        
    01-Jan-2023 14:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            290                2               310               6                1345                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 14:04:00           0        
    01-Jan-2023 15:00:00    {'WIND'}    9.13e+05          1              {'HCM'}             1          1448        1011            260                2               300               4                1350                6                   6                  6                  6                   6                {'NA'}              {'NA'}              {'NA'}             {'NA'}         01-Jan-2023 15:04:00           0        

[dpb]

@Kaiyuan, while @Voss has illustrated how to do what you asked for, I'd suggest unless the intent is that unless all of these files will be considered only individually for subsequent use/analysis, it might be a useful thing to add the meta data presently contained in the file names and add it as additional variable(s) and concatenate the files into one. Then one could easily do analyses by plant as well within the file without having to load multiple files.

[Kaiyuan]

Thanks for the contributions, Voss.

Because I just begin to synchronize the tables in the following stage, and I think it would be easier for me to understand if it is proceeded one by one without mixing up.

I am also going to learn how to handle file which is concatenated into one as well.

[dpb]

That's fine, I just wanted to plant the idea when/if you do get to that point that you think of doing that instead of trying to process the files individually and make some sort of intermediary way instead of having the grouping variables in hand.

I was going to illustrate with a short piece of code, but I discovered there's something funky in the first file name above; what looks to be an underscore "_" character separating the numeric section are not both char(95) in that file as they are in the other two, the first of the two in that file name is char(101) instead--where/how that came to be is curious, but while it appears the same visually, somehow the name got created differently and so parsing the pieces for it turned out to be different than for the others...

d=dir('*.xlsx');
[~,fn]=fileparts({d.name})

fn = 1x3 cell array
    {'aberdeenshire_...yne-no-2'}    {'aberdeenshire_00161_dyce'}    {'antrim_01448_portglenone'}

strfind(fn,'_')     % the location of the underscores

ans = 1x3 cell array
    {[14]}    {[14 20]}    {[7 13]}

illustrates the issue; despite appearnces to the contrary, there's only one underscore character in the first file name.

double(fn{1}(20))   % see what's matching the 2nd file name at character position 20

ans = 101

strfind({d.name},char(101))  % look for more of those 

ans = 1x3 cell array
    {[3 6 7 13 20]}    {[3 6 7 13 24]}    {[20 24]}

shows that somehow there are a bunch of 'em in the first two files, but none in the third. That seems most peculiar; you may want to investigate how those names are being created.

[Voss]

@dpb: I've noticed that when I attach a file to an answer or comment using the "Link from this thread" tab, sometimes (probably whenever the name is longer than some threshold) the file name is modified, e.g., "aberdeenshire_00150_aboyne-no-2.xlsx" becomes "aberdeenshire_...ne-no-2.xlsx". To get around that, I download the file, and then attach using the "From my computer" tab, in which case the attached file has the complete name.

[dpb]

Yeah, I've noticed it, too, but I thought when what was displayed at the top of the Answer didn't show the shortened version it should be all right. Clearly that must have been a false assumption.

Maybe I'll come back later and try to clean it up; don't really need the real files for what I wanted to illustrate for @Kaiyuan, so I can just create a dir() struct that contains the full strings. I gots other things have got to go do at the moment, though...

[Kaiyuan]

Hi Voss and dpb:

I can confirm the full name of the files can be displayed without problem.

I am currently modifying the above code, so that the files can be synchronized with the standard time table (Time_series.xlsx) attached with this message (the files should be synchronized are attached at the original questions such as aberdeenshire_00150_aboyne-no-2.xlsx ).

I only have problem to save the synchronized files as excel files individually. I used the writetimetable(Ts,'fn_xlsx1{ii}.xlsx') to try to save these synchronized files but it did not produce any excel file. Could you teach me how to fix it?

% specify source and destination directories

% Direction of files needed to be synchronized
source_dir1 = 'C:\Wind_Data_Rows_Deleted_2023\test;

% Direction of the standard time file
source_dir2 = 'C:\Wind_Data_Rows_Deleted_2023\Standard_Time';

% Destination directories
dest_dir = 'C:\Wind_Forecast_Matlab';

% get info about the relevant xlsx files,
% and construct xlsx full-path file names

% get into not synchronized files
F1 = dir(fullfile(source_dir1, '*.xlsx'));
fn_xlsx1 = fullfile({F1.folder},{F1.name});

% get into standard time files
F2 = dir(fullfile(source_dir2, '*.xlsx'));
fn_xlsx2 = fullfile({F2.folder},{F2.name});

% construct the mat-file full-path file names
[~,fn] = fileparts(fn_xlsx1);
fn_mat = fullfile(dest_dir,strcat(fn,'.mat'));

% if dest_dir doesn't already exist, create it
if ~isdir(dest_dir)
    mkdir(dest_dir);
end

Tt = readtimetable(fn_xlsx2{1});

% read each xlsx file as timetable, and save each mat file
% synchronize each files with standard time table, and save each mat file as excel file
for ii = 1:numel(fn_xlsx1)
    T = readtimetable(fn_xlsx1{ii});
    Ts = synchronize(Tt,T,'first')
    save(fn_mat{ii},'Ts');
    writetable(Ts,'fn_xlsx1{ii}')  
end

[Walter Roberson]

writetable(Ts, fn_xlsx1{ii});

However...

Ts will be a timetable object, and writetable() cannot be used for timetable objects. You need writetimetable() instead of writetable()

It is seldom a good idea to overwrite input files.

[Voss]

If you want the synchronized timetable files to go to the destination directory so as not to overwrite the original unsynchronized ones, then:

% ... code before here unchanged 

% construct the mat-file and output xlsx-file full-path file names
[~,fn,ext] = fileparts(fn_xlsx1);
fn_mat = fullfile(dest_dir,strcat(fn,'.mat'));
fn_xlsx_out = fullfile(dest_dir,strcat(fn,ext));

% if dest_dir doesn't already exist, create it
if ~isfolder(dest_dir)
    mkdir(dest_dir);
end

Tt = readtimetable(fn_xlsx2{1});

% read each xlsx file as timetable, and save each mat file
% synchronize each files with standard time table, and save each mat file as excel file
for ii = 1:numel(fn_xlsx1)
    T = readtimetable(fn_xlsx1{ii});
    Ts = synchronize(Tt,T,'first');
    save(fn_mat{ii},'Ts');
    writetimetable(Ts,fn_xlsx_out{ii})  
end

Also, make sure source_dir1 is defined properly; the code in your recent comment has a typo (missing quote on the end):

source_dir1 = 'C:\Wind_Data_Rows_Deleted_2023\test';

% ^ was missing

[Kaiyuan]

Thanks for the answer.

I have the last question. When I try to synchronize the table using synchronize(Tt,T,'first') or synchronize(Tt,T), peroidic blank rows were generated in the final table as shown on attached pics.

I suspect the hourly time series of the excel files are interpreted by the matlab until the minutes/seconds position. For example, the time 1/1/2023 2:00 am is interpreted as 1/1/2023 1:59:00 am or 1/1/2023 1:59:59 am by the matlab, so it create duplicate rows after I synchronize the data.

So how do I synchronize the files according to table Tt without creating these blank duplicate rows? I have tried variety of mean from https://uk.mathworks.com/help/matlab/ref/timetable.synchronize.html but the results are still unwanted.

[Voss]

One option is to round the datetimes in T.ob_end_time to the nearest hour using dateshift:

for ii = 1:numel(fn_xlsx1)
    T = readtimetable(fn_xlsx1{ii});
    T.ob_end_time = dateshift(T.ob_end_time,'start','hour','nearest');
    Ts = synchronize(Tt,T,'first');
    save(fn_mat{ii},'Ts');
    writetimetable(Ts,fn_xlsx_out{ii})  
end

[Kaiyuan]

Thanks you so much, Voss.

The code works as desired now!

[Voss]

You're welcome!