How to replace multiple xlsread commands

I have an older matlab code that reads multiple areas of a single excel sheet using multiple xls read commands. For example:

[NUM1 TXT1] = xlsread(myfile.xlsx,'Sheet1','A1:C10')
[NUM2,TXT2]= xlsread(myfile.xlsx,'Sheet1','Z200:AB500');

The data in the spreadsheet can be numerical or text, so having them split out like xlsread is handy for me (I use both the text and numerical data). I want to replace the multiple xls read commands with a single command to speed up the code, because the excel files are large and it takes a while to get all the data. I'm playing with a single readcell command and trying to get the data locations sorted out from the cell array but struggling with the multiple data types.

data=readcell(myfile.xlsx,'Sheet','Sheet1','Range','A1:AB500')
data1 = data(1:10,1:3)

Now data 1 is a cell array that contains A1:C10, but I need to get the numeric data and text data out of it in a way that replicates the [NUM1 TXT1] that I get from xlsread. Any ideas?

Answer 1

[Matt J]

data1 = {'dog',1,3,'cat'; 10 12 'fish' 17}

data1 = 2x4 cell array
    {'dog'}    {[ 1]}    {[   3]}    {'cat'}
    {[ 10]}    {[12]}    {'fish'}    {[ 17]}

idx=cellfun(@isnumeric,data1);

NUM1=nan(size(data1));
NUM1(idx)=cell2mat(data1(idx))

NUM1 = 2×4

NaN 1 3 NaN 10 12 NaN 17

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

TXT1=data1;
TXT1(idx)={''}

TXT1 = 2x4 cell array
    {'dog'   }    {0x0 char}    {0x0 char}    {'cat'   }
    {0x0 char}    {0x0 char}    {'fish'  }    {0x0 char}

[Matt J]

But isn't each column of each data block uniformly text or char? If so, I feel like two calls to readtable might be faster,

T1 = readtable('myfile.xlsx','Range','A1:C10')
T2 = readtable('myfile.xlsx','Range','Z200:AB500');

You can also experiment with reading the whole block, as you were planning to do with readcell.

data=readtable('myfile.xlsx','Range','A1:AB500')
data1 = data(1:10,1:3)

[ED]

This kind of works and is faster so far, except xlsread removes empty rows and columns of data and this doesnt. Any clean work around for that?

[Matt J]

Which proposal are we talking about? Are you using readtable?

[ED]

Using readcell. I did some testing and I went from about 1.5 minutes to get the data down to 30 seconds, so it's definitely faster to read it once and sort it out within MATLAB.

I found a work around to remove empty rows and columns so I'll post here for future:

NUM1(~any(~isnan(NUM1), 2),:)=[];%Remove empty rows
idx_last = find(sum(~isnan(NUM1),1) > 0, 1 , 'last');%find index of last column of data
NUM1(:,idx_last+1:end) = [];%remove unused columns

[Matt J]

I would do,

nanmap=isnan(NUM1);
I=all(nanmap,2);
J=all(nanmap,1);
NUM1(I,J)=[];