Readtable is not reading all of my Data

I am trying to use readtable to read a large excel file (5185 rows) and sometimes this excel file will have columns 21 & 22 empty other then the title up until the 3000+ lines. When this is the case the entire column of 21 & 22 has <missing> as its variable. I assume that after a certain amount of rows the readtable just assumes that the rest of the column is empty. For example, when there are values stored in those columns starting at row 148 it will properly read the data and <missing> does not come up anywhere. Does anyone know a way that can bypass this assumption?

This is the code I am using to read the excel data:

FileName_excel_Results = sprintf('SmartChipData_A%06i%s_Results.xlsx', app.ChipIDEditField.Value, app.dropdown.Value);

fullFileName_excel_Results = fullfile(app.result_filepath, FileName_excel_Results);

t = readtable(fullFileName_excel_Results, 'Sheet','Summary','ExpectedNumVariables',23); % storing the Selected_Spots excel

summary = table2cell(t);

disp(string(summary(3196,:)));

Here is the output I get from the Disp:

Columns 1 through 7

"3195" "45" "28" "45" "C:\Users\Single C…" "C:\Users\Single C…" "[0,0,0]"

Columns 8 through 17

"45/28" "0" "0" "1" "-1" "-1" "-1" "Auto" "A" "i7-28"

Columns 18 through 23

"CGATAACTGGTCTTAAG…" "i5-45" "TAAAGTAGTAAAGTAGG…" <missing> <missing> "47"

This is what is stored in that row from the excel sheet:

3195 45 28 45 C:\Users\Single Cell Scope\Desktop\Chip143881\Cyan\S0000\C45\R45_C45_0000_00_Cyan.tif C:\Users\Single Cell Scope\Desktop\Chip143881\Green\S0000\C45\R45_C45_0000_01_Green.tif [0,0,0] 45/28 0 0 1 -1 -1-1 Auto A i7-28 CGATAACTGGTCTTAAGATGTGAA i5-45 TAAAGTAGTAAAGTAGGATTCTCT C1 1A1 47

[Voss]

Can you upload the file (using the paperclip button)?

[Ben Roberts]

Just added the file

Answer 1

[Voss]

filename = fullfile('.', sprintf('SmartChipData_A%06i%s_Results.xlsx', 143881, 'B'));

Specifying ExpectedNumVariables in detectImportOptions and then using those options in readtable appears to work:

opts = detectImportOptions(filename,'Sheet','Summary','ExpectedNumVariables',23);
t = readtable(filename, opts, 'Sheet', 'Summary');
t(4738:4750,20:23)

ans = 13x4 table
             Primer_I5              Pick_Met    Spot_Well     Num_Drops
    ____________________________    ________    __________    _________

    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C1'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C1'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C2'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C1'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C1'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C2'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C1'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C1'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C2'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C1'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C3'}     {0x0 char}     {'0' }  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C1'}     {'1A1'   }     {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}     {'C1'}     {'1A1'   }     {'47'}  

But specifying ExpectedNumVariables in readtable directly does not:

t = readtable(filename, 'Sheet', 'Summary', 'ExpectedNumVariables', 23);
t(4738:4750,20:23)

ans = 13x4 table
             Primer_I5              Pick_Met    Spot_Well    Num_Drops
    ____________________________    ________    _________    _________

    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'0' }  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}  
    {'TGGTGAAATATCTTACGTGAGAAC'}      NaN          NaN        {'47'}