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

19 views (last 30 days)
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

Accepted Answer

Guillaume
Guillaume on 24 May 2017
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
  3 Comments
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
Daphne PARLIARI on 12 Mar 2020
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.

Sign in to comment.

More Answers (0)

Categories

Find more on Data Import from MATLAB in Help Center and File Exchange

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!