How to combine multiple excel files into one file with data in separate columns?
9 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
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.
0 comentarios
Respuestas (1)
Joshua
el 29 de Jun. de 2017
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)
2 comentarios
Joshua
el 29 de Jun. de 2017
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)
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!