How can I combine data in multiple excel files an write the combined data in a new excel file?

I have in one folder a lot of excel files.Each of excel file contain data like this:(for example)

(First excel file)

 1     2    3

 1.1  2.2  3.3 

 80   90   91

(second excel file)

 4     5    6

 4.4   5.5  6.6

 88    87   86

I want to combine only row 1 and row 2 to become like this in a newly created excel file.

 1     2     3      4     5    6

 1.1   2.2   3.3   4.4   5.5  6.6

Can someone help me to solve this problem.(Thank you)

Answer 1

[Iman Ansari]

Hi

a=xlsread('name1.xlsx');
b=xlsread('name2.xlsx');
N=[a(1:2,:) b(1:2,:)]
xlswrite('New.xlsx',N);

files with similar names (name1.xlsx,name2.xlsx,name3.xlsx,...):

   b=[];
  for i=1:n
      name=['name' num2str(i) '.xlsx'];
      a=xlsread(name);
      b=[b a(1:2,:)];
  end
  xlswrite('New.xlsx',b);

[shazwani amran]

that's help a lot.thanks. this is my further question. how can i manipulate the data as below? (first excel file)

 1     2    3
 1.1  2.2  3.3 
 80   90   91

(second excel file)

 4     5    6
 4.4   5.5  6.6
 88    87   86
I want to combine only row 1 and row 2 to become like this in a newly created excel file.

 1     2     3      4     5    6    7     8    9
 1.1   2.2   3.3   
                   4.4   5.5  6.6
                                    7.7  8.8  9.9
                                                   .......(and so on)                                   
Can someone help me to solve this problem.(Thank you)

[Iman Ansari]

If you want other elements became zeros:

b=[]
for i=1:3
    name=['name' num2str(i) '.xlsx'];
    a=xlsread(name);
    n1=size(a,2);
    [m n]=size(b);
    b(1,n+1:n+n1)=a(1,:);
    b(i+1,n+1:n+n1)=a(2,:);
end
xlswrite('New.xlsx',b);

Or you can use cell array to don't have zeros:

b={};
for i=1:3
    name=['name' num2str(i) '.xlsx'];
    a=xlsread(name);
    a=num2cell(a);
    n1=size(a,2);
    [m n]=size(b);
    b(1,n+1:n+n1)=a(1,:);
    b(i+1,n+1:n+n1)=a(2,:);
end
xlswrite('New.xlsx',b);

[shazwani amran]

Yes! I got it.thanks a lot for ur help.really appreciate it.:)

[VIKAS POONIA]

Hlo Sir,

First excel file

1 2 3

1.1 2.2 3.3

80 90 91

Second Excel file

4 5 6

4.4 5.5 6.6

88 87 86

I want to combine only first column of first and second excel file to make 1 excel file (save individually as 1.xlsx) and then second column of first and second excel file to make another excel file as 2.xlsx.

Can someone help me to solve this problem.

Answer 2

[KRUNAL]

What to do when you have files with same name i.e the two input files as well as the output file

Answer 3

[Ahmad Bilal]

If I have data in different sheets ( different sheets have different names) in one excel file. How i can modify this code?

   b=[];
  for i=1:n
      name=['name' num2str(i) '.xlsx'];
      a=xlsread(name);
      b=[b a(1:2,:)];
  end
  xlswrite('New.xlsx',b);

[Ahmad Bilal]

Until now I have developed this code:

[~,sheets] = xlsfinfo('C:\Users\Bilal Ahmad\Desktop\Complete Simulations\Microphone Array 1_AllSources\new_excel.xlsx');

for i =1:length(sheets)
    data{i} = xlsread('C:\Users\Bilal Ahmad\Desktop\Complete Simulations\Microphone Array 1_AllSources\new_excel.xlsx',sheets{i},'C2:C4');
     
  
   
end

Answer 4

[Go Sugar]

Hey! What if I want to combine multiple inputs (60) raw by raw to one excel file without copying the variable names?

I want each subject (excel file) in a separate raw (not column). I tried the below code. It gives an error of "Dimensions of matrices being concatenated are not consistent".

for m=1:60

file= strcat('output',num2str(m),'.xlsx');

data=xlsread(file);

filedata=[data; data(m)];

alldata=table(filedata);

writetable(alldata, 'alldata.xlsx');

end

I appriciate your help. Thank you. :)