Add all values from loop to an array

I am trying to read all xls files from a folder and display information from certain cells of each file. I want to put all of these values into a single array.

%% Problem 1

fds = fileDatastore('*.xls', 'ReadFcn', @importdata); %I believe that how this function is set up only data on matlab online will be read, function will have to be altered for downloaded matlab.

fullFileNames = fds.Files;

numFiles = length(fullFileNames);

for k = 1 : numFiles

fprintf('Now reading file %s\n', fullFileNames{k});

[status,sheets] = xlsfinfo(fullFileNames{k});

sheet = string(sheets);

S = length(sheets);

i=1;

for x = i:S

p = sheet(1,i);

[~,~,dat]=xlsread(fullFileNames{k},p);

dat=[dat(3,4) dat(3,10) dat(4,4) dat(4,11)];

i= i+1;

end

end

[Cris LaPierre]

There is nothing about your code that is unique to MATLAB Online. You just need to be sure the first input to filedatastore is pointing to your intended folder. Currently, it will load all xls files in your current folder.

Answer 1

[Jon]

Let's say you want to store the values you have selected from each sheet in an array called outDat, you could modify your code to do something like

 S = length(sheets);
 outDat = zeros(4,S); % preallocate array to hold output data
 i=1;
 for x = i:S
     p = sheet(1,i);
     [~,~,dat]=xlsread(fullFileNames{k},p);
      % store the values for this sheet in the output array 
      outDat(i,:) =[dat(3,4) dat(3,10) dat(4,4) dat(4,11)]; 
 
   i= i+1;
    
 end
end

[Kyle]

When I try to run this it says

"Conversion to double from cell is not possible.

Error (line 27)

outDat(i,:) =[dat(3,4) dat(3,10) dat(4,4) dat(4,11)]; "

[Jon]

The problem is you are using the third argument of xlsread, which returns a cell array. If all of your data is numeric you can just get the data returned in the first argument to xlsread, which is a numeric (double) array.

Otherwise you need to convert the data from cell to matrix before putting it into the output array.

xlsread is now outdated, you can probably use readmatrix instead.

Please attach one of your excel files so I can try too and give you further advice

Answer 2

[Cris LaPierre]

I would use a filedatastore to load all the data into a single variable. You can see an example of how to use one to do this in this video from the Data Processing with MATLAB specialization on Coursera.

Here is the final code from that example. You can modify this to work for your data.

flightsDataStore = fileDatastore("flights*.csv","ReadFcn",@importFlightsData,"UniformRead",true);
flightsAll = readall(flightsDataStore)

Once complete, all the data from all files matching the pattern "flights*.csv" are loaded into the variable flightsAll.

[Cris LaPierre]

Your code does not take advantage of your fileDataStore. Modifying your code, I would think you could do something like this (note this is untested since you have not shared any files for us to test with)

fds = fileDatastore('*.xls', "ReadFcn",@importMyData,"UniformRead",true);
DA = readall(fds)

function data = importMyData(filename)
sheets = sheetnames(filename);
data = zeros(length(sheets),4);
for s = 1:length(sheets)
    dat = readtable(filename,'Sheet',s,'NumHeaderLines',0);
    data(s,:) = [dat{3,[4,10]} dat{4,[4,11]}];   
end
end

Answer 3

[Kyle]

% This is a solution but I do not think it is the most efficent

fds = fileDatastore('*.xls', 'ReadFcn', @importdata);

fullFileNames = fds.Files;

numFiles = length(fullFileNames);

o = 1;

DA = zeros(10000,4);

DA2 = num2cell(DA);

for k = 1 : numFiles

fprintf('Now reading file %s\n', fullFileNames{k});

[status,sheets] = xlsfinfo(fullFileNames{k});

sheet = string(sheets);

S = length(sheets);

i=1;

for x = i:S

p = sheet(1,i);

[~,~,dat]=xlsread(fullFileNames{k},p);

dat=[dat(3,4) dat(3,10) dat(4,4) dat(4,11)];

i= i+1;

DA2(o,:) = dat;

o = o +1;

end

o = o +1;

end