MATLAB: Xlsread ‘basic’ mode reading some text values as NaN

excelimporting excel dataMATLABxlsread

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

Best Answer

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.
Related Question