How to combine multiple excel files into one file with data in separate columns?

I have 50 excel files that I need to edit and put into one file. It would be easier to do all this if there were a way to combine all the files. My file names are tek0001.xls, tek0002.xls, etc. and each contain two columns of data. Is there a way to combine all of these files into one file so that all the data will be in a new column within the new file. I have only been able to combine them into one file so that all the files are in the first two columns of the new file and that is not useful to me. Any help is appreciated thanks.

Answer 1

[Joshua]

This is a bit complex, but worked for two random excel files. You can simplify if the two columns in each excel file are always the same length. If they aren't, simplifying creates breaks in the column with no data.

n=2; % number of files
  data=[]; % initialize data matrix
  for i=1:n
      % if statement to name input file
      if i-9<0
          filename = ['tek000',num2str(i),'.xlsx'];
      else
          filename = ['tek00',num2str(i),'.xlsx'];
      end
      % checks if input file exists
      if exist(filename, 'file') == 2
          [num,txt,raw] = xlsread(filename);
          [x,y]=size(num);
          % checks to make sure each element in first column exists
          for j=1:x
              if(~isnan(num(j,1)))
                  data=[data;num(j,1)];
              end
          end
          % checks to make sure each element in second column exists
          for j=1:x
              if(~isnan(num(j,2)))
                  data=[data;num(j,2)];
              end
          end
      end
  end
  % writes extracted data to file
  xlswrite('combined',data)

Here is the simplified code:

n=2; % number of files
data=[]; % initialize data matrix
for i=1:n
    % if statement to name input file
    if i-9<0
        filename = ['tek000',num2str(i),'.xlsx'];
    else
        filename = ['tek00',num2str(i),'.xlsx'];
    end
    % checks if input file exists
    if exist(filename, 'file') == 2
        [num,txt,raw] = xlsread(filename);
        [x,y]=size(num);
        data=[data;num(:,1)];
        data=[data;num(:,2)];
    end
end
% writes extracted data to file
xlswrite('combined',data)

[Colin]

This still seems to give me a similar issue as before. It takes the first column of data from each file and then places it all in order in the first column of the new file. Then it takes all the second columns and places them in order in the first column as well.

[Joshua]

When I run it with two files, I get, in order:

file1 column1 data

file1 column2 data

file2 column1 data

file2 column2 data

Just to clarify, is that the desired behavior or did you want the column1 data from every file first, and then the column2 data from every file? If it is the first one I described, then it should work. I added some extra code to make sure, but it is working with integer data. It should not be producing the second case I described unless something weird happened. Maybe post two of your excel files for me to try if it is not information sensitive?

n=2; % number of files
data=[]; % initialize data matrix
input_file = 'combined';
if exist([input_file,'.xls'], 'file') == 2
    delete([input_file,'.xls']);
end
for i=1:n
    % if statement to name input file
    if i-9<0
        filename = ['tek000',num2str(i),'.xlsx'];
    else
        filename = ['tek00',num2str(i),'.xlsx'];
    end
    % checks if input file exists
    if exist(filename, 'file') == 2
        clear num;
        [num,txt,raw] = xlsread(filename);
        [x,y]=size(num);
        % checks to make sure each element in first column exists
                for j=1:x
                    if(~isnan(num(j,1)))
                        data=[data;num(j,1)];
                    end
                end
                % checks to make sure each element in second column exists
                for j=1:x
                    if(~isnan(num(j,2)))
                        data=[data;num(j,2)];
                    end
                end
%         data=[data;num(:,1)];
%         data=[data;num(:,2)];
    end
end
% writes extracted data to file
xlswrite(input_file,data)