xlsread 'basic' mode reading some text values as NaN

3 次查看（过去 30 天）
Simon Woodward
Simon Woodward 2014-9-23
评论： Walter Roberson 2015-7-21
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 个评论
显示 3更早的评论
Simon Woodward
Simon Woodward 2014-9-23
Matlab fails at this line in actxserver.m:
h=feval(['COM.' convertedProgID], 'server', machinename, interface);

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

the cyclist
the cyclist 2014-9-23
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 个评论
显示 8更早的评论
Simon Woodward
Simon Woodward 2014-9-24
wellnames = temp{1}; % cell array
welldata = horzcat([temp{2:end}]); % cell array
welldata = str2double(welldata); % matrix
Simon Woodward
Simon Woodward 2014-9-24
Just a note to future readers. The bug is in Excel/Office, I think, this is just a workaround.

请先登录，再进行评论。

更多回答（2 个）

Ken Atwell
Ken Atwell 2014-9-24
编辑：Ken Atwell 2014-9-24
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 个评论
Simon Woodward
Simon Woodward 2014-9-24
编辑：Simon Woodward 2014-9-24
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');

请先登录，再进行评论。

Simon Woodward
Simon Woodward 2015-7-21
编辑：Simon Woodward 2015-7-21
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.

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by