Combine excel files into one

I want to run a code inside every folder i have an excel file and i want to compare 10 excel files into 1. Can anyone help me?

[Mathieu NOE]

hello

you have to explain what data you want to extract , combine and save.

for the time being your doing things in the for loop that are not even in the saved output excel file (you r'e saving what you simply loaded from the input file)

so you have to explain your logic and probably share some input files as well if we want to test the code

all the best

Answer 1

[Mathieu NOE]

so I relicated the same folder tree and file name (same in every folder)

this is a slightly modified code , try it :

path = 'D:\2019';  
S = dir(fullfile(path, '*', 'data.xls'))

for k = 1:numel(S)
    F = fullfile(S(k).folder,S(k).name);
    data = readtable(F);
    d = strcat(table2array((data(:,1))), {' '}, table2array((data(:,2 ))));
    out = [d data(:,3)]; % date / time / acceleration
    out = renamevars(out,'Var1','Date & Time');
    writetable(out,'file.xls',"Sheet",k)
end

[Mathieu NOE]

I have R2020b

maybe your matlab version does not yet contain that function

if you comment / remove the line

out = renamevars(out,'Var1','Date & Time');

you simply end up with a excel file which first column name is by default "Var1", instead of "Date & Time"

does it matters ?

[Mathieu NOE]

hello again

the excel file has as many sheets as files processed (here 3)

01-01-2019 data (1438 collumns) is on sheet 1

02-01-2019 data (1438 collumns) is on sheet 2

03-01-2019 data (1438 collumns) is on sheet 3

but maybe you wanted to merge all data vertical in one sheet ? we can change to that if you want

[Mathieu NOE]

no problemo

here you are :

path = 'D:\2019';  
S = dir(fullfile(path, '*', 'data.xls'))
out = [];

for k = 1:numel(S)
    F = fullfile(S(k).folder,S(k).name);
    data = readtable(F);
    d = strcat(table2array((data(:,1))), {' '}, table2array((data(:,2 ))));
    tmp = [d data(:,3)]; % date / time / acceleration
    out = [out;tmp]; % vertical concatenation
    
    %out = renamevars(out,'Var1','Date & Time');
    
end

writetable(out,'file.xls')

[Mathieu NOE]

hello again

to your first comment, I used also fullfile to make sure the ouput excel file is located in the right directory

to your second request see the code below (that includes the modification above)

if you want to make a generic code , the main section should become a function, then you would simply loop according to how much of input files must be processed for one excel output file

path = 'D:\2019';   
S = dir(fullfile(path, '*', 'data.xls'));

%% export 1 : first five (01-01-2019 to 05-01-2019)
n = 5;
out = [];
filename_out = 'file1.xls';

for k = 1:n
    F = fullfile(S(k).folder,S(k).name);
    data = readtable(F);
    d = strcat(table2array(data(:,1)), {' '}, table2array(data(:,2 )));
    tmp = [d data(:,3)]; % date / time / acceleration
    out = [out;tmp]; % vertical concatenation
    %out = renamevars(out,'Var1','Date & Time');
end
writetable(out,fullfile(path,filename_out));

%% export 2 : for the rest (06-01-2019 to 10-01-2019)
out = [];
filename_out = 'file2.xls';

for k = n+1:numel(S)
    F = fullfile(S(k).folder,S(k).name);
    data = readtable(F);
    d = strcat(table2array(data(:,1)), {' '}, table2array(data(:,2 )));
    tmp = [d data(:,3)]; % date / time / acceleration
    out = [out;tmp]; % vertical concatenation
    %out = renamevars(out,'Var1','Date & Time');
end
writetable(out,fullfile(path,filename_out));

[Mathieu NOE]

simply change the start / stop index in the for loop :

for k = 3:7 .... see below

path = 'D:\2019';   
S = dir(fullfile(path, '*', 'data.xls'));

%% export folders 3 to 7
out = [];
filename_out = 'file3to7.xls';

for k = 3:7  % export folders 3 to 7
    F = fullfile(S(k).folder,S(k).name);
    data = readtable(F);
    d = strcat(table2array(data(:,1)), {' '}, table2array(data(:,2 )));
    tmp = [d data(:,3)]; % date / time / acceleration
    out = [out;tmp]; % vertical concatenation
    %out = renamevars(out,'Var1','Date & Time');
end
writetable(out,fullfile(path,filename_out));

[Mathieu NOE]

hello

the excel files attached in your previous comment are not daily data as far as I have understood your problem

for me the daily files are your "041A0259.xls" files and that is what I used again to propose you a better and more verstaile code

now you can easily parametrize :

• which folders you want ( first and last folders number are used in the name of the output excel file, so you don't have to rename manually each time)
• which columns of data you want (we could also put first / last columns values in the output excel file name if that is interesting for you)

I also noticed that those two lines of code are not needed , we can remove them without changing anything to the output data structure

    d = strcat(table2array(data(:,1)), {' '}, table2array(data(:,2 )));
    tmp = [d data(:,3)]; % date / time / acceleration

so this is my latest release , check it out !

path = 'D:\2019';   
S = dir(fullfile(path, '*', 'data.xls'));

%% export folders defined below
% folders_to_export = (1:3); % case 1
folders_to_export = (3:7); % case 2

%% select columns (indexes) to keep 
% columns_to_keep = (1:3); % case 1 : only first 3 columns : date / time / acceleration
columns_to_keep = (1:13); % case 2 : only first 13 columns 
% columns_to_keep = (1:37); % case 3 : all columns

%% main loop
out = [];
filename_out = ['file_folders' num2str(folders_to_export(1)) 'to' num2str(folders_to_export(end)) '.xls']; % the excel output name contains the selected folders 

for ci = 1:numel(folders_to_export)  % export folders according to values in vector 'folders_to_export'
    k = folders_to_export(ci)
    F = fullfile(S(k).folder,S(k).name);
    data = readtable(F);
    out = [out;data(:,columns_to_keep)]; % vertical concatenation
end
writetable(out,fullfile(path,filename_out));

[Mathieu NOE]

as always

my pleasure !

[Mathieu NOE]

you can also select the columns this way (4th option) : here I keep 4 columns : 1,2,3,15 th column of data file

%% select columns (indexes) to keep 
% columns_to_keep = (1:3); % case 1 : only first 3 columns : date / time / acceleration
% columns_to_keep = (1:13); % case 2 : only first 13 columns 
% columns_to_keep = (1:37); % case 3 : all columns
columns_to_keep = [1 2 3 15]; % case 4 : select specific columns

[Mathieu NOE]

hello

ok I'll see how to make it automatic ... but I will do that a bit latter as I am busy today

[Mathieu NOE]

try this , I hope it's working fine on your side too

path = 'D:\2019';  % you 
S = dir(fullfile(path, '*', 'data.xls'));

%% export folders by month
folders_to_export = numel(S); % case "monthly"

%% select columns (indexes) to keep 
% columns_to_keep = (1:3); % case 1 : only first 3 columns : date / time / acceleration
% columns_to_keep = (1:13); % case 2 : only first 13 columns 
% columns_to_keep = (1:37); % case 3 : all columns
columns_to_keep = [1 2 3 15]; % case 4 : select specific columns


%% main loop

month_number_old = 1;
out = [];

for ci = 1:folders_to_export  % export folders according to values in vector 'folders_to_export'
    folder_name = S(ci).folder;
    file_name = S(ci).name;
    
    % detect month 
    month_number = extractBetween(folder_name,'-','-');
    month_number = str2num(month_number{:});
   % month_name = datestr(datetime(1,month_number,1),'mmmm');

    if month_number > month_number_old % we are switching to the next month 

        % 1 / save previous month data 
        month_name = datestr(datetime(1,month_number_old,1),'mmmm');
        filename_out = [month_name '.xls']; % the excel output name contains the selected folders 
        writetable(out,fullfile(path,filename_out));
        
        % 2 / start a new data concat (1st iteration)
        month_number_old = month_number; % update month_number_old
        F = fullfile(folder_name,file_name);
        data = readtable(F);
        out = [data(:,columns_to_keep)]; % vertical concatenation
    
    else   % we are still in the same month so keep concat data
        
        F = fullfile(folder_name,file_name);
        data = readtable(F);
        out = [out;data(:,columns_to_keep)]; % vertical concatenation       
         
    end
    
    % if we have reached the last iteration (last folder), then
    % save the data (this last case is not covered by the if/else loop
    % above)
    if ci == folders_to_export
        month_name = datestr(datetime(1,month_number,1),'mmmm');
        filename_out = [month_name '.xls']; % the excel output name contains the selected folders 
        writetable(out,fullfile(path,filename_out));
    end
end

[Mathieu NOE]

hello

yes you can either do it manually :

filename_out = ['Sensor1_'month_name '.xls'];

or if the data files contains the sensor reference we could also automatically pick that info

do you have such files to share ?

for the NaN's , yes they appear in the matlab tables but once I save them to xls they don't show up as NaN in excel

[Mathieu NOE]

Sorry this was a typo

this is now correct :

filename_out = ['Sensor1_' month_name '.xls'];

[Mathieu NOE]

NB you have two lines in the code where we use filename_out

to avoid any mistake when you change the Sensor name / number I prefer to put a specific line at the beginning so that both lines are modified at the same time

sensor = 'Sensor1_'

makes the code more robust

path = 'D:\2019';  % you 
S = dir(fullfile(path, '*', 'data.xls'));

%% export folders by month
folders_to_export = numel(S); % case "monthly"

%% select columns (indexes) to keep 
% columns_to_keep = (1:3); % case 1 : only first 3 columns : date / time / acceleration
% columns_to_keep = (1:13); % case 2 : only first 13 columns 
% columns_to_keep = (1:37); % case 3 : all columns
columns_to_keep = [1 2 3 15]; % case 4 : select specific columns


%% main loop
sensor = 'Sensor1_'

month_number_old = 1;
out = [];

for ci = 1:folders_to_export  % export folders according to values in vector 'folders_to_export'
    folder_name = S(ci).folder;
    file_name = S(ci).name;
    
    % detect month 
    month_number = extractBetween(folder_name,'-','-');
    month_number = str2num(month_number{:});

    if month_number > month_number_old % we are switching to the next month 

        % 1 / save previous month data 
        month_name = datestr(datetime(1,month_number_old,1),'mmmm');
        filename_out = [sensor month_name '.xls']; % the excel output name contains the selected folders 
        writetable(out,fullfile(path,filename_out));
        
        % 2 / start a new data concat (1st iteration)
        month_number_old = month_number; % update month_number_old
        F = fullfile(folder_name,file_name);
        data = readtable(F);
        out = [data(:,columns_to_keep)]; % vertical concatenation
    
    else   % we are still in the same month so keep concat data
        
        F = fullfile(folder_name,file_name);
        data = readtable(F);
        out = [out;data(:,columns_to_keep)]; % vertical concatenation       
         
    end
    
    % if we have reached the last iteration (last folder), then
    % save the data (this last case is not covered by the if/else loop
    % above)
    if ci == folders_to_export
        month_name = datestr(datetime(1,month_number,1),'mmmm');
        filename_out = [sensor month_name '.xls'];
        writetable(out,fullfile(path,filename_out));
               
    end
end

[Mathieu NOE]

try this

path = pwd;  
S = dir([path '\*.xls']);      

% create "list" ( month numbers matching month position )
n = 0;
list = [];
list_month = {'January','February','March','April','May','June','July','August','September','October','November','December'}';

for k = 1:numel(S)
    tmp = split(S(k).name,{'_','.'}); % keep only the month string extracted  from excel file filename (format : "Sensor1_January.xls")
    
    if contains(tmp{1},'Sensor')  % select only valid xls files (with "Sensor" in the name)
        n = n+1;
        [tf,idx] = ismember(tmp{2},list_month);% look for membership with month list (idx contains position in list)
        list(n) = idx;
    end
end

[newlist,order] = sort(list);

% execute the data concatenation following the numbers listed in "order" 
out = [];
for k = 1:numel(order)
        filename = S(order(k)).name % see command window : now excel files are processed in correct order
        F = fullfile(path,filename);
        data = readtable(F);
        out = [out;data];
end
writetable(out, 'all_data.xls');

[Mathieu NOE]

ok

so the code worked for all excel files ?

can you share what you have in variable "out"

(you can save it in a mat file and use the paperclip button)

[Mathieu NOE]

or if you could share the 12 excel files ?

[Mathieu NOE]

I understand

I have made this code assuming you wantes the excel files start with "Sensor. like : "Sensor.._January.xls"

we can change that if this is no more your wish

[Mathieu NOE]

ok , so I changed the code for files not having "Sensor." in their names , simply the month names

path = pwd;  
S = dir([path '\*.xls']);      

% create "list" ( month numbers matching month position )
n = 0;
list = [];
list_month = {'January','February','March','April','May','June','July','August','September','October','November','December'}';

for k = 1:numel(S)
    tmp = split(S(k).name,{'_','.'}); % keep only the month string extracted  from excel file filename (format : "Sensor1_January.xls")
    [tf,idx] = ismember(tmp{1},list_month);% look for membership with month list (idx contains position in list)
    list(k) = idx;
end

list = list(list>0); % remove non valid xls files

[newlist,order] = sort(list);

% execute the data concatenation following the numbers listed in "order" 
out = [];
for k = 1:numel(order)
        filename = S(order(k)).name % see command window : now excel files are processed in correct order
        F = fullfile(path,filename);
        data = readtable(F);
        out = [out;data];
end
writetable(out, 'all_data.xls');

[Mathieu NOE]

there must be another reason, but not the size by itself...

Is there any maximum for the size of matlab table? - MATLAB Answers - MATLAB Central (mathworks.com)

can you try with only 2 or 3 excel (months) files and then increase to see if it happens at a particuar number of monthes ?

[Mathieu NOE]

Now I remember that there is a maximum size for excel spreadsheets

the older format (xls) is even more limited than the newer one (xlsx) :

Excel versions 97-2003 (Windows) have a file extension of XLS and the worksheet size is 65,536 rows and 256 columns.

In Excel 2007 and 2010 the default file extension is XLSX and the worksheet size is 1,048,576 rows and 16,384 columns

So change to xlsx format when you use writetable in the code (for large tables)

[Mathieu NOE]

that is strange

when I do the concatenation with my code and 3 files (Jan / Feb / Nov , the last two are simply a copy of Jan data) I have correct dispaly of time for both xls and xlsx annual files :

[Mathieu NOE]

maybe this is more an excel problem - does it change when you force the column to be format "hour:min" ?

progressbar is nice to have - why reinventing the wheel ?

progressbar - File Exchange - MATLAB Central (mathworks.com)

FYI, you don't have to have all the functions in the working directory

I have created my own directory where I store all functions I have either created or downloaded

you simply need to add it to matlab path so it's accessible from everywhere

[Mathieu NOE]

you have to force the cell format according to what you want - by default excel may not know how you want the data to be displayed

for the date , here I forced the column cell format to be yyyy-mm-dd. The extra time display (which was always 00:00) has disappeared now

[Mathieu NOE]

hello again

the code works even if monthly data files are missing

If you don't have January.xls file for example, the code will start with February data

[Mathieu NOE]

ok

what do you have in variable "out" in this case ? empty ?

[Mathieu NOE]

what is the value of folders_to_export ?

[Mathieu NOE]

I have no issue with the code whatever the number of folders (present or not) where you have the data file ()

so I wonder if you have the same folder structure and data file name as me

folders (my example) : (year - month - day)

\2019-01-01\

\2019-01-02\

\2019-01-03\

\2019-02-01\

\2019-02-02\

\2019-11-01\

each folder (one day) contains one file , with always the same name : 041A0259.xls

[Mathieu NOE]

could you zip the 275 folders and send it to me ?

[Mathieu NOE]

hello

I fixed the problem (the original code was assuming we would always start with January data)

now I simply check first wich months are present in the folder and start with the first available month (here April)

otherwise no big change in the main code

I tested it on my side and it seems to work well

here the updated code :

% you 
path = 'D:\'; 
S = dir(fullfile(path, '*', 'data.xls'));


%% export folders by month
folders_to_export = numel(S); % case "monthly"

%% select columns (indexes) to keep 
% columns_to_keep = (1:3); % case 1 : only first 3 columns : date / time / acceleration
% columns_to_keep = (1:13); % case 2 : only first 13 columns 
% columns_to_keep = (1:37); % case 3 : all columns
columns_to_keep = [1 2 3 15]; % case 4 : select specific columns

% get list of monthes (unique)
folders_month_numbers = extractBetween([S.folder],'-','-');
folders_month_numbers = unique(str2double(folders_month_numbers)); % unique and sorted month values


%% main loop
% sensor = 'Sensor1_'; % add this prefix to the output excel file name
sensor = '';  % no prefix 

month_number_old = folders_month_numbers(1); % init to first month number extracted from above (folders_month_numbers)
out = [];

for ci = 1:folders_to_export  % export folders according to values in vector 'folders_to_export'
    
    folder_name = S(ci).folder;
    file_name = S(ci).name;
    % detect month 
    month_number = extractBetween(folder_name,'-','-');
    month_number = str2num(month_number{:});
    
    if month_number > month_number_old % we are switching to the next month 
        % 1 / save previous month data 
        month_name = datestr(datetime(1,month_number_old,1),'mmmm');
        filename_out = [sensor month_name '.xls']; % the excel output name contains the selected folders 
        writetable(out,fullfile(path,filename_out));
        % 2 / start a new data concat (1st iteration)
        month_number_old = month_number; % update month_number_old
        F = fullfile(folder_name,file_name);
        data = readtable(F);
        out = [data(:,columns_to_keep)]; % vertical concatenation       
    else   % we are still in the same month so keep concat data       
        F = fullfile(folder_name,file_name);
        data = readtable(F);
        out = [out;data(:,columns_to_keep)]; % vertical concatenation             
    end
    
    if ci == folders_to_export
        month_name = datestr(datetime(1,month_number,1),'mmmm');
        filename_out = [sensor month_name '.xls'];
        writetable(out,fullfile(path,filename_out));
    end
end

[Mathieu NOE]

My pleasure !

[Mathieu NOE]

hello again

could you share some files so I can test the new code ?

[Mathieu NOE]

seems to me the file names do not contain the years, so it should be mm_dd_HH_MM_ss.csv (and not mm_dd_yy_HH_MM_ss.csv)

so if this is true , here below some code that I hope will work also for you

it assumes you have only one month max of files in your current folder , because I have only coded a routine that checks which day we are , regardless of month

also I don't know what info is usefull to retrieve from the csv files , so I opted to read them as cells, and save again the results as cells into xlsx output file

with the provided 3 csv files , the output excal file name is 03-05.xlsx (same month = 3, same day = 5)

hope it helps !

%  I have inside a folder minutely generated files (with format mm_dd_yy_HH_MM_ss.csv),
%  how can the above code be modified in order to create daily files?

% correction : format should be : mm_dd_HH_MM_ss.csv (year is not present)

% you 
path = 'D:\'; 
S = dir(fullfile(path, '*.csv'));
N = numel(S); % 

%% main loop
% sensor = 'Sensor1_'; % add this prefix to the output excel file name
sensor = '';  % no prefix 
day_previous = 1; % init to default value (day 1)
out = [];

for ci = 1:N  % loop over number of files 
    
    folder_name = S(ci).folder;
    filename = S(ci).name; % format mm_dd_HH_MM_ss.csv
    tmp = str2double(split(filename(1:length(filename) - 4),'_'));
    
    % compute month / day / ...
    month = tmp(1);
    day = tmp(2);
%     hour  = tmp(3);
%     minutes  = tmp(4);
%     seconds  = tmp(5);
    
    
    if day > day_previous % we are switching to the next day 
        
        % 1 / save previous daily data (if data is non empty !)
        if ~isempty(out)
            filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day_previous) '.xlsx']; % the excel output name contains month and day values
            writecell(out,fullfile(path,filename_out));
        end
        
        % 2 / start a new data concat (1st iteration)
        day_previous = day; % update 
        F = fullfile(folder_name,filename);
        data = readcell(F,"Delimiter",",");
        out = [data]; %    
        
    else   % we are still in the same day so keep concat data       
        F = fullfile(folder_name,filename);
        data = readcell(F,"Delimiter",",");
        out = [out;data]; % vertical concatenation             
    end
    
