Concatenate multiple .csv files horizontally

Dear MATLAB aficionados!

I am trying to horizontally concatenate multiple .csv files produced using Freesurfer (see attached 'Trial_001.csv' and 'Trial_002.csv'). I want to concatenate the values listed after each of the fields (e.g. "10", "mean", "stdev") horizontally into a single .csv file and preserve the file name as the column header for each trial. I have tried the below which outputs the attached 'Compiled.csv'. This however concatanates vertically. When I try using horzcat, it returns "Duplicate table variable name: 'measures'". Beyond manually relabelling every .csv file, I am not sure how to resolve. Also, neither option preserves the file names.

Any advice would be greatly appreciated!

input_path = '/Users/annabellesorby-adams/TRIAL/01_Output_Files' ;
results_path = '/Users/annabellesorby-adams/TRIAL/03_Compiled' ;
output_file = [results_path filesep 'Compiled.csv'];

file_info = dir(fullfile(input_path,'*.csv'));
full_file_names = fullfile(input_path,{file_info.name});
n_files = numel(file_info);
all_data = cell(1,n_files);

for ii = 1:n_files
        all_data{ii} = readtable(full_file_names{ii},'PreserveVariableNames',false);
end

all_data{:}
writetable(cat(1,all_data{:}),output_file);
disp 'All done!'

Answer 1

[Image Analyst]

Try it this way:

input_path = pwd; %'/Users/annabellesorby-adams/TRIAL/01_Output_Files' ;
results_path = pwd; %'/Users/annabellesorby-adams/TRIAL/03_Compiled' ;
fullOutputFileName = fullfile(results_path, 'Compiled.xlsx')
file_info = dir(fullfile(input_path,'Trial_*.csv'));
allFullFileNames = fullfile(input_path,{file_info.name})
numberOfFiles = numel(file_info);
nextColumn = 1;
for k = 1:numberOfFiles
    thisInputFileName = allFullFileNames{k};
    thisData = readtable(thisInputFileName);
    % Write out this data to the output workbook in the appropriate column.
    % first convert column number to the column letter that Excel uses.
    columnLetter = char(ExcelCol(nextColumn)); % Is in the attached function.
    fprintf('Writing file #%d of %d ("%s") into column %d (%s).\n', ...
        numberOfFiles, thisInputFileName, nextColumn, columnLetter);
    cellReference = sprintf('%s1', columnLetter);
    writetable(thisData, fullOutputFileName, 'Range', cellReference, 'WriteMode','inplace');
    % Increment column number for next time:
    nextColumn = nextColumn + width(thisData);
end
uiwait(helpdlg( 'All done!'));
if ispc
    promptMessage = sprintf('Do you want to open the output file in Excel?');
    titleBarCaption = 'Continue?';
    buttonText = questdlg(promptMessage, titleBarCaption, 'Yes.  Open it.', 'No. Quit', 'Yes.  Open it.');
    if contains(buttonText, 'Yes', 'IgnoreCase', true)
        fprintf('Opening file "%s") in Excel.\n', fullOutputFileName);
        winopen(fullOutputFileName);
    end
end

[Annabel Sorby-Adams]

Oh my goodness, thank you, this works perfectly! The only one thing would be to ask if it is possible to change the title of the column to be named for the trial (e.g., Trial 001, Trial 002). In the attached excel output from the code provided, the column headers for the values state 'x' but it would be good to have this labelled for the trial to run stats. Please let me know!

[Image Analyst]

You've probably figured it out by now, but you can get the properties of the table just after you read it in, then change them by appending the base file name, then write it out with the new column headers. Here is the code.

input_path = pwd; %'/Users/annabellesorby-adams/TRIAL/01_Output_Files' ;
results_path = pwd; %'/Users/annabellesorby-adams/TRIAL/03_Compiled' ;
fullOutputFileName = fullfile(results_path, 'Compiled.xlsx')
file_info = dir(fullfile(input_path,'Trial_*.csv'));
allFullFileNames = fullfile(input_path,{file_info.name})
numberOfFiles = numel(file_info);
nextColumn = 1;
for k = 1:numberOfFiles
    thisInputFileName = allFullFileNames{k};
    thisData = readtable(thisInputFileName);
    
    % Change the column header to add the base file name.
    oldColumnHeaderNames = thisData.Properties.VariableNames;
    [folder, baseFileNameNoExt, ext] = fileparts(thisInputFileName);
    header1 = sprintf('%s_%s', oldColumnHeaderNames{1}, baseFileNameNoExt);
    header2 = sprintf('%s_%s', oldColumnHeaderNames{2}, baseFileNameNoExt);
    % Replace the old header names with new names.
    thisData.Properties.VariableNames = {header1, header2}
    
    % Write out this data to the output workbook in the appropriate column.
    % first convert column number to the column letter that Excel uses.
    columnLetter = char(ExcelCol(nextColumn)); % Is in the attached function.
    fprintf('Writing file #%d of %d ("%s") into column %d (%s).\n', ...
        numberOfFiles, thisInputFileName, nextColumn, columnLetter);
    cellReference = sprintf('%s1', columnLetter);
    writetable(thisData, fullOutputFileName, 'Range', cellReference, 'WriteMode','inplace');
    % Increment column number for next time:
    nextColumn = nextColumn + width(thisData);
end
uiwait(helpdlg( 'All done!'));
if ispc
    promptMessage = sprintf('Do you want to open the output file in Excel?');
    titleBarCaption = 'Continue?';
    buttonText = questdlg(promptMessage, titleBarCaption, 'Yes.  Open it.', 'No. Quit', 'Yes.  Open it.');
    if contains(buttonText, 'Yes', 'IgnoreCase', true)
        fprintf('Opening file "%s") in Excel.\n', fullOutputFileName);
        winopen(fullOutputFileName);
    end
end

[Annabel Sorby-Adams]

Thank you so much! This works perfectly. I really appreciate it!