How to write names of many files in single excel file along with its folder name?

Dear experiences

i have many folders where every folder contain large number of text files.. i need to write their names along with parent directory name in an excel file.. such as following:

for example if there are master folder include 100 folders { fol1, fol2, fol3..etc} in every sub-folder{fol1,fol2..etc} there are some text files .. i need to write their names in an excel file where first column contain sub-folder name, and second columns contain the names of text files in that sub-folder name, and so on for others .. all result in a single excel file.. such that.

A column      B column 
fol1           1st text file name in fol1
fol1           2nd text file name in fol1
fol2           1st text file name in fol2          
etc..

i have tested the following code but its write all files in first row..

projectdir = 'D:\masterfiles';
xls_filename ='D:\masterfiles\master.xls'
column_range = A;
row_range = 2;
xls_sheet = 'Sheet1';
dinfo = dir(projectdir);
dinfo(~[dinfo.isdir]) = [];   %remove non-folders
dinfo(ismember({dinfo.name}, {'.', '..'})) = [];   %remove . and .. directories
num_subdir = length(dinfo);
for S = 1 : num_subdir
  this_subdir = dinfo(S).name;
    subdinfo = dir( fullfile(projectdir, this_subdir, '*.htm*') );
    num_subfile = length(subdinfo);
    for F = 1 : num_subfile
      this_file = subdinfo(F).name;
        xlswrite(xls_filename,{dinfo(S).name}, xls_sheet, 'A');
        xlswrite(xls_filename,{this_file}, xls_sheet, xlscol(row_range));
row_range =row_range + 1;
       end
end
thanks for any suggestion.

Answer 1

[Guillaume]

Assuming you're using R2016b, where dir supports recursing into folder these 4 lines are all you need:

projectdir = 'D:\masterfiles';
t = struct2table(dir(fullfile(projectdir, '*\*.txt')));   %find text files in all immediate subdirectories of projectdir
t.folder = strrep(t.folder, fullfile(projectdir, '\'), '');  %fullfile(path, '\') canonise the path
writetable(t(:, {'folder', 'name'}), fullfile(projectdir, 'master.xls');

[ahmed obaid]

Unrecognized variable name 'folder'., i'm using R2015a so is there any modification please ?

[Guillaume]

As I said, this requires the new version of dir that comes with R2016b.

[ahmed obaid]

i can't modified my version .. is there any modification for my issue please ..

[Guillaume]

Try this:

d = dir(fullfile(projectdir, '*'));
files = [];
for subdir = d([d.isdir] & ~ismember({d.name},{'.', '..'}))'
   subfiles = dir(fullfile(projectdir, subdir.name, '*.txt'));
   [subfiles.folder] = deal(subdir.name);
   files = [files; subfiles]; %#OK<AGROW>
end
t = struct2table(files);
writetable(t(:, {'folder', 'name'}), fullfile(projectdir, 'master.xls');

[ahmed obaid]

very fast and smart solution ... thank you a lot

Answer 2

[Jan]

Collect the data at first and write them to the file in one block:

% [EDITED, 02.02.2017 21:19 UTC]
projectdir   = 'D:\masterfiles';
xls_filename = 'D:\masterfiles\master.xls';
xls_sheet    = 'Sheet1';

dinfo = dir(projectdir);
dinfo(~[dinfo.isdir]) = [];   %remove non-folders
dinfo(ismember({dinfo.name}, {'.', '..'})) = [];   %remove . and .. directories
num_subdir = length(dinfo);
output = {};
for S = 1:num_subdir
  this_subdir = dinfo(S).name;
  subdinfo = dir(fullfile(projectdir, this_subdir, '*.htm*'));
  if ~isempty(subdinfo )
    subname  = {subdinfo.name}.';
    subpath  = repmat({this_subdir}, length(subname), 1);
    output   = cat(1, output, cat(2, subpath, subname));   % [BUGFIX]
  end
end
Range = sprintf('A%1:B%d', size(output, 1));
xlswrite(xls_filename, output, xls_sheet, Range);

[Jan]

@ahmed obaid: Are you able to fix this problem by your own?

I took the line "column_range = A;" from the code you have posted, but actually this variable is not used anywhere.

"subname = {subdinfo.name},';" should not be changed to "subname = {subdinfo.name};", but to:

subname  = {subdinfo.name}.';   % Dot instead of comma

[ahmed obaid]

thank for your help, now its only write the last folder along with its file names and keeping cells to N/A .. as in my attached image.

[Jan]

The code had another bug, which is fixed now. The range must be specified to avoid filling the complete column.