xlsread 'basic' mode reading some text values as NaN

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

[Guillaume]

I would think that resolving the COM issue would be more productive than trying to half bake a workaround.

What is the error you're getting?

[the cyclist]

Could you upload the sample input file?

[Simon Woodward]

I have been unable to resolve the COM issue. Our IT department thinks it could be registry errors originating from a previous installation of MS Office.

Matlab says

Warning: Could not start Excel server for
import, 'basic' mode will be used. Refer to HELP
XLSREAD for more information. 
> In xlsread at 169
  In VistasData12 at 79
Warning: Range cannot be used in 'basic' mode.
The entire sheet will be loaded. 
> In xlsread at 184
  In VistasData12 at 79
Warning: XLS File contains unrecognized
string header - skipping remaining text -
(1027:0x52 1578).

[Simon Woodward]

File attached. I'm trying to read the "Data" worksheet.

[Simon Woodward]

Matlab fails at this line in actxserver.m:

          h=feval(['COM.' convertedProgID], 'server', machinename, interface);

Answer 1

[the cyclist]

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.

[Simon Woodward]

Thanks for your answer. csvread can only handle numeric data. I don't want to use the ImportTool, I want to do it programmatically, so that if the data source changes, I can quickly rerun the script. I guess I'll have to do it manually if all else fails though.

[the cyclist]

I meant that you could use the Import Tool interactively to find something that works, then use its code generation feature to create the code that worked for import. You can use that code programatically.

[Kelly Kearney]

A csv file with mixed text and numeric data can be pretty easily read via textscan. If you have the Statistics Toolbox, you could try reading via dataset; the xlsfile option for dataset doesn't rely on the COM server (I'm on a Mac and am almost never able to use xlsread reliably, but have no problems with dataset).

[Simon Woodward]

It looks like the Import Tool uses xlsread.

[the cyclist]

I did the following:

• Converted the file to *.xlsx format
• Ran the Import Tool
• Selected "cell array" as the input variable type

The following code was generated, and I seem to have successfully imported the worksheet, including values for the specific problematic cells you mentioned.

I am on a Mac, so this procedure did not use COM server.

%%Import data from spreadsheet

% Auto-generated by MATLAB on 2014/09/23 17:33:54

%%Import the data
[~, ~, TaupoGISDataIncl] = xlsread('Taupo GIS Data (Incl. Coring and Well Info).xlsx','Data');
TaupoGISDataIncl = TaupoGISDataIncl(2:end,2:end);
TaupoGISDataIncl(cellfun(@(x) ~isempty(x) && isnumeric(x) && isnan(x),TaupoGISDataIncl)) = {''};

[Simon Woodward]

I saved my table as a csv, and now trying to use textscan. I tried:

temp = textscan(fid,'%s %f %f %f %s %f %f %f %f %f %f %f %f %f %f %f','Delimiter',',');

but this read a single line of empty arrays. I tried a few other configurations. The documentation for this function is pretty bad. I am having trouble ignoring the header. When I read it all as strings it inserted at extra line after each line.

I'll attach the file.

[Simon Woodward]

Thank you, I'll give it a try.

Edit: No luck. Starting the Import Tool gave the same COM error that I was having before.

[Simon Woodward]

This worked. At least I got the data in correctly. Now I have to get it out of the cell array.

fid = fopen('L:\GIS Data\Components\Taupo\Taupo GIS Data (Incl. Coring and Well Info).csv'); % 'Data' worksheet
aline = fgetl(fid); % get a line (header?)
acols = length(find(aline==','))+1; % number of columns
aformat = repmat('%s ', 1, acols); % create format
temp = textscan(fid,aformat,'Delimiter',',');
fclose(fid);

[Simon Woodward]

wellnames = temp{1}; % cell array
welldata = horzcat([temp{2:end}]); % cell array
welldata = str2double(welldata); % matrix

[Simon Woodward]

Just a note to future readers. The bug is in Excel/Office, I think, this is just a workaround.

Answer 2

[Ken Atwell]

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.

[Simon Woodward]

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');

Answer 3

[Simon Woodward]

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.

[Walter Roberson]

xlsread 'basic' mode was never designed to read formulas.