Readmartix function removes first blank row

When I'm using the readmartix function to import data from an excel sheet if the first row is blank or a set of NaNs the output matrix removes that first row. I would like to keep that row to keep matrix size consistent across data sets. Is there a option that can be easily changed to keep that row rather than it being removed then having to add it back later.

[dpb]

You can try creating a SpreadsheetImportOptions object that specifies the starting row for data explicitly and see if that will work.

I've not tried it but only idea I've got otomh other than reverting to the deprecated xlsread and using the raw data return variable and picking the data out of it as we used to do to solve the similar issues with it in returning numerical data that wasn't consistent when were missing rows.

[Benjamin Binder-Markey]

Yes I should have been clearer. I'm specifically calling on a specific sheet and range ie readmatrix('file name', 'sheet', 'sheetname', 'range', "C5:E20") Which should return a 15x3 matrix, but if the first row is blank or NaN (i'm creating multiple matrices from different parts of the spread sheet) it will remove that row and return a 14x3 matrix. I was just wondering it there is an option to not remove that first line, rather than having to insert it later so all the matrices have the same dimensions. I've tried the xlsread and does the same thing.

Thanks!

[dpb]

I understand that -- the implied answer to the Q? by the response is "no, there's no specific option supplied" -- I was suggesting a possible workaround that might possibly work if you create and pass an importoptions object that specifically contains the data start line.

I do not know if that will override the default behavior or not; I have not tested it, but other than reverting to the raw output variable from xlsread or determining after the fact if such has happened and fixing up the occurrence, it's the only real option I can think of to try.

[dpb]

" I'm specifically calling on a specific sheet and range ie readmatrix('file name', 'sheet', 'sheetname', 'range', "C5:E20") Which should return a 15x3 matrix,..."

> numel(5:20)
ans =
    16
>> 

Maybe there's some confusion regarding what you expect as well, here...

[Benjamin Binder-Markey]

yes sorry late night tying just making up an example...

Answer 1

[David Hill]

opts = detectImportOptions('Data.csv');%look at opts.DataLines and change it
opts.DataLines=[1 Inf];
data=readmatrix('Data.csv',opts);

[dpb]

Yeah, what I was suggesting above altho for the specific case OP is actually using specific sheet:range so must be dealing with spreadsheet file...

Answer 2

[dpb]

Well, let's quit hypothesizing and just see what happens...

% BB-M.xlsx has reshape(3:8,2,[]) at C2 upper left corner
>> n=xlsread('BB-M.xlsx',1,'C1:E3')   % as we know,xlsread() left to own devices returns only data
n =
     3     4     5
     6     7     8
>> [n,~,r]=xlsread('BB-M.xlsx',1,'C1:E3')    % the raw optional output it returns whole thing but cell array
n =
     3     4     5
     6     7     8
r =
  3×3 cell array
    {[NaN]}    {[NaN]}    {[NaN]}
    {[  3]}    {[  4]}    {[  5]}
    {[  6]}    {[  7]}    {[  8]}
>> opt=detectImportOptions('BB-M.xlsx')     % see what the import option object is by default...
opt = 
  SpreadsheetImportOptions with properties:
   Sheet Properties:
                        Sheet: ''
   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'Var1', 'Var2', 'Var3'}
                VariableTypes: {'double', 'double', 'double'}
        SelectedVariableNames: {'Var1', 'Var2', 'Var3'}
              VariableOptions: Show all 3 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts
        PreserveVariableNames: false
   Range Properties:
                    DataRange: 'C2' (Start Cell)
           VariableNamesRange: ''
                RowNamesRange: ''
           VariableUnitsRange: ''
    VariableDescriptionsRange: '' 
	To display a preview of the table, use preview
>> opt.DataRange='C1';         % OK it starts at data location it found, let's set the target specifically
>> readmatrix('BB-M.xlsx',opt) % and pass the modified object
ans =
   NaN   NaN   NaN
     3     4     5
     6     7     8
>>

And, voila! The desired starting point is honored and missing data indicatiors returned.

Easy enough solution -- create an import options object to match what you want and pass it...you can have just one of these saved; no need to regenerate every time as long as the location and spreadsheet structure is the same.

[Benjamin Binder-Markey]

Thanks for all the feedback! I found my error thanks to your solution, I was calling the option 'Range' vs. 'DataRange' as soon as I switched it to 'DataRange' it returned the correct size with the empty cells.

Thanks again.