actxserver cannot read all of the data

1 view (last 30 days)
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?

Accepted Answer

Fangjun Jiang
Fangjun Jiang on 6 Dec 2011
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.
  1 Comment
Masoud Ghanbari
Masoud Ghanbari on 21 Jun 2013
Hi
Would You Please Tell Me What Is The Function Of Using COM Here???

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!