xlsread returning empty only sometimes

I am having trouble with xlsread actually returning values. This code is in a for loop with i as the iterating variable.

sheet = strcat(num2str(i), ' in');
file = 'data.xlsx';
times_data = xlsread(file,sheet,'A3:A402');
Data1 = xlsread(file,sheet,'B3:B402');
Data2 = xlsread(file,sheet,'F3:F402');
Data3 = xlsread(file,sheet,'J3:J402');
Data4 = xlsread(file,sheet,'N3:N402');
Data5 = xlsread(file,sheet,'R3:R402');

In the code above only Data5 has anything stored in it. In the "Workspace" tab everything else is show as empty( with a [] symbol). Does anyone know why this may be and a way to fix it?

[Walter Roberson]

What is your value for i ?

My testing with your file on Mac does not show anything unusual for the sheets that are present.

[Mathieu Lebiere]

i goes between 0.5 and 4 in increments of 0.5. It is part of a for loop. I did not get any errors when running the code either on windows.

Answer 1

[Cris LaPierre]

Any chance you can attach one of your spreadsheets? Much easier to debug that way.

Without seeing that, I'd have two guesses:

1. Data1-4 and times_data are empty because there is no data in the specified range
2. The data entered in those ranges is not numeric. In this case, you want to use the [num,txt,raw] = xlsread(___) syntax. You can read more in the xlsread documentation page.

[Cris LaPierre]

My recommendation would be to use readtable to import the spreadsheet. That imports each column as a separate variable, and supports mixed data types.

sheet = strcat(num2str(i), ' in');
file = 'data.xlsx';

data = readtable(file,'Sheet',sheet);

Once the data is imported, you can continue processing it. It's easier once it's already in MATLAB.

You can read more about how to use readtable to import a spreadsheet here.

[Walter Roberson]

In particular, in the num (first) output of xlsread(), the output is trimmed. Rows and columns are examined to determine whether there are any translatable numbers in them, and any leading or trailing row that is untranslatable or all-nan is removed.

This has the effect of removing leading and trailing rows and columns that are empty or all text. But especially in the case where you are only reading one column, if the column happens to be all nan or all empty, instead of getting a nan column the size of the input, all of those entries will be removed, potentially leaving you with emptiness.

[Mathieu Lebiere]

I have uploaded a copy of the data. Everything that should be a number appears to be a number to me. All of the columns have data in them that are numbers but the first row is populated with text as labels. Please let me know if this helps. In the mean time I will look into readtable. Thank you!

[Cris LaPierre]

It appears to be #2. If I read the file using the '[num,txt,raw] = xlsread(___)' syntax, the values appear in the txt and raw variables but not num. When I use the import tool, it defaults to text on those columns as well. In fact, only the last 4 columns in sheet '0.5 in' are recognized as numbers.

If I change the format in excel to Number, it does read in the values. However, that may be a bit cumbersome depending on the number of files/sheets to process.

This is where I again recommend readtable. It is also much quicker because you only have to read the file once instead of 6 times for each sheet.

file = 'data.xlsx'; 
opts = detectImportOptions(file);
opts.SelectedVariableNames = [1 2 6 10 14 18];
opts = setvartype(opts,[1 2 6 10 14 18],'double');
opts.VariableNames([1 2 6 10 14 18]) = {'time','Data1','Data2','Data3','Data4','Data5'};
opts.DataRange = '3:402';
[~,sheets] = xlsfinfo(file);

for n = 1:length(sheets)
    data{n} = readtable(file,opts,'Sheet',sheets{n});
end

[Mathieu Lebiere]

Much appreciated!

[Walter Roberson]

On Mac the values were read properly . That suggests that adding the option 'basic' might have worked on Windows .