how do i combine multible excel file into single file using matlab?

Dear experiences ...

I have many excel file in certain folder 100+ (D:\source), where these files are named in the following structre {0.xlsx, 1.xlsx,2.xlsx, ....etc}, all excel files havin the following columns contents look like the following ..

No  Index  File Name
1  6  2012-07-20-18-23-02
2  7  3097-success-edit
3  8  2012-07-20-18-30-44
4  11  2012-large-columns
5  13  2012-admins-logs
etc..

so i need to combine all these files into single excel file involve the following contents:

Id  Index  File Name              file_label
1  6  2012-07-20-18-23-02     0
2  7  3097-success-edit       0
3  8  2012-07-20-18-30-44     0
4  11  2012-large-columns      1
5  13  2012-admins-logs        1

where ID here is numeric count for the rows, index whatever extracted from every excel file, File name whetever extracted from every file, and finally labels is give value based on file name, in the above example for the file names that extracted from 0.xlsx file its give an (0) values to all file names that comes from this 0.xlsx file, and (1) for the file names that comes from 1.xlsx file, and (2) for the file names that come from 2.xlsx file and so on..

i would thank any one can help me in this issue.

thanks

Answer 1

[Guillaume]

Assuming your excel files all have consistent header, this should be fairly simple to achieve:

path = 'D:\source';
files = dir(fullfile(path, '*.xlsx'));
result = table();         
for fidx = 1:numel(files)
   filecontent = readtable(fullfile(path, files(fidx).name)); 
   [~, filenumber] = fileparts(files(fidx).name);
   filecontent.file_label = repmat(str2double(filenumber), height(filecontent), 1);
   result = [result; filecontent];
end

[ahmed obaid]

smart soultion, thanks a lot

[Kasih Ditaningtyas Sari Pratiwi]

Hi Guillaume. I want to ask about how to merge multiple xls files into a single file with the same worksheet. I read some questions which also have the same problem, but mostly the resulted file has separate worksheets. In my case, I want to merge the table so that it only has one worksheet. I try this code below:

%%Merge multiple XLS files into one XLS file
[files,dir] = uigetfile('*.xls','Select Files','MultiSelect','on');
output = fullfile(folder,'rainfall.xls');
addpath(dir);
for i = 1:numel(files)
    ftab = readtable(files{i});
    writetable(ftab,output,'Sheet',filenames{i});
end

but of course it has separated worksheets. I want to make it in one worksheet as the files have also the same structure so it should be easily to merge. Do you know what should I modify in my code above? Thank you very much. I really appreciate for your help.

[Daphne PARLIARI]

Hey Guillaume.

I tried your code (thanks for that!) and for me, it stops in the 6th line with the error "Dot indexing is not supported for variables of this type.".

The code I am trying:

path = append(output_path,'\',stations{i},'\');
files = append(path, 'Stats ', months ,' ', stations{i} ,' ',vars{1},' .csv'); 
%files = string(files);
%str = char(files);
result = table();         
for fidx = 1:numel(files)
   filecontent = readtable(files{fidx}); 
   [~, filenumber] = fileparts(files(fidx).name);
   filecontent.file_label = repmat(str2double(filenumber), height(filecontent), 1);
   result = [result; filecontent];
end

Just to help you understand, stations{i} come from the attached .xlsx and the attached .csv is one of the many files I want to combine.