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

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

Answer 1

[Walter Roberson]

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?

[Matthew Ciccone]

Thank you! it works!!

The first portion is to convert csv to xlsx

The second portion is to consolidate all those converted sheets into one excel workbook

One issue: Write table only allows me to do 1-31 characters.... how can I extend this limit or truncate the filenames uniformly?

[Walter Roberson]

Your readtable() is reading from the csv files, not the xls files. If you need to produce individual files and combine them, then

files = fullfile({d.folder}, {d.name});
for K = 1 : length(files)
   thisfile = files{K};
   [folder, sheetname, ~] = fileparts(thisfile);
   newfile = fullfile(folder, [sheetname, '.xls']);
   fTable = readtable(thisfile)
    writetable(fTable, newfile)
    writetable(fTable, outfile, 'sheet', sheetname);
end

[Walter Roberson]

al = 'A':'Z';
nal = length(al);
t = array2table(randi(9, 5, 2))

t = 5×2 table
    Var1    Var2
    ____    ____

     5       1  
     3       8  
     3       1  
     7       9  
     8       2  

rv1 = al(randi(nal, 1, 63));
rv2 = al(randi(nal, 1, 63));
t.Properties.VariableNames = {rv1, rv2};
writetable(t, 'test.xlsx');

tin = readtable('test.xlsx', 'variablenamingrule','preserve')

tin = 5×2 table
    NDXFYPTKZQQLVQUWMABJAGUGKKJBCJXRGGVUCJWOELYZXBTTRABHUVOTPPMKEVK    JAKMBMYWNAIUMOLNRDLOTAUVVPTLCNDLVYWHMFZEWQJCQNCBIMTAETAKDFYLHWW
    _______________________________________________________________    _______________________________________________________________

                                   5                                                                  1                               
                                   3                                                                  8                               
                                   3                                                                  1                               
                                   7                                                                  9                               
                                   8                                                                  2                               

writetable can handle 63 characters for variable names.

writetable(t, 'test.xls');
tin = readtable('test.xls', 'variablenamingrule','preserve')

tin = 5×2 table
    NDXFYPTKZQQLVQUWMABJAGUGKKJBCJXRGGVUCJWOELYZXBTTRABHUVOTPPMKEVK    JAKMBMYWNAIUMOLNRDLOTAUVVPTLCNDLVYWHMFZEWQJCQNCBIMTAETAKDFYLHWW
    _______________________________________________________________    _______________________________________________________________

                                   5                                                                  1                               
                                   3                                                                  8                               
                                   3                                                                  1                               
                                   7                                                                  9                               
                                   8                                                                  2                               

and can do so for xls and xlsx files both.

[Walter Roberson]

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