Combine excel files into one

Question

Ancalagon8 2022-10-19

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1830553-combine-excel-files-into-one

评论： Mathieu NOE 2024-4-17

I want to run the code below, inside every folder i have an excel file (data.xls) and i want to compare 10 excel files into 1. Can anyone help me?

path = 'D:';
S = dir(fullfile(path, '*', 'data.xls'))
for k = 1:numel(S)
    F = fullfile(S(k).folder,S(k).name);
    data = readtable(F);
    d = datetime(strcat(table2array((data(:,1))), {' '}, table2array((data(:,2 )))));
    acceleration = table2array((data(:,3 )));
    xlswrite('file.xls', data)
end

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Mathieu NOE 2022-10-19

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 2022-10-19

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1830553-combine-excel-files-into-one#answer_1079083

编辑：Mathieu NOE 2022-11-9

在 MATLAB Online 中打开

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

67 个评论
显示 65更早的评论隐藏 65更早的评论

Mathieu NOE 2022-10-24

编辑：Mathieu NOE 2022-11-9

在 MATLAB Online 中打开

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 2022-10-25

编辑：Mathieu NOE 2022-11-9

在 MATLAB Online 中打开

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 2022-11-4

编辑：Mathieu NOE 2022-11-9

在 MATLAB Online 中打开

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 2022-11-7

编辑：Mathieu NOE 2022-11-9

在 MATLAB Online 中打开

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

Ancalagon8 2023-3-1

在 MATLAB Online 中打开

I was refering to the version of merging the daily data:

path = 'D:\';  % 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 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

In cases i do not have data for a whole month or two, I receive this error:

Error using writetable
Unsupported type 'double'. Use writematrix instead.
Error in line 23
writetable(out,fullfile(path,filename_out));

Mathieu NOE 2023-3-3

在 MATLAB Online 中打开

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 2024-4-16

在 MATLAB Online 中打开

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

Mathieu NOE 2024-4-16

在 MATLAB Online 中打开

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 2024-4-17

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 2024-4-17

在 MATLAB Online 中打开

freadtxt.m

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 2024-4-17

在 MATLAB Online 中打开

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 2024-4-17

在 MATLAB Online 中打开

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 2024-4-17

在 MATLAB Online 中打开

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

Ancalagon8 2024-4-17

@Mathieu NOE thank you!!

Mathieu NOE 2024-4-17

as always, my pleasure !!

请先登录，再进行评论。

Combine excel files into one

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

采纳的回答

67 个评论
显示 65更早的评论隐藏 65更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Combine excel files into one

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

采纳的回答

67 个评论 显示 65更早的评论隐藏 65更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

67 个评论
显示 65更早的评论隐藏 65更早的评论