is there instead solution of xlsread()?

its needed to convert .xls files to .mat but speed of reading by xlsread is too low. is there instead solution of xlsread()? (file exchange or other way to convert .xls to .mat)

UPDATED:

Folder=cd(directory);
 d = dir('*.xls');
N_File=numel(d);
e = actxserver ('Excel.Application');
        h=waitbar(0,'Progress...');

       for o = 1:N_File
          qs=numel(d)-o;
          clc;
      fprintf('Please wait %d second ',qs)
       fprintf(1,repmat('\n',1,1));
      waitbar(o/N_File,h);
      %     r = xlsread(d(o).name,2);
      cd(directory);

      ExcelWorkbook = e.workbooks.Open(fullfile(Folder,d(o).name));

      Sheet=ExcelWorkbook.Sheets.Item(2);
      Range=Sheet.UsedRange;
      r=cell2mat(Range.Value);
      ExcelWorkbook.Close;

      d=sprintf('r%d',o);
      assignin('base',d,r);

       end
      close all; 
       e.Quit;
      e.delete;

[Fangjun Jiang]

Use the code below, I didn't see where you save the .mat file.

%%

Folder=cd(directory);

d = dir('*.xls');

N_File=numl(d);

e = actxserver ('Excel.Application');

h=waitbar(0,'Progress...');

for o = 1:N_File

waitbar(o/N_File,h);

ExcelWorkbook = e.workbooks.Open(fullfile(Folder,d(o).name));

Sheet=ExcelWorkbook.Sheets.Item(2);

Range=Sheet.UsedRange;

r=cell2mat(Range.Value);

ExcelWorkbook.Close;

end

e.Quit;

e.delete;

[mohammad]

realy nice, i save it in workspace by this command: d=sprintf('r%d',o);

assignin('base',d,r);

that while using this, errors:

??? Improper index matrix reference.

Error in ==> chechspeed at 34

ExcelWorkbook = e.workbooks.Open(fullfile(Folder,d(o).name));

[mohammad]

wao perfect now it takes 30 seconds (170 seconds saving)

there is no errors

you are really professional in MATLAB

thanks a lot

Answer 1

[Fangjun Jiang]

Try the COM server running Microsoft Excel.

http://www.mathworks.com/help/techdoc/ref/actxserver.html

[Jan]

XLSREAD use the COM interface to Excel also, except for the 'basic' mode.

[Fangjun Jiang]

Yes. If you look at the xlsread.m, you'll find lots of overhead. If speed is critical and the data in the spreadsheet is uniformly numerical, I think use COM interface will be faster. Plus, it's not that much complicated than using xlsread().

[mohammad]

i used these but xls file not convert to mat:

e = actxserver ('Excel.Application')

ExcelWorkbook = e.workbooks.Open('H:\N.xls',0,true)

ExcelWorkbook =

Interface.Microsoft_Excel_12.0_Object_Library._Workbook

set(e,'Visible',true);

[Fangjun Jiang]

Use the following, then you can save data to a .mat file.

%%

e = actxserver ('Excel.Application');

ExcelWorkbook = e.workbooks.Open(fullfile(pwd,'test.xls'));

set(e,'Visible',true);

Sheet=ExcelWorkbook.Sheets.Item(1);

Range=Sheet.UsedRange;

Data=Range.Value;

e.Quit;

e.delete;

[mohammad]

so nice

really so nice, only 'Data' is a cell but i need being a matrix

[mohammad]

Fangjun could it be saved in matrix form or it must be converted to matrix after saving in a cell?

if second case is true, how could it be converted?

[Fangjun Jiang]

Then, use Data=cell2mat(Range.Value);

[mohammad]

This saves only 26s for reading 300 files

[Fangjun Jiang]

You mean 26 seconds? 26 seconds out of how long? There might be other things you can speed up. Post your code, I think Jan Simon is the expert on this.

[mohammad]

there are 300 xls files

by xlsread it takes: 2.004992629931244e+02 seconds

by above code it takes: 2.264987549e+02 seconds

( while testing I was closed all other programs and also done nothing else)

I put code in main question.

Thanks a lot for helping Fangjun

[Fangjun Jiang]

For example, don't put the line "e = actxserver ('Excel.Application')" inside the for-loop. Instead, put it outside of the for-loop. Inside the loop, just open the file, read, then close the file. Definitely it is going to be much faster than using xlsread() because every time xlsread() is called, a COM server is created and then deleted.

[mohammad]

when i put outside the for loop errors this:

??? No appropriate method, property, or field workbooks for class handle.handle.

Error in ==> chechspeed at 34

ExcelWorkbook = e.workbooks.Open(fullfile(pwd,dfile));

Answer 2

[Jan]

Do you speak of binary Excel files or tab separated ASCII files? While the binary files are imported using an Excel-function, reading ASCII-files can be done faster using a C-Mex. To my surprise FSCANF('%f') of MSVC2008 is slower than a hand-coded C-method to import numbers from ASCII files.

But an implementation of all features of READXLS will consume a lot of time. It will not be a benefit to spend a week for programming to accelerate the data import by some hours only. I have only the core function at the momemt to read from other ASCII files.

Do you import the file from a network drive? Then the speed is most likely limited by the network communication and an acceleration of the reading function would not help very much.

[mohammad]

Thanks

Jan these excel files has 2 columns and 10000 rows and arrays are numbers. i need converting these xls to mat and being on workspace.

please explain more simple

[Jan]

If the XLS file has ASCII format, you can read it when you open it in an editor. Then there are faster reading methods.

If the XLS file is binary, the reading is done through a COM method in Excel. If you read the source ("edit readxls"), you find the same method as posted by Fangjun. Therefore I assume that this cannot be accelerated.

On the otherhand: 2 columns and 10'000 lines? This is not a larghe file. I'm surprised that the reading is slow. Is the file stored on the local harddrive or in a network?

[mohammad]

Jan size is not large but 65000 times .xls files must be read. there are in hard drive

when i try opening with editor,'import wizard' coming up

[Jan]

Please use an external editor, e.g. WordPad. If it shows rubbish, the file is in binary format.

[mohammad]

OK Jan, I opened by WordPad and it shows rubbish!

so there is no way for more speed?