How to read all CSV files within a folder, combine into one master file using columns, and edit column headers?
63 views (last 30 days)
Show older comments
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:
- Open a particular folder
- Create a "for" loop to read all files in the folder
- Create a "master" excel workbook file to hold the combined files in one sheet
- Transfer all three columns in file0 to the master file
- 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
- 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
3 Comments
Stephen23
on 26 Sep 2022
Edited: Stephen23
on 26 Sep 2022
" 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);
As an alternative, you could simply generate the filenames using SPRINTF, as shown in the MATLAB help:
Answers (1)
KSSV
on 26 Sep 2022
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) ;
2 Comments
Stephen23
on 26 Sep 2022
"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:
Having lots of fiddly variable names like that is unlikely to make it easy to process your data.
See Also
Categories
Find more on Standard File Formats in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!