xlsread 'basic' mode reading some text values as NaN
12 views (last 30 days)
Show older comments
Commented: Walter Roberson on 21 Jul 2015
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
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.
More Answers (2)
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.
Simon Woodward on 21 Jul 2015
Edited: Simon Woodward on 21 Jul 2015
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!Start Hunting!