How to combine multiple csv into 1 csv file?

Hello guys, I wanted to combine multiple csv files into one single file. Is it possible to do that? I have searched anywhere but it seems the code doesn't return any value or open microsoft excel at all...

Here's my code:

 num = {};
 text = {};
 all = {};
 p=dir('C:\Users\Kenny\Desktop\skripsi\pakebandingyangini\masuk\cowo\*.csv');
 for i=1:length(p)
    [num{end+1}, text{end+1}, all{end+1}]= xlsread(['C:\Users\Kenny\Desktop\skripsi\pakebandingyangini\masuk\cowo\', p(i).name]);
 end

How to see the output of this code or, how can I join these csvs into 1 single csv file?

Thank you for your help.

Answer 1

[Image Analyst]

Read in the different csv files with csvread(). Then concatenate them and write them out with csvwrite()

csv1 = csvread(filename1);
csv2 = csvread(filename2);
csv3 = csvread(filename3);
allCsv = [csv1;csv2;csv3]; % Concatenate vertically
csvwrite(outputFileName, allCsv);

[dpb]

Won't that get sorta' slow(*) if there are lots of large files, plus it duplicates all the data...???

I've always thought it really would be nice if there were more OS functions/facilities that had been natively implemented by TMW, but otoh, I can see the point in "why duplicate what already exists?" and it would take time away from what is really uniquely supported. Double-edged sword (altho the one that really drives me batty is not having the optional switches on dir)

(*) Better on a speed basis than the alternative of

csv=[];
for each file
  csv=[csv; csvread(file)];
end

certainly but memory-intensive.

Hmmm....thought. What about the ASCII-file option with SAVE and 'append'? Never timed it; don't know how it does performance-wise albeit it's somewhat limited in output format (but csvwrite is even more so).

[Image Analyst]

It might be. But I wasn't sure about your solution of copying a bunch of files onto one output file. I didn't know if that would give you the output file with just the last file it copied, or if the copy would somehow automatically do an append of all the files. So I did the append "manually".

[dpb]

C:\> help copy
Copies one or more files to another location.

COPY [/D] [/V] [/N] [/Y | /-Y] [/Z] [/A | /B ] source [/A | /B]
   [+ source [/A | /B] [+ ...]] [destination [/A | /B]]
...
To append files, specify a single file for destination, but multiple files
for source (using wildcards or file1+file2+file3 format).

C:\>

This is specifically from CMD.EXE but I'm sure every shell will have an equivalent form if not exactly if not on Windows platform (which I presumed OP is given reference to Excel)

Just came to me the other pretty efficient way all within Matlab would be to simply read/write each file as stream with fread/fwrite

[Walter Roberson]

The use of '+' to concatenate multiple files into one is specific to MS Windows. In OS-X and Linux, the typical mechanism would be (at the shell level)

cat file1 file2 file3 > outputfile

[dpb]

I guess I sorta' knew that, Walter; thanks for posting it.

I moved from VAX/VMS to the micro world of embedded systems for a number of years before the PC and never have had occasion to use a -nix like OS...so, I couldn't have come up w/ the syntax prior to having seen it, but knew there just had to be one... :)

Answer 2

[Md Adil]

%% Load in files
files_in_fold=dir('*.csv');
full_table=table;
for ii=1:length(files_in_fold)
    temp=readtable(files_in_fold(ii).name);
    for jj=1:height(temp)
        name_table{jj,1}=files_in_fold(ii).name;
    end
    temp=[temp name_table];
    full_table=[full_table;temp];
end
writetable(full_table,'Combined_CSVs.csv','WriteRowNames',true);

@Walter Roberson & @Image Analyst this code is working for me to combine the csv files with their names but the problem is it can't read more than 8 cells of csv file. If there is more than 8 cells, it can't read it. Can you please help me solving this problem?

[Image Analyst]

Yes, but how? You forgot to attach one of your CSV files, so how can we figure out why it's only reading part of it. Perhaps the format is messed up.

If you have any more questions, then attach your data in a new thread (not here) and code to read it in with the paperclip icon after you read this:

TUTORIAL: How to ask a question (on Answers) and get a fast answer

Answer 3

[dpb]

TMW hasn't implemented much in the way of the OS inside Matlab; use the OS instead...

rootdir='C:\Users\Kenny\Desktop\skripsi\pakebandingyangini\masuk\cowo');
outname='allfiles';       % a new name for the output file
cmd=['copy ' fullfile(rootdir,'*.csv') ' ' fullfile(rootdir,outname,'.csv')];  % build OS COPY command
system(cmd)               % and submit it to OS...

METHOD 2

Per my late awakening in sidebar conversation with IA, an "all Matlab" solution that is pretty efficient...

d=dir(fullfile(rootdir,'*.csv');                 % retrieve the files
fido=fopen(fullfile(rootdir,'newout.csv'),'w');  % open output file to write
for i=1:length(d)
  fidi=fopen(fullfile(rootdir,d(i).name));       % open input file
  fwrite(fido,fread(fidi,'*char'));              % copy to output
  fclose(fidi);                                  % close that input file
end
fido=fclose(fido); clear fid*           % close output file, remove temporaries

[Kasih Ditaningtyas Sari Pratiwi]

Hi dpb, I use also the method 2 code to combine multiple files into single file, but I got an error : "Struct contents reference from a non-struct array object." Here is my code :

%%Merge multiple CSV files into one CSV file
myDir = uigetfile('*.csv','Select the data file','MultiSelect','on');   % gets directory from any folder
d=fullfile(myDir,'*.csv');                                              % retrieve the files
fido=fopen(fullfile('finalCSVnew.csv'),'w');                            % open output file to write
for i=1:length(d)
  fidi=fopen(fullfile(myDir,d(i).name));                                % open input file
  fwrite(fido,fread(fidi,'*char'));                                     % copy to output
  fclose(fidi);                                                         % close that input file
end
fido=fclose(fido); clear fid* d                                         % close output file, remove temporaries

Could you please help me in solving the problem? Thank you so much for your help.

[Image Analyst]

Lots and lots wrong with that - too much to explain. Just see the corrected code below:

% Merge multiple CSV files into one CSV file
[filenames, folder] = uigetfile('*.csv','Select the data file','MultiSelect','on')   % gets directory from any folder
% Create output file name in the same folder.
outputFileName = fullfile(folder, 'finalCSVnew.csv')
fidOutput = fopen(outputFileName, 'wt');                            % open output file to write
for k = 1 : length(filenames)
  % Get this file name.
  thisFileName = fullfile(folder, filenames{k})
  % Open input file:
  fidInput = fopen(thisFileName);  
  % Read text from it
  thisText = fread(fidInput, '*char');
  % Copy to output file:
  fwrite(fidOutput, thisText);    
  fclose(fidInput);   % close the input file
end
fido=fclose(fidOutput);
% Open Windows Explorer to this folder
winopen(folder);

[Tanumaya Bhowmik]

Will this script eliminate headers from individual files before combining?

[Image Analyst]

No, it just transfers everything. You'd have to use fgetl() to pull out lines one at a time and then use contains() to skip any header lines you do not want to transfer. Then use fprintf() to copy the lines you want out to the output file. Can you figure it out? Here is a snippet to get you started:

% Open the file for reading in text mode.
fileID = fopen(fullFileName, 'rt');
% Read the first line of the file.
textLine = fgetl(fileID);
lineCounter = 1;
while ischar(textLine)
	% Print out what line we're operating on.
	fprintf('%s\n', textLine);
	% Read the next line.
	textLine = fgetl(fileID);
	lineCounter = lineCounter + 1;
end
% All done reading all lines, so close the file.
fclose(fileID);

[Walter Roberson]

If there are exactly NumHeaders lines of headers in each file, and you want to keep them at the very beginning, then

NumHeaders = 1;

% Merge multiple CSV files into one CSV file
[filenames, folder] = uigetfile('*.csv','Select the data file','MultiSelect','on')   % gets directory from any folder
% Create output file name in the same folder.
outputFileName = fullfile(folder, 'finalCSVnew.csv')
fidOutput = fopen(outputFileName, 'wt');                            % open output file to write
for k = 1 : length(filenames)
  % Get this file name.
  thisFileName = fullfile(folder, filenames{k})
  % Open input file:
  fidInput = fopen(thisFileName);  
  %skip headers except for first file
  if k ~= 1
      for L = 1 : NumHeaders; fgetl(fidInput); end
  end
  % Read text from it
  thisText = fread(fidInput, '*char');
  % Copy to output file:
  fwrite(fidOutput, thisText);    
  fclose(fidInput);   % close the input file
end
fido=fclose(fidOutput);
% Open Windows Explorer to this folder
winopen(folder);

[KRISHNA PATIL]

whenever I try to run your code it says "braces indexing is not supported for variable of this type" how to fix this issue?

[Image Analyst]

You probably selected only one file so filenames is probably a string rather than a cell array of strings. So do this:

if numel(filenames) > 1
    thisFileName = fullfile(folder, filenames{k})
else
    thisFileName = fullfile(folder, filenames)
end

[Walter Roberson]

NumHeaders = 1;

% Merge multiple CSV files into one CSV file
[filenames, folder] = uigetfile('*.csv','Select the data file','MultiSelect','on');   % gets directory from any folder
if isnumeric(filenames); return; end   %user cancel
filenames = cellstr(filenames);   %in case user only selected one

% Create output file name in the same folder.
outputFileName = fullfile(folder, 'finalCSVnew.csv')
fidOutput = fopen(outputFileName, 'wt');                            % open output file to write

for k = 1 : length(filenames)
  % Get this file name.
  thisFileName = fullfile(folder, filenames{k})
  % Open input file:
  fidInput = fopen(thisFileName);  
  %skip headers except for first file
  if k ~= 1
      for L = 1 : NumHeaders; fgetl(fidInput); end
  end
  % Read text from it
  thisText = fread(fidInput, '*char');
  % Copy to output file:
  fwrite(fidOutput, thisText);    
  fclose(fidInput);   % close the input file
end

fido=fclose(fidOutput);

% Open Windows Explorer to this folder
winopen(folder);

[Cakil]

Hi, I have tried to merge my csv files with the code of Image Analyst. It worked without any problem but the files are merged vertically. Is there a way to merge them horizontally?

[Walter Roberson]

@Ezgi Asirok

Unfortunately in order to merge csv files horizontally, the entire csv file has to be rewritten each time the file is added to. Therefore the usual technique is to read everything into memory first and then write the data out. If the number of rows in each file is not exactly the same, then you need to take care to pad the rows with nans (and possibly empty strings) to ensure they are all the same length.

[Rudra Ramakrishnan]

Do you have a code for copying the csv files horizontally ?

[Image Analyst]

Why do you need it that way? Give an example of how you'd like it.

[Rudra Ramakrishnan]

There are around 285 files with 2 colums of data, I need both the columns of the first file and the second column of all the other files copied side by side.

I then need to process the data row wise like getting the average, standard deviation, etc

Is there any way ?

[Walter Roberson]

Untested code.

The code could be made notably simpler for the case in which all of the columns are numeric or it was certain that all of the files were the same size. Half of this code is taken up with the possibility that the files are different sizes, and that either the old data or the new data needs to be padded with NaN or "" or as appropriate, without any assumptions about the data types of the columns. If one of the files has numeric columns and another one has string columns and another has character vector columns... this code should (hopefully) take care of padding as necessary with content that is appropriate for the data type.

The code does, however, expect that all columns are one of the types that can be returned by readtable(). If you are modifying this code to merge together existing tables that might have variables that are (for example) Transfer Functions, then the code to pad rows might well break down.

%merge csv files horizontally
[filenames, folder] = uigetfile('*.csv','Select the data file','MultiSelect','on');   % gets directory from any folder
if isnumeric(filenames); return; end   %user cancel
filenames = cellstr(filenames);   %in case user only selected one
fullnames = fullfile(folder, filenames);
nfiles = length(fullnames);

existingvars = {};
combined = [];
for K = 1 : nfiles
    thisfile = fullnames{K};
    thisdata = readtable(thisfile);
    
    %the variable names might already existing in another file. Every
    %variable name for a table must be unique.
    
    thisvars = thisdata.Properties.VariableNames;
    combinedvars = [existingvars, thisvars];
    U = matlab.lang.makeUniqueStrings(combinedvars);
    thisvars = U(length(existingvars)+1:end);
    thisdata.Properties.VariableNames = thisvars;
    existingvars = combinedvars;
    
    if K == 1
        combined = thisdata;
    else
        if height(thisdata) > height(combined)
            numnew = height(thisdata) - height(combined);
            E = combined(1,:);
            C = repmat({missing}, 1, width(E));
            C(table2array(varfun(@iscell, E))) = {{missing}};
            E(1,:) = C;
            E = repmat(E, numnew, 1);
            combined = [combined; E];
        elseif height(thisdata) < height(combined);
            numnew = height(combined) - height(thisdata);
            E = thisdata(1,:);
            C = repmat({missing}, 1, width(E));
            C(table2array(varfun(@iscell, E))) = {{missing}};
            E(1,:) = C;
            E = repmat(E, numnew, 1);
            thisdata = [thisdata; E];
        end
        combined = [combined, thisdata];
    end
end

[Md Adil]

Hello@Image Analyst @Walter Roberson your codes for horizontally merged csv files worked for me but I need to assign each file with their individual file name. How can I do that? For example I am attaching a picture. Can you please help me to assign the names of the file individually like this. Thank you for your time.

[Image Analyst]

I suggest you create a table with only 3 columns. Col 1 is the file name, col2 is Animal, and col3 is electrodes.

[Walter Roberson]

@Md Adil

To get that structure, you will need to have your table have one variable per file (named after the file), and the variable would be a table with variables Animal and electrodes.

Otherwise you would need to use a cell array in which the first and second rows contained character vectors or strings or categorical, and the remaining rows contained numeric values. It is likely that the display would not be nice -- for example you could expect that 'electrodes' will be changed to '1x10 char' on display.

It cannot be done as a table() with first variable named 'file1.csv', third variable named 'file2.csv' and so on, because that would require naming the second and 4th columns with empty variable names, which is not permitted for table variables.