Preallocation not helping with speed; actxserver

Question

Robert Dylans 2015-9-1

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/238476-preallocation-not-helping-with-speed-actxserver

评论： Robert Dylans 2015-9-2

I have a folder of 500 separate Excel files (.csv to be specific). I'm reading a specific data range from them. My main problem is that it's taking longer than I would expect. Originally with xlsread I was taking ~200 seconds. I've recently learned of using actxserver and have cut down to ~50 seconds. I'm trying to preallocate memory for it, but I've noticed that the duration is exactly the same whether I do this or not.

z=zeros(3452,1);
for i=1:500
    high{i}=z;
end
tic
folder='F:\Program Files\Historical\daily';
files=dir(fullfile(folder,'*.csv'));
e=actxserver('Excel.Application');
for k=1:numel(files)
    waitbar(k/numel(files),progress);
    ExcelWorkbook=e.workbooks.Open(fullfile(folder,files(k).name));
    range=ExcelWorkbook.ActiveSheet.Range('E1:E3452');
    high{k}=range.Value;
end
toc

Am I formatting the preallocation wrong? Or am I missing something else? Or is my computer just slow?

Thanks for any help.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Image Analyst 2015-9-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/238476-preallocation-not-helping-with-speed-actxserver#answer_190919

They're simple csv files, not complicated workbooks. Have you tried simply using csvread()? Maybe that will be faster since there's no overhead of trying to launch and shutdown Excel.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Robert Dylans 2015-9-2

在 MATLAB Online 中打开

Yes... thank you. Didn't realize what a significant different there was. Cut down from 50 seconds to 4 seconds.

Edited code, in case anybody finds this in a future search...

Had to edit a bit since csvread handles different length files differently than xlsread. Each file had a different number of rows. In this case I wanted them aligned/oriented with the last data point being the last row in each column.

tic
folder='F:\Program Files\Historical\daily';
files=dir(fullfile(folder,'*.csv'));
datapoints=3926;  %Did a previous scan to find longest file
numfiles=numel(files);
high=zeros(datapoints,numfiles);
for k=1:numfiles
    data=csvread(fullfile(folder,files(k).name));
    high((datapoints-length(data)+1):datapoints,k)=data(:,1);  %import column 1 only
end
toc