"Readmatrix" function excluding non-numeric value if first on last in vector - Help

Hi,

I'm currently extracting sequences stored in a excel worksheet. These sequences represents the order in which data must be extract in other files. All columns are of the same length, for exemple 5 elements. Now the twist is that in some columns there are non-numeric terms such as 'n.a.', as for example in the '.xlsx' :

When using the readmatrix() function on the first line, the 'n.a.' is stored in the middle of the vector, the function returns a 1x5 vector with a 'NaN' value in the middle or at the first position as shown below :

readmatrix('Example.xlsx','Sheet','Feuil1','Range','A1:E1')

ans =

     1     2   NaN     4     5
     
readmatrix('Example.xlsx','Sheet','Feuil1','Range','A3:E3')

ans =

   NaN     2     3     4     5

However, if the number are stored in the other direction (as columns), the readmatrix() function ignores the 'n.a.' values stored first :

readmatrix('Example.xlsx','Sheet','Feuil2','Range','A1:A4')

ans =

     1
     2
     3
     4
   
readmatrix('Example.xlsx','Sheet','Feuil2','Range','C1:C4')

ans =

     2
     3
     4
     
readmatrix('Example.xlsx','Sheet','Feuil2','Range','E1:E4')

ans =

     1
     2
     3
   NaN

Is there an logic behind this behavior or a way around this problem?

Thank youv very much.

[dpb]

Try using the SpreadsheetImportOptions object to see if setting number of variables and type will help.

Otherwise, revert to readtable or xlsread that will return (optionally) the full raw cell values you can process.

Or, cleanup the Excel spreadsheet first by ensuring NaN numeric values or other missing value.

Answer 1

[Walter Roberson]

In that case it would be treated as a variable name field to be skipped.