A loop for multiple .xlsx files import with different names

Hi,

I trying to import multiple .xlsx files from a specific folder on my Mac, but any answers that I have found on MATLAB Answers don't helped me. The files' name are {1501 1502 ... 1912} with this form: YYmm, but occasionally I could have few omissions and in this case, I would see the message: 'YYmm file doesn't exist'.

In any file, I have strings and numbers, what's the command more adequate to the importation?

source_dir = '~/Users/username/Documents/MATLAB/CDSUSD/Amundi1';
source_files = dir(fullfile(source_dir, '*.xls'));
for t = 1:length(source_files)
  data = xlsread(fullfile(source_dir, source_files(t).name));
end

Thanks at all.

[Mario Malic]

I would check first what is the output of source_files, if you can find which files are omitted, maybe there's something wrong with the name, or maybe some files are corrupted?

[Mario Diaco]

Thank for the attention. I would see omitted files because I haven't these observation, I know it. There are not corrupted files.

[Walter Roberson]

Instead of assigning to data assign to data{t}

Answer 1

[dpb]

1. Your wildcard specification dir(fullfile(source_dir, '*.xls')) will only find files ending in precisely 'xls' and your text says you have .xlsx files. Use dir(fullfile(source_dir, '*.xlsx')); instead unless it's possible to have either (bad idea, but it happens). dir(fullfile(source_dir, '*.xls*')); will handle that case if must.
2. Your struct that dir() returns will contain no missing files so that would not be an issue using that solution (one of, if not the one) of my favorite ways to do so. Trying to build matching file names is rife with extra coding to do so and then processing to handle the possible of there being missing ones.
3. That said, if there are other files in the subdirectory besides those you do want and there's not another easy way to write the wildcard string to only returns the one desired besides the file type extension, then building the names is about the only alternative. In which case, then program defensively knowing it is expected to have some missing files:

for yr=1901:1903                 % I'm presuming the 1501, ... was typo...
  f=fullfile(source_dir,string(yr)+'.xlsx');
  try
    data=xlsread(f);
  catch
  end
end

will just skip the missing.

Will note that xlsread is deprecated on PC anyways, not sure on Mac.

[Mario Diaco]

Thanks for the answer. I saw the mistake ".xls" to ".xlsx" too late, thanks. The name "1501" is not a typo, it is the monthly observation at the time "Jan-2015".

I think that your code returns just the list of files, I would like to import the data from these files.

Yes! xlsread is recommended neither on MacOS, if you know any better solution I can change the command, I seen readtable but I hasn't been able to import numbers and strings from my files.

[dpb]

I think that your code returns just the list of files, I would like to import the data from these files."

That's what the code in the try...catch...end loop does. It just goes on if the xlsread command fails because the file doesn't exist.

If you want all the files in the subdirectory, the dir(;*.xlsx') solution is the way to go...if there are others you don't want, then you can build the comprehensive list of files for the years in question. I didn't understand the naming convention in prior response but if the filenames are just four digits of yymm, then one way to generate the full list for a number of years would be:

y1=2015; y2=2019;                                      % define start, end years
t=[datetime(y1,1,1):calmonths(1):datetime(y2,12,1)].'; % build datetime array over time
t.Format='yyMM';                                       % set desired output format
fnarray=cellstr(t);                                    % convert to the naming convention yymm

[Mario Diaco]

Thanks, the last code it's really in line with my quest. But I haven't understood where are my imported data, sorry. After I ran your first code I see a structure 1xn.files, but I have the necessity to manage the inside data.

The structure of my .xlsx file is this:

In row A4 I have the labels and I would import data by this row: RIC | Name | | Country | Weight | n. of share | Change, where the first three are alpha-numeric values ​​and the last three are numbers. I would maintain this structure to execute other operations.

Thank you for your attention.

[dpb]

Then use readtable instead of xlsread. As above in the loop the try...catch block will return the numeric data only; to reconstruct both numeric and text from a spreadsheet with xlsread you have to return either

[n,t]=xlsread(fname);  % numeric in n, text in t

or

[~,~,r]=xlsread(fname);  % all data in cell array r as cell

either takes munging on to get both data types that can operate on subsequently.