MATLAB Answers

When I import my excel sheet into excel the dimensions change

24 views (last 30 days)
Arielle Patapanian
Arielle Patapanian on 13 Jul 2020 at 10:34
Commented: Arielle Patapanian on 13 Jul 2020 at 13:11
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 :)

  0 Comments

Sign in to comment.

Accepted Answer

Walter Roberson
Walter Roberson on 13 Jul 2020 at 12:09
(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.

  5 Comments

Show 2 older comments
Arielle Patapanian
Arielle Patapanian on 13 Jul 2020 at 12:48
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
Walter Roberson on 13 Jul 2020 at 13:08
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) );

Sign in to comment.

More Answers (0)