CODEWITHSUNDEEP
matlabfor-loopxlsread
R Thompson
R Thompson

Reputation: 353

How can I increase the speed of this xlsread for loop?

I have made a script which contains a for loop selecting columns from 533 different excel files and places them into matrices so that they can be compared, however the process is taking too long (it ran for 3 hours yesterday and wasn't even halfway through!!).

I know xlsread is naturally slow, but does anyone know how I can make my script run faster? The script is below, thanks!!

%Split the data into g's and h's
CRNum = 533; %Number of Carrington Rotation files
A(:,1) = xlsread('CR1643.xlsx','A:A'); % Set harmonic coefficient columns
A(:,2) = xlsread('CR1643.xlsx','B:B');
B(:,1) = xlsread('CR1643.xlsx','A:A');
B(:,2) = xlsread('CR1643.xlsx','B:B');

for k = 1:CRNum
    textFileName = ['CR' num2str(k+1642) '.xlsx'];
A(:,k+2) = xlsread(textFileName,'C:C'); %for g
B(:,k+2) = xlsread(textFileName,'D:D'); %for h
end

Upvotes: 2

Views: 1055

Answers (3)

neurd
neurd

Reputation: 3

As mentioned in this post, the easiest thing to change would be to set 'Basic' to true. This disables things like formulas and macros in Excel and allows you to read a simple table more quickly. For example, you can use:

xlsread('CR1643.xlsx','A:A', 'Basic', true)

This resulted in a decrease in load time from about 22 seconds to about 1 second for me when I tested it on a 11,000 by 7 Excel sheet.

Upvotes: 0

eulerleibniz
eulerleibniz

Reputation: 291

Don't use xlsread if you want to go through a loop. because it opens excel and then closes excel server each time you call it, which is time consuming. instead before the loop use actxserver to open excel, do what you want and finally close actxserver after your loop. For a good example of using actxserver, search for "Read Spreadsheet Data Using Excel as Automation Server" in MATLAB help.

And also take a look at readtable which works faster than xlsread, but generates a table instead.

Upvotes: 2

Dennis Jaheruddin
Dennis Jaheruddin

Reputation: 21561

The most obvious improvement seems to be to load the files only partially if possible. However, if that is not an option, try whether it helps to only open each file once (read everything you need, and then assign it).

M(:,k+2) = xlsread(textFileName,'C:D');

Also check how much you are reading in each time, if you read in many rows in the first file, you may make the first dimension of A big, and then you will fill it each time you read a file?

As an extra: a small but simple improvment can be found at the start. Don't use 4 load statements, but use 1 and then assign variables based on the result.

Upvotes: 1

Related Questions