end

% save last daily data 
if ci == N
    filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day) '.xlsx']; % the excel output name contains month and day values
    writecell(out,fullfile(path,filename_out));
end

[Ancalagon8]

Thank you for your answer!

Unfortunatelly I had "Undefined function or variable 'readcell'" error (i run Matlab 2018b).

[Mathieu NOE]

ok, so this is a fix for older matlab releases - download the two functions in attachment

in the code I replaced :

• readcell function with csv2cellfast
• writecell function with cell2csv

NB that now the output files are now in csv format

Code updated :

% you 
path = 'D:\'; 
S = dir(fullfile(path, '*.csv'));
N = numel(S); % 

%% main loop
% sensor = 'Sensor1_'; % add this prefix to the output excel file name
sensor = '';  % no prefix 
day_previous = 1; % init to default value (day 1)
out = [];

for ci = 1:N  % loop over number of files 
    
    folder_name = S(ci).folder;
    filename = S(ci).name; % format mm_dd_HH_MM_ss.csv
    tmp = str2double(split(filename(1:length(filename) - 4),'_'));
    
    % compute month / day / ...
    month = tmp(1);
    day = tmp(2);
%     hour  = tmp(3);
%     minutes  = tmp(4);
%     seconds  = tmp(5);
    
    
    if day > day_previous % we are switching to the next day 
        
        % 1 / save previous daily data (if data is non empty !)
        if ~isempty(out)
            filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day_previous) '.csv']; % the excel output name contains month and day values
            cell2csv(fullfile(path,filename_out),out);
        end
        
        % 2 / start a new data concat (1st iteration)
        day_previous = day; % update 
        F = fullfile(folder_name,filename);
        data = csv2cellfast(F,'fromfile');
        out = [data]; %    
        
    else   % we are still in the same day so keep concat data       
        F = fullfile(folder_name,filename);
        data = csv2cellfast(F,'fromfile');
        out = [out;data]; % vertical concatenation             
    end
    
