MATLAB Answers

Why wont xlsread let me read "old_name" (line14)? (dummy directories used below)

1 view (last 30 days)
fileDir = 'C:\Users\xxxxxxxxxxxxxxxxxxx';
outfile = '\\xxxxxxxxxxxxxxxxxxxxxxxxxxx';
addpath(fileDir);
fileNames = dir(fileDir);
fileNames = {fileNames.name};
fileNames = fileNames(cellfun(...
@(f)contains(f,'.csv'),fileNames));
yourfolder='C:\Users\xxxxxxxxxxxxxxxx'
d=dir([yourfolder '\*.csv']);
files={d};
for k=1:numel(files)
old_name=files{k};
[~,~,b] = xlsread(old_name);
new_name=strrep(old_name,'csv','xls')
xlswrite(new_name,b);
end
for f = 1:numel(fileNames)
fTable = readtable(fileNames{f});
writetable(fTable,outfile,'Sheet',fileNames{f});
end %close the file

Accepted Answer

Walter Roberson
Walter Roberson on 15 Jun 2021
files={d};
That should be
files = fullfile({d.folder}, {d.name});
and
writetable(fTable,outfile,'Sheet',fileNames{f});
should probably be something like
[~, sheetname, ~] = fileparts(fileNames{f});
writetable(fTable, outfile, 'Sheet', sheetname);
I see that the second part of your code is reading csv files and writing each of them as separate sheets into one output file, but I do not see the connection between doing that and the xlsread() / xlswrite() portion of the code?
  4 Comments
Walter Roberson
Walter Roberson on 16 Jun 2021
al = 'A':'Z';
nal = length(al);
t = array2table(randi(9, 5, 2))
t = 5×2 table
Var1 Var2 ____ ____ 7 6 1 8 4 6 8 4 4 7
rv1 = al(randi(nal, 1, 63));
writetable(t, 'test.xlsx', 'sheet', rv1);
Invalid sheet name. Name must be text and contain 1-31 characters, excluding :, \, /, ?, *, [, and ].
Ah, sheet names seems to be what you are talking about. That limit is built into Excel; https://www.ibm.com/support/pages/max-length-sheet-name-31
In that case:
files = fullfile({d.folder}, {d.name});
[~, sheetnames, ~] = fileparts({files.name});
sheetnames = matlab.lang.makeUniqueStrings(sheetnames, 31);
for K = 1 : length(files)
thisfile = files{K};
sheetname = sheetnames{K};
[folder, basename, ~] = fileparts(thisfile);
newfile = fullfile(folder, [basename, '.xls']);
fTable = readtable(thisfile)
writetable(fTable, newfile)
writetable(fTable, outfile, 'sheet', sheetname);
end

Sign in to comment.

More Answers (0)

Tags

Community Treasure Hunt

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

Start Hunting!