The behavior I observe in R2023b is that missing rows are included. It also seems to be the same when run here online:
readcell('tst.xlsx')
How to read all rows of an Excel with missing rows included?
21 views (last 30 days)
Show older comments
My goal is to read all contents of an Excel file as cells using the function "readcell". However, every time when I used this function, it automatically eliminates any rows with missing contents above. How do I force this function to read the entire content of an Excel file with the missing rows above included?
4 Comments
Image Analyst
on 9 Mar 2025
Edited: Image Analyst
on 9 Mar 2025
If you have any more questions, then attach your data and code to read it in with the paperclip icon after you read this:
Accepted Answer
dpb
on 9 Mar 2025
Edited: dpb
on 9 Mar 2025
Actually, I had forgotten until the error message above reminded me...there is one way without the import options struct, but it uses an undocumented argument list named parameter pair...so it's unlikely one will think of trying it.
fn='test2.xlsx';
c=readcell(fn,'DataRange','A1')
The documentation still has only 'Range' as the named parameter and indicates that should control where the data are read from, but as we've seen here, it doesn't.
I haven't tried but I presume that maybe one can specify any fieldname that exists in the appropriate type of import object struct; note that it is 'DataRange' there.
I don't recall if I've submitted this as bug/enhancement/documentation error or not...it really should be on their hit list of fixes one way or another.
I just checked to see if newer documentation showed anything different, but it still has the same descripation at readcell
More Answers (3)
dpb
on 9 Mar 2025
Edited: dpb
on 9 Mar 2025
You can see if it has been fixed (I submitted bug/enhancement report quite some time ago), but I'm still at R2021b, but
readcell('tst.xlsx','range','A1')
should work, but it didn't as of R2021b
The simplest workaround uses an import data options object--
opt=detetctimportoptions('test.xlsx'); % create the base default import struct
opt.DataRange='A1'; % tell it to read data from the start
readcell('test.xlsx',opt) % read using the import options
Locally, R2021b
>> readcell('test.xlsx','range','A1')
ans =
3×3 cell array
{[ 1.00]} {[ 2.00]} {[ 3.00]}
{1×1 missing} {1×1 missing} {1×1 missing}
{[ 4.00]} {[ 5.00]} {[ 6.00]}
>> opt=detectImportOptions('test.xlsx')
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
VariableNamingRule: 'modify'
Range Properties:
DataRange: 'A2' (Start Cell)
VariableNamesRange: ''
RowNamesRange: ''
VariableUnitsRange: ''
VariableDescriptionsRange: ''
To display a preview of the table, use preview
>> opt.DataRange='A1';
>> readcell('test.xlsx',opt)
ans =
4×3 cell array
{1×1 missing} {1×1 missing} {1×1 missing}
{[ 1.00]} {[ 2.00]} {[ 3.00]}
{1×1 missing} {1×1 missing} {1×1 missing}
{[ 4.00]} {[ 5.00]} {[ 6.00]}
>>
1 Comment
dpb
on 9 Mar 2025
readcell('test2.xlsx') % default fails to read blank line
readcell('test2.xlsx','range','A1') % has starting range been fixed yet???
No, it hasn't.
readcell('test2.xlsx','numheaderlines',0)
opt=detectImportOptions('test2.xlsx')
opt.DataRange='A1';
readcell('test2.xlsx',opt)
readcell('test2.xlsx','numheaderlines',0,'range','A1')
Sulaymon Eshkabilov
on 9 Mar 2025
You can consider specifying which sheet and which range of cells to be read/imported using readcell() or readtable():
% READCELL()
D1 = readcell("tst.xlsx", Sheet=1, Range = 'A1:C5')
% READTABLE()
D2 = readtable("tst.xlsx", Sheet=1, Range = 'A1:C5')
4 Comments
Sulaymon Eshkabilov
on 9 Mar 2025
Moved: Matt J
on 9 Mar 2025
Note that I am using this online MATLAB.
dpb
on 9 Mar 2025
No, it doesn't read the initial opening empty lines per OP's request in any of the above, the metainfo data is actually the fourth line in the Excel file, the three blank lines preceding it have not been returned.
See the <examples I added as a comment to my earlier Answer> to see the actual content of the beginning of the file.
Matt J
on 9 Mar 2025
Edited: Matt J
on 9 Mar 2025
ActiveX can be used to determine the last row/column of the worksheet: Then, you can force readcell to read in a larger range with the Range option:
[lastRow,lastCol] = getLastCell('TST.xlsx')
lastRow =
3
lastCol =
4
readcell('TST.xlsx',Range=[1,1,lastRow,lastCol])
ans =
3×4 cell array
{[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]}
{[ 1]} {[ 2]} {[ 3]} {[ 50]}
{[ 4]} {[ 5]} {[ 6]} {[ 70]}
function [lastRow,lastCol] = getLastCell(filename)
sheet = 1; % Change if needed
% Open Excel via ActiveX
excel = actxserver('Excel.Application');
wb = excel.Workbooks.Open(fullfile(pwd, filename));
ws = wb.Sheets.Item(sheet);
% Get the UsedRange
range = ws.UsedRange;
% Get the first used row/column index
firstRow = range.Row; % First used row index
firstCol = range.Column; % First used column index
% Get number of rows and columns in the UsedRange
numRows = range.Rows.Count;
numCols = range.Columns.Count;
% Compute the last used row/column index
lastRow = firstRow + numRows - 1;
lastCol = firstCol + numCols - 1;
% Clean up
wb.Close(false);
excel.Quit();
delete(excel);
end
7 Comments
Matt J
on 9 Mar 2025
It's not a manipulation. The use case given by the OP was that the spreadsheet my contain blank initial lines.
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
You can also select a web site from the following list
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom(English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)