end

% save last daily data 
if ci == N
    filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day) '.csv']; % the excel output name contains month and day values
    cell2csv(fullfile(path,filename_out),out);
end

[Mathieu NOE]

ok, I will work on that topic (how to plot the data)

but what you have in your mat file is correct, : for each line , you have five cells containing the line splitted in 5 pieces (because there are four commas, and we asked to split according to commas, but we could have done it differently - because I was not sure what info / data / format you want to keep or store or plot from the many csv files.

just for your info I compared the first input csv file (first line) with the corresponding first line of the output csv file, and then your mat file - everything matches (according to my explanation above) :

input file : 03_05_23_34_00.csv

first line :

Tue Mar 5 23:34:00 2024 34{'y': 1.2282254516601563, 'x': 1.9823989746093749, 'z': -10.158597644042969}{'y': 0.5572519083969466, 'x': -2.2213740458015265, 'z': -0.8549618320610687}

output file : 03-05.csv

first line :

Tue Mar 5 23:34:00 2024 34{'y': 1.2282254516601563, 'x': 1.9823989746093749, 'z': -10.158597644042969}{'y': 0.5572519083969466, 'x': -2.2213740458015265, 'z': -0.8549618320610687}

it's identical !

your mat file :

tmp = data(1,:); % to extract the first line

tmp(:) = 5×1 cell array

{'Tue Mar 5 23:36:00 2024 36{'y': 1.4125789794921875'}

{' 'x': 1.7836802368164062' }

{' 'z': -10.12507882080078}{'y': 0.9312977099236641' }

{' 'x': -2.236641221374046' }

{' 'z': -1.0229007633587786}' }

=> five cells containing the line splitted in 5 pieces (because there are four commas)

maybe if you need only the x,y,z data (not the dates ?) we can even make the whole process a bit simpler and faster , instead of storing everythingin cells.

[Mathieu NOE]

hello

try this new code

I made few changes - it's faster now because we are not loading anymore the data to cells and we don't save the final result as cells to csv files either (a not so fast method)

instead we load the data simply as a big char array , do the daily concatenation, then save again from char to csv file.

Then we need to extract the x,y,z data from the final char array - to make this simpler, I put the more complex code into a function ([x1,y1,z1,x2,y2,z2] = extract_xyz(out)) so the main code remains simpler to read.

there should be one figure per day that display both x,y,z data sets

to make this code works , you need to download the attached function (freadtxt.m) ;

try it !

% you 
path = 'D:\'; 
S = dir(fullfile(path, '*00.csv'));
N = numel(S); % 

%% main loop
% sensor = 'Sensor1_'; % add this prefix to the output excel file name
sensor = '';  % no prefix 
day_previous = 1; % init to default value (day 1)
out = [];

for ci = 1:N  % loop over number of files 
    
    folder_name = S(ci).folder;
    filename = S(ci).name; % format mm_dd_HH_MM_ss.csv
    tmp = str2double(split(filename(1:length(filename) - 4),'_'));
    
    % compute month / day / ...
    month = tmp(1);
    day = tmp(2);
    
    
    if day > day_previous % we are switching to the next day 
        
        % 1 / save previous daily data (if data is non empty !)
        if ~isempty(out)
            filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day_previous) '.csv']; % the output name contains month and day values
            
            % save the data in output file (comment this section if you don't
            % need to save)
             dlmwrite(filename_out,out,'delimiter','');
            
            % plot the data
            [x1,y1,z1,x2,y2,z2] = extract_xyz(out); % see function below
                % extract x,y,z datas from "out" char array 
                % data are organized as follows (two sets of y/x/z datas side by side)
                % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}
                
            figure
            plot(x1)
            title(['Data plot for day = ' num2str(day_previous)]);
            hold on
            plot(y1)
            plot(z1)
            plot(x2)
            plot(y2)
            plot(z2)  
            legend('x1','y1','z1','x2','y2','z2');
            xlabel('samples');
            hold off
        end
        
        % 2 / start a new data concat (1st iteration)
        day_previous = day; % update 
        F = fullfile(folder_name,filename);
        data = freadtxt(F);
        out = [data]; %    
        
    else   % we are still in the same day so keep concat data       
        F = fullfile(folder_name,filename);
        data = freadtxt(F);
        out = [out data]; % horizontal  concatenation             
    end
    
