When I import my excel sheet into excel the dimensions change

I have an excel sheet (attached) with 681 rows and 841 columns, but when I read it into excel using:

xlsread('Australia_actual_evap_1995.xlsx');

I get an array of the size: 663 x 816. This is very unusual and has not happened to me before, any ideas why this is happening?

Thanks :)

Answer 1

[Walter Roberson]

(when not passed a range to process) xlsread() starts out by reading all of the data in the worksheet into a cell array, call it raw. It runs str2double() on raw. This is stored in a temporary variable, call it NUM.The non-nan values in NUM are detected, and are used to replace the character values in the corresponding cell in raw. The raw cell will become the third output.

The locations in NUM that locations that are nan are detected, and the values in raw in those locations are copied into corresponding locations in a cell; this becomes the txt matrix (second output)

Now, numeric matrix NUM is examined, and the first and last rows and columns that are not all nan are detected. Everything in NUM that is inside that rectangle is copied to a numeric array; call it num. num will become the first output.

Your data contains several columns that consist entirely of nans. The ones at the beginning and the ones at the ends are being trimmed off; the rows at the end that are all nan are being trimmed off.

If you take the third output, the raw output, it will not have any columns removed, but you will need to convert it from cell to array.

[Arielle Patapanian]

Hi thank you this makes sense. I want to keep the NaN columns and rows since the rows and columns represent latitude and longitude.

I'm a little confused as to what I need to convert from cell to array. Is it the variable 'Australia_actual_evap_1995' that I need to convert to an array?

Australia_actual_evap_1995 = xlsread('Australia_actual_evap_1995.xlsx'); 

[Walter Roberson]

[~, ~, raw] = xlsread('Australia_actual_evap_1995.xlsx');
Australia_actual_evap_1995 = cell2mat(raw);

[Arielle Patapanian]

Thanks for the code! I seem to get this error when I input it:

Error using cell2mat (line 45)
All contents of the input cell array must be of the same data type.

Error in Untitled4 (line 2)
Australia_actual_evap_1995 = cell2mat(raw);

[Walter Roberson]

filename = 'Australia_actual_evap_1995.xlsx';
opt = detectImportOptions(filename, 'readvariablenames', false);
opt.DataRange = 'A1';
opt = setvartype(opt,'double');

Australia_actual_evap_1995 = table2array( readtable(filename, opt) );

[Arielle Patapanian]

Thank you so much!! That worked perfectly :D