Load plenty of xlsx files

Hello everybody,

I want to build a script where I would like to load several xlsx datasets at once and do further math on them afterwards. The xlsx files do all look the same, they just differ in the single values of the cells. Therefor I used the Import Tool of Matlab to generate a code for the import of a single xlsx file. Now I would like to edit this code for my purposes:

%%Import data from spreadsheet
[Filename, Path] = uigetfile({'*.xlsx','DEWE Files (*.xlsx)'},'MultiSelect', 'on','C:\...');
Directory=[Path Filename];
[~, ~, raw] = xlsread(Directory,'Tabelle1');
raw(cellfun(@(x) ~isempty(x) && isnumeric(x) && isnan(x),raw)) = {''};

%%Replace non-numeric cells with NaN
R = cellfun(@(x) ~isnumeric(x) && ~islogical(x),raw); % Find non-numeric cells
raw(R) = {NaN}; % Replace non-numeric cells

%%Create output variable
data = reshape([raw{:}],size(raw));

%%Allocate imported array to column variable names
Data_1 = data(:,1);
Data_2 = data(:,2);
Data_3 = data(:,3);
Data_4 = data(:,4);

%%Clear temporary variables
clearvars data raw R;

I added the line with the uigetfile because I want to use multiselect.

Now my idea was, to use a for loop to make the import code run through all my selected xlsx files. So that I get Data_1_File_1, Data_1_File_2, Data_1_File_3,... in the end. The problem is, the xlsread does only accept char in its syntax and as soon as I load more than one file, I get errors. Maybe someone can help me with the for loop?

I hope it is clear what I want to do and I am eager to hear any advice!

[Christian]

Basically, what I want to do is, write a for-loop, which gives me the chars Directory_1, Directory_2, Directory_3,...

So I can use them in the xlsxread.

Answer 1

[Jan]

See: FAQ: process-a-sequence-of-files

Either create the file names automatically:

Folder = cd;   % Set accordingly
for k = 1:10
  FileName = sprintf('Data_1_File_%d.xlsx', k);
  [~, ~, raw] = xlsread(fullfile(Folder, FileName), 'Tabelle1');  
  ...
end

Or use all files inside the folder:

Folder   = cd;   % Set accordingly
FileList = dir(fullfile(Folder, '*.xlsx'));
for k = 1:numel(FileList)
   FileName = FileList(k).name;
   [~, ~, raw] = xlsread(fullfile(Folder, FileName), 'Tabelle1');
   ...
end

It works with a list obtained by uigetfile also:

[Filename, Path] = uigetfile({'*.xlsx','DEWE Files(*.xlsx)'}, ...
                             'MultiSelect', 'on','C:\...');
for k = 1:numel(Filename)
  File = fullfile(Path, Filename{k});
  [~, ~, raw] = xlsread(File, 'Tabelle1');
  ...
end

By the way: Reading all FAQ is strongly recommended: http://matlab.wikia.com/wiki/FAQ .

[Christian]

Hey Jan, thank you for your respond!

Maybe I do not get the Point, but if I use this code:

[Filename, Path] = uigetfile({'*.xlsx','DEWE Files(*.xlsx)'}, ...
                             'MultiSelect', 'on','C:\...');
for k = 1:numel(Filename)
  File = fullfile(Path, Filename{k});
  [~, ~, raw] = xlsread(File, 'Tabelle1');
  ...
end

The xlsread does only read/save the File with the Filename{k=numel(Filename)}. But I want all selected files to be in the Workspace. Any suggestions on that?

[Christian]

it works, when I use curly braces! After the loop I can address the data by Angle(1) or Angle(2).

Angle{k} = data(:,1);

Thank you!