Importing an Excel File - How to ignore non-numeric values

Hi Everyone.

I am importing some HUGE Excel files into Matlab for plotting. My code to do this is as follows:

clear all  clc;
[filename,pathname] = uigetfile('*.*','Select Input File','*.*');
data = importdata([pathname,filename],',');
for n = 1:length(data.colheaders)
    eval([data.colheaders{n},'=data.data(:,',num2str(n),');']);
end
clear('filename','pathname','data','n');

save('data.mat');

This process works very well, except when an irregular value (such as 1.#INF00) is in one of the cells. Then the process stops importing after that row in the excel file. Does anyone know a way around thisaround this? to ignare these values or just save them as NaN or something?

Thanks for your help!

Answer 1

[Fangjun Jiang]

I would recommend trying [NUMERIC,TXT,RAW]=XLSREAD(FILE) to see if it can put numeric data apart from string data. If not, you can still try to process the raw data, some useful functions are: cellfun(),cell2mat(), isnumeric(), iscell(), ischar().

[num,txt,raw]=xlsread('test.xls')
num =
     1     2     3
     4   NaN     6
     7     8     9
txt = 
    'ColA'    'ColB'        'ColC'
    ''        ''            ''    
    ''        '1.#INF00'    ''    
raw = 
    'ColA'    'ColB'        'ColC'
    [   1]    [       2]    [   3]
    [   4]    '1.#INF00'    [   6]
    [   7]    [       8]    [   9]
cellfun(@isnumeric,raw)
ans =
     0     0     0
     1     1     1
     1     0     1
     1     1     1
cellfun(@ischar,raw)
ans =
     1     1     1
     0     0     0
     0     1     0
     0     0     0

Answer 2

[Oleg Komarov]

• I frankly would suggest to avoid eval: http://matlab.wikia.com/wiki/FAQ#How_can_I_create_variables_A1.2C_A2.2C....2CA10_in_a_loop.3F
• You can use textscan's option TreatAsEmpty to include all the strings that should be considered as NaN.