end

% save last daily data 
if ci == N
    
         filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day) '.csv']; % the output name contains month and day values

        % save the data in output file (comment this section if you don't
        % need to save)
         dlmwrite(filename_out,out,'delimiter','');

        % plot the data
        [x1,y1,z1,x2,y2,z2] = extract_xyz(out); % see function below
            % extract x,y,z datas from "out" char array 
            % data are organized as follows (two sets of y/x/z datas side by side)
            % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}

        figure
        plot(x1)
        title(['Data plot for day = ' num2str(day)]);
        hold on
        plot(y1)
        plot(z1)
        plot(x2)
        plot(y2)
        plot(z2)  
        legend('x1','y1','z1','x2','y2','z2');
        xlabel('samples');
        hold off
end


%%%%%%%%%%%%%%%%%%% functions %%%%%%%%%%%%%%%%%%%%%%%%%%%

function [x1,y1,z1,x2,y2,z2] = extract_xyz(out)
    % extract x,y,z datas from "out" char array 
    % data are organized as follows (two sets of y/x/z datas side by side)
    % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}
    tmp = extractBetween(out,'{','}'); % we get both sets of data in alternate way
    [m,~] = size(tmp);

    % first data set
    tmp1 = tmp((1:2:m),:);
    % Calling the regexp() function over the above cell array to extract number part
    B = regexp(tmp1,'-?[0-9]+.[0-9]+', 'match'); % extract numeric parts inside the cell

    for k=1:numel(B)
        tmp1_out(k,:) = str2double([B{k}]);
    end
    y1 = tmp1_out(:,1);
    x1 = tmp1_out(:,2); 
    z1 = tmp1_out(:,3); 

    % second data set
    tmp2 = tmp((2:2:m),:);
    % Calling the regexp() function over the above cell array to extract number part
    B = regexp(tmp2,'-?[0-9]+.[0-9]+', 'match'); % extract numeric parts inside the cell
    for k=1:numel(B)
        tmp2_out(k,:) = str2double([B{k}]);
    end
    y2 = tmp2_out(:,1);
    x2 = tmp2_out(:,2); 
    z2 = tmp2_out(:,3); 
            
