Clear Filters
Clear Filters

xlsread 'basic' mode reading some text values as NaN

2 views (last 30 days)
I am using xlsread to important several tables of data from several xls and xlsx spreadsheets. These spreadsheets hold the master data from our groundwater monitoring well programme, and are not mine to edit.
The tables are mixtures of text, numbers, and NaNs. It was working fine, then out of the blue, Excel started throwing an error and wouldn't let me start the Excel COM server. I have tried reinstalling and updating Excel (Office 2010 Professional on Windows 7) but no luck.
In trying to use xlsread 'basic' mode as a workaround, I discovered that some of the text values in my spreadsheet were being read as NaN. Explicitly formatting them as Text in the spreadsheet didn't make any difference. In the following excerpt for example, the "Z (Elevation at Ground Surface)", "Borehole M" and "Borehole N" cells are returned as Nan, everything else is returned correctly. Any ideas?
Name X (NZTM) Y (NZTM) Z (Elevation at Ground Surface) Identifier Well Screen Length [m]
Borehole A 1843742.53 5722270.47 527.47 Borehole NaN
Borehole B 1843792.81 5722324.00 529.71 Borehole NaN
Borehole C 1843758.12 5722297.37 528.46 Borehole NaN
Borehole D 1843767.40 5722257.22 524.40 Borehole NaN
Borehole E 1843773.89 5722254.63 523.53 Borehole NaN
Borehole F 1843770.67 5722256.15 524.39 Borehole NaN
Borehole I 1843763.64 5722242.17 523.19 Borehole NaN
Borehole J 1843785.72 5722250.37 522.80 Borehole NaN
Borehole K 1843780.81 5722261.46 523.81 Borehole NaN
Borehole L 1843775.05 5722283.06 526.41 Borehole NaN
Borehole M 1843794.13 5722266.16 523.64 Borehole NaN
Borehole N 1843784.60 5722253.39 523.09 Borehole NaN
Borehole WR28 1843746.36 5722295.71 529.80 Borehole NaN
Bridge 1843906.77 5721935.06 NaN Bridge NaN
  5 Comments
Simon Woodward
Simon Woodward on 23 Sep 2014
Matlab fails at this line in actxserver.m:
h=feval(['COM.' convertedProgID], 'server', machinename, interface);

Sign in to comment.

Accepted Answer

the cyclist
the cyclist on 23 Sep 2014
You could try saving the Excel file as a CSV, then using csvread.
Also, if you have a relatively modern version of MATLAB, you could use the Import Tool, which is relatively easy to use interactively. It can then generate the code it used for import.
  10 Comments
Simon Woodward
Simon Woodward on 24 Sep 2014
wellnames = temp{1}; % cell array
welldata = horzcat([temp{2:end}]); % cell array
welldata = str2double(welldata); % matrix
Simon Woodward
Simon Woodward on 24 Sep 2014
Just a note to future readers. The bug is in Excel/Office, I think, this is just a workaround.

Sign in to comment.

More Answers (2)

Ken Atwell
Ken Atwell on 24 Sep 2014
Edited: Ken Atwell on 24 Sep 2014
Like cyclist, I was able to load this file and first bringing it into Excel and saving it in the newer XLSX format (XLS has fallen into disfavor since about a decade ago). On Windows, you would want a slightly different command line than Cyclist used on his Mac. Try:
>> [n,t,r]=xlsread('Taupo GIS Data (Incl. Coring and Well Info).xlsx', 'Data', 'A:Z', 'basic')
  • 'n' contains numeric values
  • 't' contains text values
  • 'r' is a raw mix of both numeric and text values.
'r' is 147x16, which looks right.
Good luck.
  1 Comment
Simon Woodward
Simon Woodward on 24 Sep 2014
Edited: Simon Woodward on 24 Sep 2014
When I update to *.xlsx, I get a new error:
Warning: XLSREAD has limited import
functionality on in basic mode. Refer to
HELP XLSREAD for more information.
> In xlsread at 169
In VistasData12 at 83
Warning: Range cannot be used in 'basic'
mode. The entire sheet will be loaded.
> In xlsread at 184
In VistasData12 at 83
??? Error using ==> xlsread at 234
File contains unexpected record length.
Try saving as Excel 98.
Error in ==> VistasData12 at 83
[num, txt, raw] = xlsread('L:\GIS
Data\Components\Taupo\Taupo GIS Data
(Incl. Coring and Well
Info).xlsx','Data','A2:P147','basic');

Sign in to comment.


Simon Woodward
Simon Woodward on 21 Jul 2015
Edited: Simon Woodward on 21 Jul 2015
Using xlsread basic on the xls file, it is now reading in the data in cells which contain values, but not in those cells which contain formulas.

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!