Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Data Import from Excel

Subject: Data Import from Excel

From: Safa

Date: 31 Mar, 2011 17:02:05

Message: 1 of 7

I have an excel spreadsheet with several worksheets. I would like to import data from each worksheet into matlab as a separate matrix. I know there are three ways to do this...import wizard, importdata function and xlsread function. I've tried each of these with various results. import wizard gives me error messages as there are NaN in some columns. importdata, works, however it saves the data under several layers or structures, and if I attempt to save the matrices in the sublayers, I get error messages. I think xlsread works best, but I can only do it manually i.e. A = xlsread('testfile.xlsx',1, 'B2:B100'); B=xlsread('testfile.xlsx',2, 'B2:B100') etc. How can I loop this? Appreciate some hints in solving this. Many thanks in advance, Safa.

Subject: Data Import from Excel

From: sscnekro

Date: 31 Mar, 2011 20:59:05

Message: 2 of 7

"Safa" wrote in message <in2c2d$1as$1@fred.mathworks.com>...
> A = xlsread('testfile.xlsx',1, 'B2:B100'); B=xlsread('testfile.xlsx',2, 'B2:B100') etc. How can I loop this?

Hey there, I'm not sure what exactly do you need, but if you always work with the same excel file name and the same range, (so your resulting matrices are of the same dimension), try something like:
for ii = 1:n; A(:,:,ii) = xlsread('testfile.xlsx',ii, 'B2:B100'); end;
This should give you a 3D array A with n pages, each page being one of your matrices. Hope this helps

Subject: Data Import from Excel

From: Safa

Date: 31 Mar, 2011 21:04:04

Message: 3 of 7

"Safa" wrote in message <in2c2d$1as$1@fred.mathworks.com>...
> I have an excel spreadsheet with several worksheets. I would like to import data from each worksheet into matlab as a separate matrix. I know there are three ways to do this...import wizard, importdata function and xlsread function. I've tried each of these with various results. import wizard gives me error messages as there are NaN in some columns. importdata, works, however it saves the data under several layers or structures, and if I attempt to save the matrices in the sublayers, I get error messages. I think xlsread works best, but I can only do it manually i.e. A = xlsread('testfile.xlsx',1, 'B2:B100'); B=xlsread('testfile.xlsx',2, 'B2:B100') etc. How can I loop this? Appreciate some hints in solving this. Many thanks in advance, Safa.

After spending half a day on this, I searched through old messages, and found a helpful code posted by Leslie McBrayer. Thank you Leslie!

http://www.mathworks.com/matlabcentral/newsreader/view_thread/263604#688389

This code works. Hope it helps someone.
[type,sheetname] = xlsfinfo('test.xlsx');
m=size(sheetname,2);
alldata =cell(1, m);
for i=(1:1:m);
Sheet = char(sheetname(1,i)) ;
alldata{i} = xlsread('test.xlsx', Sheet, 'B3:B100');
data_matrix=cell2mat(alldata);
end

Subject: Data Import from Excel

From: Safa

Date: 31 Mar, 2011 21:48:04

Message: 4 of 7

"Safa" wrote in message <in2q84$2ur$1@fred.mathworks.com>...
> "Safa" wrote in message <in2c2d$1as$1@fred.mathworks.com>...
> > I have an excel spreadsheet with several worksheets. I would like to import data from each worksheet into matlab as a separate matrix. I know there are three ways to do this...import wizard, importdata function and xlsread function. I've tried each of these with various results. import wizard gives me error messages as there are NaN in some columns. importdata, works, however it saves the data under several layers or structures, and if I attempt to save the matrices in the sublayers, I get error messages. I think xlsread works best, but I can only do it manually i.e. A = xlsread('testfile.xlsx',1, 'B2:B100'); B=xlsread('testfile.xlsx',2, 'B2:B100') etc. How can I loop this? Appreciate some hints in solving this. Many thanks in advance, Safa.
>
> After spending half a day on this, I searched through old messages, and found a helpful code posted by Leslie McBrayer. Thank you Leslie!
>
> http://www.mathworks.com/matlabcentral/newsreader/view_thread/263604#688389
>
> This code works. Hope it helps someone.
> [type,sheetname] = xlsfinfo('test.xlsx');
> m=size(sheetname,2);
> alldata =cell(1, m);
> for i=(1:1:m);
> Sheet = char(sheetname(1,i)) ;
> alldata{i} = xlsread('test.xlsx', Sheet, 'B3:B100');
> data_matrix=cell2mat(alldata);
> end

Hey, thanks for your neat code. It is even better than the one I found. How do you convert a 3D array, to a 2D matrix? Appreciate your help.