end

[Mathieu NOE]

this is a small improvement , so the time axis is now given in minutes , instead of samples

% you 
path = 'D:\'; 
S = dir(fullfile(path, '*00.csv'));
N = numel(S); % 

%% main loop
% sensor = 'Sensor1_'; % add this prefix to the output excel file name
sensor = '';  % no prefix 
day_previous = 1; % init to default value (day 1)
out = [];

for ci = 1:N  % loop over number of files 
    
    folder_name = S(ci).folder;
    filename = S(ci).name; % format mm_dd_HH_MM_ss.csv
    tmp = str2double(split(filename(1:length(filename) - 4),'_'));
    
    % compute month / day / ...
    month = tmp(1);
    day = tmp(2);
    
    
    if day > day_previous % we are switching to the next day 
        
        % 1 / save previous daily data (if data is non empty !)
        if ~isempty(out)
            filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day_previous) '.csv']; % the output name contains month and day values
            
            % save the data in output file (comment this section if you don't
            % need to save)
             dlmwrite(filename_out,out,'delimiter','');
            
            % plot the data
            [x1,y1,z1,x2,y2,z2] = extract_xyz(out); % see function below
            % extract x,y,z datas from "out" char array 
            % data are organized as follows (two sets of y/x/z datas side by side)
            % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}

            % create time vector, in minutes for the display
            time = (0:numel(x1)-1)*dt/(60); % division by (60) to go from seconds to minutes
            figure
            plot(time,x1,time,y1,time,z1,time,x2,time,y2,time,z2)
            title(['Data plot for day = ' num2str(day_previous)]);
            legend('x1','y1','z1','x2','y2','z2');
            xlabel('minutes');
        end
        
        % 2 / start a new data concat (1st iteration)
        day_previous = day; % update 
        F = fullfile(folder_name,filename);
        data = freadtxt(F);
        out = [data]; %    store data
        
        % new : define sampling time - we have 1 minute record per file 
        samples = count(data,'''y''')/2; % division by 2 because the pattern 'y' appears twice per line
        dt = 60/samples; % sampling time in seconds
        
    else   % we are still in the same day so keep concat data       
        F = fullfile(folder_name,filename);
        data = freadtxt(F);
        out = [out data]; % horizontal  concatenation             
    end
    
