actxserver cannot read all of the data

The following loop reads data from excel into matlab with the use of actxserver. The loop wont complete due to an error which states that the data from excel is too great. I find this hard to believe as there are 75 excel sheets each with approximately 14000 rows and 21 columns so the dataset isn't extremely massive. Therefore, I'm not sure if its the loop which is causing the problem or the dataset is too big.

The script is as follows:

clear all
%obtain the name of each of the folders under investigation
path='F:\University\CEH Lancaster\Project\Practice'; 
folder = path; 
dirListing = dir(folder); 
dirListing=dirListing(3:end);%first 2 are just pointers

for i=1:length(dirListing);
    Folder_Name{i}=dirListing(i,1).name;
    f{i} = fullfile(path, dirListing(i,1).name);%obtain the name of each folder
    files{i}=dir(fullfile(f{i},'*.xls'));%find the .xls files
    for j=1:length(files{1,i});
        File_Name{1,i}{j,1}=files{1,i}(j,1).name;%find the name of each .xls file in each folder
    end
end

%manually obtain the name of the required worksheets
Name_workbook={'Data1', 'Data2', 'Data3', 'Data4'};

%read data in from excel
excel = actxserver('Excel.Application');
excel.Visible=0;
for i=1:length(File_Name);
    a(i)=length(File_Name{1,i});
    for j=1:a(i);
        file{1,i}{j,1}=excel.Workbooks.Open(fullfile(path,Folder_Name{1,i},File_Name{1,i}{j,1}));
        sheet1{1,i}{j,1}=excel.Worksheets.get('Item', Name_workbook(1,i));
        MyRange{1,i}{j,1}=sheet1{1,i}{j,1}.UsedRange; 
        MyData{1,i}{j,1}=MyRange{1,i}{j,1}.Value;        
    end
end

Does anyone have an opinion on what I should try next?

Answer 1

[Fangjun Jiang]

I think the problem might be that you don't have proper pre-allocation. I can understand your code. You need to plan ahead your coding flow, use variable names more effectively to help improve the readability of the code. I almost lost track of all your similar variable names such as path, folder, dirListing, Folder_Name, f, files, File_Name.

Don't use "path" as the variable name as it is a function.

You need one variable, e.g. TopFolder to specify the top folder name.

You can have a variable, e.g. SubFolder which is a structure array to store all the sub-folder name from dir()

Then you can have a variable called ExcelFile which could be structure array or cell array to store all the Excel file names. By that time, you coudl clear the SubFolder variable.

Once you have the number of Excel files, you can pre-allocate a cell array to store the data you are going to read.

Inside the loop, the variable for workbook, sheet, range etc. can all be re-used.

The size of the data is large, I am not sure if you mean 75 Excel files, or 15 files with 5 sheets in each file.

75*14000*21*8 equals 176M bytes, or 900M if 5 times over.

[Masoud Ghanbari]

Hi

Would You Please Tell Me What Is The Function Of Using COM Here???