Subject: Data Import from Excel

From: Safa

Date: 31 Mar, 2011 22:08:04

Message: 5 of 7

This seems to work:
for ii = 1:2;
    A(:,ii) = xlsread('test.xlsx',ii, 'B3:B100');
end

Thanks sscnekro for your help.

Subject: Data Import from Excel

From: Alex Harrison

Date: 11 Apr, 2011 13:08:05

Message: 6 of 7

"Safa" wrote in message <in2q84$2ur$1@fred.mathworks.com>...
> "Safa" wrote in message <in2c2d$1as$1@fred.mathworks.com>...
> > I have an excel spreadsheet with several worksheets. I would like to import data from each worksheet into matlab as a separate matrix. I know there are three ways to do this...import wizard, importdata function and xlsread function. I've tried each of these with various results. import wizard gives me error messages as there are NaN in some columns. importdata, works, however it saves the data under several layers or structures, and if I attempt to save the matrices in the sublayers, I get error messages. I think xlsread works best, but I can only do it manually i.e. A = xlsread('testfile.xlsx',1, 'B2:B100'); B=xlsread('testfile.xlsx',2, 'B2:B100') etc. How can I loop this? Appreciate some hints in solving this. Many thanks in advance, Safa.
>
> After spending half a day on this, I searched through old messages, and found a helpful code posted by Leslie McBrayer. Thank you Leslie!
>
> http://www.mathworks.com/matlabcentral/newsreader/view_thread/263604#688389
>
> This code works. Hope it helps someone.
> [type,sheetname] = xlsfinfo('test.xlsx');
> m=size(sheetname,2);
> alldata =cell(1, m);
> for i=(1:1:m);
> Sheet = char(sheetname(1,i)) ;
> alldata{i} = xlsread('test.xlsx', Sheet, 'B3:B100');
> data_matrix=cell2mat(alldata);
> end

Thanks for finding and posting this. I need to use it in my program but it keeps giving me the following error:

??? Attempt to execute SCRIPT xlsread as a function:

Error in ==> xlsread at 25
    alldata{i} = xlsread('fingerprint_scan.xls', Sheet,'Z26');

All I've done is change the file name and the cell/s to be read. Just wondered if you know what might be going wrong?

Thanks, Alex

Subject: Data Import from Excel

From: Alex Harrison

Date: 11 Apr, 2011 17:58:21

Message: 7 of 7

"Alex Harrison" <dingbatsgrowontrees@hotmail.co.uk> wrote in message <inuufl$e9d$1@fred.mathworks.com>...
> "Safa" wrote in message <in2q84$2ur$1@fred.mathworks.com>...
> > "Safa" wrote in message <in2c2d$1as$1@fred.mathworks.com>...
> > > I have an excel spreadsheet with several worksheets. I would like to import data from each worksheet into matlab as a separate matrix. I know there are three ways to do this...import wizard, importdata function and xlsread function. I've tried each of these with various results. import wizard gives me error messages as there are NaN in some columns. importdata, works, however it saves the data under several layers or structures, and if I attempt to save the matrices in the sublayers, I get error messages. I think xlsread works best, but I can only do it manually i.e. A = xlsread('testfile.xlsx',1, 'B2:B100'); B=xlsread('testfile.xlsx',2, 'B2:B100') etc. How can I loop this? Appreciate some hints in solving this. Many thanks in advance, Safa.
> >
> > After spending half a day on this, I searched through old messages, and found a helpful code posted by Leslie McBrayer. Thank you Leslie!
> >
> > http://www.mathworks.com/matlabcentral/newsreader/view_thread/263604#688389
> >
> > This code works. Hope it helps someone.
> > [type,sheetname] = xlsfinfo('test.xlsx');
> > m=size(sheetname,2);
> > alldata =cell(1, m);
> > for i=(1:1:m);
> > Sheet = char(sheetname(1,i)) ;
> > alldata{i} = xlsread('test.xlsx', Sheet, 'B3:B100');
> > data_matrix=cell2mat(alldata);
> > end
>
> Thanks for finding and posting this. I need to use it in my program but it keeps giving me the following error:
>
> ??? Attempt to execute SCRIPT xlsread as a function:
>
> Error in ==> xlsread at 25
> alldata{i} = xlsread('fingerprint_scan.xls', Sheet,'Z26');
>
> All I've done is change the file name and the cell/s to be read. Just wondered if you know what might be going wrong?
>
> Thanks, Alex

Sorry ignore that it's sorted now :)

Tags for this Thread

No tags are associated with this thread.

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us