How to read all CSV files within a folder, combine into one master file using columns, and edit column headers?

I have 12 csv files (file0, file1, ..., file11) with 3 columns of data each. The first column is the same for every file. I seek to:

1. Open a particular folder
2. Create a "for" loop to read all files in the folder
3. Create a "master" excel workbook file to hold the combined files in one sheet
4. Transfer all three columns in file0 to the master file
5. Use a "for" loop to add columns 2 and 3 of all other files (file1, file2, ..., file11) to the master file. They should be placed in new columns, not added to the existing ones
6. Change the name of each column

I have looked at other solutions for similar questions but can't find a solution with updated versions of MATLAB. My process for step 1 is below. How do I continue from here?

folderPath1 = 'C:\Users\matav\Documents\MATLAB\example';
cd(folderPath1); % opens path of the folder

[Stephen23]

"How do I continue from here?"

Byt getting rid of this line:

cd(folderPath1);

Beginners use CD (which is slow and makes debugging harder), whereas the robust and efficient approach is to use relative/absolute filenames. All MATLAB functions which import/export file data accept relative/absolute filenames.

You will need to use FULLFILE, e.g.:

P = 'C:\Users\matav\Documents\MATLAB\example';
S = dir(fullfile(P,'*.csv'));
etc.

As the MATLAB documentation states, avoid CD in code:

https://www.mathworks.com/help/matlab/matlab_prog/techniques-for-improving-performance.html#buwj1nn

[Alicia Matavosian]

Thank you for your advice. I noticed that dir() loads the files out of order with file0, file1, file10, file11, file2, ...file 11. The order of my files is very important so to avoid this, I manually assigned variables in my other comment. Do you have any suggestions for keeping the files in order?

[Stephen23]

" I noticed that dir() loads the files out of order with file0, file1, file10, file11, file2, ...file 11"

They are (most likely) in character order (that certainly explains the order seen with many common file systems).

"Do you have any suggestions for keeping the files in order?"

You could download and use my FEX submission NATSORTFILES, which was written to solve this exact issue:

S = natsortfiles(S);

https://www.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort

As an alternative, you could simply generate the filenames using SPRINTF, as shown in the MATLAB help:

https://www.mathworks.com/help/matlab/import_export/process-a-sequence-of-files.html

Answer 1

[KSSV]

folderPath1 = 'C:\Users\matav\Documents\MATLAB\example';
cd(folderPath1); % opens path of the folder
csvFiles = dir('*.csv') ;
N = length(csvFiles) ; 
T = readtable(csvFiles(1).name) ; 
c1 = T.(1) ;
c2 = T.(2) ;
c3 = T.(3) ;
for i = 1:N
    T = readtable(csvFiles(i).name) ;    
    c2 = c2+T.(2) ;
    c3 = c3+T.(3) ; 
end
T = table(c1,c2,c3) ; 

[Alicia Matavosian]

@KSSV,

Thank you for your post. Your code appears to sum columns 2 and 3 across all files. I wish to move colums 2 and 3 on each file into a unique column on the combined table. I also noticed that dir() loads the files out of order with file0, file1, file10, file11, file2, ...file 11. Because of this, I used your code as a base to manually give columns 2 and 3 a variable name. The code below shows a portion of this (the same pattern is repeated for all 12 files).

How do I move these columns into a new table and manually assign the columns? For instance, for columns 1-7, there would be c0_1, c0_2, c0_3, c1_2, c1_3, c2_2, c2_3

P = 'C:\Users\matav\Documents\MATLAB\example';
%csvFiles = dir(fullfile(P,'*.csv'));
%N = length(csvFiles) ; %This line will help with a "for" loop later
T0 = readtable('file0.csv') ; 
c0_1 = T0.(1) ; %column 1
c0_2 = T0.(2) ; %column 2 from file0
c0_3 = T0.(3) ;%column 3 from file0

T1 = readtable('file1.csv') ; 
c1_2 = T1.(2) ; %column 2 from file1
c1_3 = T1.(3) ;%column 3 from file1

T2 = readtable('file2.csv') ; 
c2_2 = T2.(2) ; %column 2 from file2
c2_3 = T2.(3) ;%column 3 from file2

[Stephen23]

"For instance, for columns 1-7, there would be c0_1, c0_2, c0_3, c1_2, c1_3, c2_2, c2_3"

Most likely a better use of a table would simply add a "file" variable to each imported table, and then concatenate all of the imported tables vertically. Then you can use the normal grouping aproaches to processing groups of data:

https://www.mathworks.com/help/matlab/ref/splitapply.html#bux1l61

Having lots of fiddly variable names like that is unlikely to make it easy to process your data.