end

% save last daily data 
if ci == N
    
         filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day) '.csv']; % the output name contains month and day values

        % save the data in output file (comment this section if you don't
        % need to save)
         dlmwrite(filename_out,out,'delimiter','');

        % plot the data
        [x1,y1,z1,x2,y2,z2] = extract_xyz(out); % see function below
        % extract x,y,z datas from "out" char array 
        % data are organized as follows (two sets of y/x/z datas side by side)
        % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}

        % create time vector, in minutes for the display
        time = (0:numel(x1)-1)*dt/(60); % division by (60) to go from seconds to minutes
        figure
        plot(time,x1,time,y1,time,z1,time,x2,time,y2,time,z2)
        title(['Data plot for day = ' num2str(day_previous)]);
        legend('x1','y1','z1','x2','y2','z2');
        xlabel('minutes');
end


%%%%%%%%%%%%%%%%%%% functions %%%%%%%%%%%%%%%%%%%%%%%%%%%

function [x1,y1,z1,x2,y2,z2] = extract_xyz(out)
    % extract x,y,z datas from "out" char array 
    % data are organized as follows (two sets of y/x/z datas side by side)
    % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}
    tmp = extractBetween(out,'{','}'); % we get both sets of data in alternate way
    [m,~] = size(tmp);

    % first data set
    tmp1 = tmp((1:2:m),:);
    % Calling the regexp() function over the above cell array to extract number part
    B = regexp(tmp1,'-?[0-9]+.[0-9]+', 'match'); % extract numeric parts inside the cell

    for k=1:numel(B)
        tmp1_out(k,:) = str2double([B{k}]);
    end
    y1 = tmp1_out(:,1);
    x1 = tmp1_out(:,2); 
    z1 = tmp1_out(:,3); 

    % second data set
    tmp2 = tmp((2:2:m),:);
    % Calling the regexp() function over the above cell array to extract number part
    B = regexp(tmp2,'-?[0-9]+.[0-9]+', 'match'); % extract numeric parts inside the cell
    for k=1:numel(B)
        tmp2_out(k,:) = str2double([B{k}]);
    end
    y2 = tmp2_out(:,1);
    x2 = tmp2_out(:,2); 
    z2 = tmp2_out(:,3); 
            
end

[Mathieu NOE]

my last code should give you time in minutes

t = 0 (origin) is de fined as the first sample of the first file

now your files are already big as the sampling rate is very high (more than 6000 samples per minute)

so I would not even try to put all dates for each individual samples on the Xticks or it will be completely unreadable at the end

do you need the "true" date (that is in the csv file) or only a time vector as I did above ?

if we plot the data for each day , maybe ploting only hours or minutes is enough ?

[Mathieu NOE]

I made a simple modification so that my time vector is not starting always at 0

but, as we have the time info in the csv files names, I can initialize my time vector with these infos

