How do I loop over xlsread to load and save several excel files?

Hey, I know that there are already several threads discussing this topic but I haven't found the answer for my problem: I have many excel files (daily files over period of 7 years) of the same type, each one containing 7 columns and many many rows (~30.000 rows,that's why I can't merge them in excel). The files are labelled as follows: Country_yyyymmdd.xlsx. I want to load the files and save each data matrix under the name of the original file or save them in a cell array. So far, my loop obviously overwrites the matrix mydata with each iteration. Is it possible to distribute "continuous names" in a loop? My last resort would otherwise be to carry out all data manipulation/calculations within this first loop, is this recommended?

files=dir('*.xlsx');
for i= 1: length(files)
   FileToLoad= files(i).name;
[mydata, header] = xlsread(FileToLoad);
end

Answer 1

[dpb]

What, precisely, would be better depends on data you haven't provided; namely what you need to do with the data; for example is 'country' a single one or multiple ones and is it intended to analyze only within or across countries if the latter? So many questions, so little info... :)

Anyway, it's simple enough to build a file with the name, you've got the name already from dir, simply

for i= 1: length(files)
  [mydata, header] = xlsread(files(i).name);
  [~,fname]=fileparts(files(i).name);
  save(fname, 'header', 'mydata')
end

will save the data in a .mat file for each file named as the base name of the .xls file. Alternatively, as you say, save in a cell array by incrementing an array index and enclosing the RHS of an assignment in the curly brackets or use a structure array and .hdr .dat fields for the disparate data types. Or, yet alternatively, add the data to a table (R2014x+) or a dataset if have the Statistics Toolbox.

[Leonie Hass]

I'm sorry, I have not made myself sufficiently clear. I would need one little change concerning your answer: the structure and thus the header of the Excel files is always the same (we can actually forget about the header). It's the data matrix mydata which I would like to label and save directly under the orginal file name. Thus, I want to create variables that are called 'country_yyyymmdd', and not .mat files. I would really appreciate an answer! If possible, could you also give the code line for the cell array solution. Thanks in advance!!! I'm really grateful for the help!

[dpb]

I know that's what you asked for, but you don't want to go down that road...that way "there be dragons!" :(

See the FAQ on why and alternatives including the structure solution with dynamic fields to the problem without 'poofing' variables into the workspace.

How_can_I_create_variables_A1.2C_A2.2C....2CA10_in_a_loop?

[Leonie Hass]

Okay^^, I got it!Thanks, I will look into your suggestions.

[iab]

Hi dpb!

Thanks for your help. I too looked all over for a solution and yours worked perfectly.