for example, with your provided files , time would start at t = 34 min (day 5)

this is now used in the plot

% you 
path = 'D:\'; 
S = dir(fullfile(path, '*00.csv'));
N = numel(S); % 

%% main loop
% sensor = 'Sensor1_'; % add this prefix to the output excel file name
sensor = '';  % no prefix 
day_previous = 1; % init to default value (day 1)
out = [];

for ci = 1:N  % loop over number of files 
    
    folder_name = S(ci).folder;
    filename = S(ci).name; % format mm_dd_HH_MM_ss.csv
    tmp = str2double(split(filename(1:length(filename) - 4),'_'));
    
    % compute month / day / ...
    month = tmp(1);
    day = tmp(2);
    hour  = tmp(3);
    minutes  = tmp(4);
    seconds  = tmp(5);    
    
    if day > day_previous % we are switching to the next day 
        
        % 1 / save previous daily data (if data is non empty !)
        if ~isempty(out)
            filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day_previous) '.csv']; % the output name contains month and day values
            
            % save the data in output file (comment this section if you don't
            % need to save)
             dlmwrite(filename_out,out,'delimiter','');
            
            % plot the data
            [x1,y1,z1,x2,y2,z2] = extract_xyz(out); % see function below
            % extract x,y,z datas from "out" char array 
            % data are organized as follows (two sets of y/x/z datas side by side)
            % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}

            % create time vector, in minutes for the display
            time = ((0:numel(x1)-1)*dt + seconds)/60 + minutes; % division by (60) to go from seconds to minutes
            figure
            plot(time,x1,time,y1,time,z1,time,x2,time,y2,time,z2)
            title(['Data plot for day = ' num2str(day_previous)]);
            legend('x1','y1','z1','x2','y2','z2');
            xlabel('minutes');
        end
        
        % 2 / start a new data concat (1st iteration)
        day_previous = day; % update 
        F = fullfile(folder_name,filename);
        data = freadtxt(F);
        out = [data]; %    store data
        
        % new : define sampling time - we have 1 minute record per file 
        samples = count(data,'''y''')/2; % division by 2 because the pattern 'y' appears twice per line
        dt = 60/samples; % sampling time in seconds
        
    else   % we are still in the same day so keep concat data       
        F = fullfile(folder_name,filename);
        data = freadtxt(F);
        out = [out data]; % horizontal  concatenation             
    end
    
end

% save last daily data 
if ci == N
    
         filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day) '.csv']; % the output name contains month and day values

        % save the data in output file (comment this section if you don't
        % need to save)
         dlmwrite(filename_out,out,'delimiter','');

        % plot the data
        [x1,y1,z1,x2,y2,z2] = extract_xyz(out); % see function below
        % extract x,y,z datas from "out" char array 
        % data are organized as follows (two sets of y/x/z datas side by side)
        % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}

        % create time vector, in minutes for the display
        time = ((0:numel(x1)-1)*dt + seconds)/60 + minutes; % division by (60) to go from seconds to minutes
        figure
        plot(time,x1,time,y1,time,z1,time,x2,time,y2,time,z2)
        title(['Data plot for day = ' num2str(day_previous)]);
        legend('x1','y1','z1','x2','y2','z2');
        xlabel('minutes');
end


%%%%%%%%%%%%%%%%%%% functions %%%%%%%%%%%%%%%%%%%%%%%%%%%

function [x1,y1,z1,x2,y2,z2] = extract_xyz(out)
    % extract x,y,z datas from "out" char array 
    % data are organized as follows (two sets of y/x/z datas side by side)
    % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}
    tmp = extractBetween(out,'{','}'); % we get both sets of data in alternate way
    [m,~] = size(tmp);

    % first data set
    tmp1 = tmp((1:2:m),:);
    % Calling the regexp() function over the above cell array to extract number part
    B = regexp(tmp1,'-?[0-9]+.[0-9]+', 'match'); % extract numeric parts inside the cell

    for k=1:numel(B)
        tmp1_out(k,:) = str2double([B{k}]);
    end
    y1 = tmp1_out(:,1);
    x1 = tmp1_out(:,2); 
    z1 = tmp1_out(:,3); 

    % second data set
    tmp2 = tmp((2:2:m),:);
    % Calling the regexp() function over the above cell array to extract number part
    B = regexp(tmp2,'-?[0-9]+.[0-9]+', 'match'); % extract numeric parts inside the cell
    for k=1:numel(B)
        tmp2_out(k,:) = str2double([B{k}]);
    end
    y2 = tmp2_out(:,1);
    x2 = tmp2_out(:,2); 
    z2 = tmp2_out(:,3); 
            
end

[Mathieu NOE]

sorry, I am not sure to understand

do you want to take the mean (average) of your data ?

I don't know if this is what you wanted , but we could make the average of every (1 minute) csv file, so at the end you would have a graph with 1 min averaging (and for a full day we would then have 24*60 values)

[Mathieu NOE]

ok - I one word, you want to detrend the data (there is a function detrend for that, or if you simply want to remove the mean value, you can also like you said )

[Mathieu NOE]

ok, this is the new vesion with detrending (done in the function [x1,y1,z1,x2,y2,z2] = extract_xyz(out))

I also did a modification on how to generate the time vector. This new version can handle the case of missing csv input files and creates a gap for the missing file

in my situation, I have 3 files corresponding to t = 34 , 35 and 36 minutes

if for any reason, there is no "35 min" file (or any number of missing files) , I wanted that we see a gap corresponding to the missing file(s).

In the previous code this gap would not happen

now, with the new code (below) , if for example, you remove the file 03_05_23_35_00.csv from the folder, you see this gap and the time vector matches the fact that we have started at t = 34 min and we must stop at t = 36+1 = 37 min

Code :

% you 
path = 'D:\'; 
S = dir(fullfile(path, '*00.csv'));
N = numel(S); % 

%% main loop
% sensor = 'Sensor1_'; % add this prefix to the output excel file name
sensor = '';  % no prefix 
day_previous = 1; % init to default value (day 1)
out = [];
time = [];             
time_total = [];              

for ci = 1:N  % loop over number of files 
    
    folder_name = S(ci).folder;
    filename = S(ci).name; % format mm_dd_HH_MM_ss.csv
    tmp = str2double(split(filename(1:length(filename) - 4),'_'));
    
    % compute month / day / ...
    month = tmp(1);
    day = tmp(2);
    hour  = tmp(3);
    minutes  = tmp(4);
    seconds  = tmp(5);    
    
    if day > day_previous % we are switching to the next day 
        
        % 1 / save previous daily data (if data is non empty !)
        if ~isempty(out)
            filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day_previous) '.csv']; % the output name contains month and day values
            
            % save the data in output file (comment this section if you don't
            % need to save)
             dlmwrite(filename_out,out,'delimiter','');
            
            % plot the data
            [x1,y1,z1,x2,y2,z2] = extract_xyz(out); % see function below
            % extract x,y,z datas from "out" char array 
            % data are organized as follows (two sets of y/x/z datas side by side)
            % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}
            

            figure
            plot(time_total,x1,time_total,y1,time_total,z1,time_total,x2,time_total,y2,time_total,z2)
            title(['Detrended Data plot for day = ' num2str(day_previous)]);
            legend('x1','y1','z1','x2','y2','z2');
            xlabel('minutes');
        end
        
        % 2 / start a new data concat (1st iteration)
        day_previous = day; % update 
        F = fullfile(folder_name,filename);
        data = freadtxt(F);
        out = [data]; %    store data
        
        % define sampling time - we have 1 minute record per file 
        samples = count(data,'''y''')/2; % division by 2 because the pattern 'y' appears twice per line
        dt = 60/samples; % sampling time in seconds
        
        % create one dedicated time vector per input (csv) file
        % values are  in minutes 
        time = ((0:samples-1)*dt + seconds)/60 + minutes; % division by (60) to go from seconds to minutes
        time_total = [time]; % horizontal  concatenation             
     
    else   % we are still in the same day so keep concat data       
        F = fullfile(folder_name,filename);
        data = freadtxt(F);
        out = [out data]; % horizontal  concatenation    
        
        % define sampling time - we have 1 minute record per file 
        samples = count(data,'''y''')/2; % division by 2 because the pattern 'y' appears twice per line
        dt = 60/samples; % sampling time in seconds
        
        % create one dedicated time vector per input (csv) file
        % values are  in minutes 
        time = ((0:samples-1)*dt + seconds)/60 + minutes; % division by (60) to go from seconds to minutes
        time_total = [time_total time]; % horizontal  concatenation             
    end
    
end

% save last daily data 
if ci == N
    
         filename_out = [sprintf('%02d',month) '-' sprintf('%02d',day) '.csv']; % the output name contains month and day values

        % save the data in output file (comment this section if you don't
        % need to save)
         dlmwrite(filename_out,out,'delimiter','');

        % plot the data
        [x1,y1,z1,x2,y2,z2] = extract_xyz(out); % see function below
        % extract x,y,z datas from "out" char array 
        % data are organized as follows (two sets of y/x/z datas side by side)
        % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}
        
        figure
        plot(time_total,x1,time_total,y1,time_total,z1,time_total,x2,time_total,y2,time_total,z2)
        title(['Detrended Data plot for day = ' num2str(day_previous)]);
        legend('x1','y1','z1','x2','y2','z2');
        xlabel('minutes');
end


%%%%%%%%%%%%%%%%%%% functions %%%%%%%%%%%%%%%%%%%%%%%%%%%

function [x1,y1,z1,x2,y2,z2] = extract_xyz(out)
    % extract x,y,z datas from "out" char array 
    % data are organized as follows (two sets of y/x/z datas side by side)
    % {'y': 1.22, 'x': 1.98, 'z': -10.15}{'y': 0.55, 'x': -2.22, 'z': -0.85}
    tmp = extractBetween(out,'{','}'); % we get both sets of data in alternate way
    [m,~] = size(tmp);

    % first data set
    tmp1 = tmp((1:2:m),:);
    % Calling the regexp() function over the above cell array to extract number part
    B = regexp(tmp1,'-?[0-9]+.[0-9]+', 'match'); % extract numeric parts inside the cell

    for k=1:numel(B)
        tmp1_out(k,:) = str2double([B{k}]);
    end
    y1 = tmp1_out(:,1);
    x1 = tmp1_out(:,2); 
    z1 = tmp1_out(:,3); 

    % second data set
    tmp2 = tmp((2:2:m),:);
    % Calling the regexp() function over the above cell array to extract number part
    B = regexp(tmp2,'-?[0-9]+.[0-9]+', 'match'); % extract numeric parts inside the cell
    for k=1:numel(B)
        tmp2_out(k,:) = str2double([B{k}]);
    end
    y2 = tmp2_out(:,1);
    x2 = tmp2_out(:,2); 
    z2 = tmp2_out(:,3); 
    
        % detrend the data 
    x1 = detrend(x1); % removes the best fit linear trend
    y1 = detrend(y1); % removes the best fit linear trend
    z1 = detrend(z1); % removes the best fit linear trend
    x2 = detrend(x2); % removes the best fit linear trend
    y2 = detrend(y2); % removes the best fit linear trend
    z2 = detrend(z2); % removes the best fit linear trend

            
end

[Mathieu NOE]

as always, my pleasure !!