Azzera filtri
Azzera filtri

Read Excel Range which is a "Table" is not working with readtable(​file,'Rang​e','NameOf​Range')

17 visualizzazioni (ultimi 30 giorni)
How can i read a Range in excel which is a table, like in screenshot "Doof" or "excelTab_Zahl" ?
The access to "Dumm" is working but not all the others, despide of they all are listed in the name Manager (see screenshot)
My code:
opts = detectImportOptions('Excel_Read_Write_Matlab.xlsx')
%Working:
data = readtable('Excel_Read_Write_Matlab.xlsx', 'Range', 'Dumm'); %'excelTab_Text'
%Not Working:
data = readtable('Excel_Read_Write_Matlab.xlsx', 'Range', 'Doof'); %'excelTab_Text'
data = readtable('Excel_Read_Write_Matlab.xlsx', 'Range', 'excelTab_Text'); %'excelTab_Text'
data = readtable('Excel_Read_Write_Matlab.xlsx', 'Range', 'excelTab_Zahl'); %'excelTab_Text'
Result of "My code":
opts =
SpreadsheetImportOptions with properties:
Sheet Properties:
Sheet: ''
Replacement Properties:
MissingRule: 'fill'
ImportErrorRule: 'fill'
Variable Import Properties: Set types by name using setvartype
VariableNames: {'A', 'B', 'C' ... and 6 more}
VariableTypes: {'double', 'char', 'char' ... and 6 more}
SelectedVariableNames: {'A', 'B', 'C' ... and 6 more}
VariableOptions: Show all 9 VariableOptions
Access VariableOptions sub-properties using setvaropts/getvaropts
VariableNamingRule: 'modify'
Range Properties:
DataRange: 'A2' (Start Cell)
VariableNamesRange: 'A1'
RowNamesRange: ''
VariableUnitsRange: ''
VariableDescriptionsRange: ''
To display a preview of the table, use preview
Error using readtable
Unable to determine range. Range must be of the form 'A1' (cell), 'A:B' (column-select), '1:5' (row-select), 'A1:B5'
(rectangle-select), or a valid named range in the sheet.
Screenshot of Excel and the Range name Manager:
Excel Name Manger

Risposta accettata

Sebastian Neubauer
Sebastian Neubauer il 22 Feb 2023
"[...] This requires to set import optional Name-Value pair 'UseExcel', 'on' when calling readtable. An enhancement request has already been created in our internal documetnation and is under consideration for a future release. Until then, setting 'UseExcel' is mandatory when using named ranges.[...]"

Più risposte (1)

Timo Dietz
Timo Dietz il 22 Feb 2023
Maybe I'm wrong, but I assume Matlab can not access Excel range definitions ('Namens-Manager') and you have to address column and row by their identifiers ('A1', 'A:B', ...).
See the opts output. Your range definitions are not listed. Just my assumption.
  6 Commenti
Timo Dietz
Timo Dietz il 22 Feb 2023
Have you tried 'readcell' instead of readtable? I encountered that readtable seems to interpret the range differently depending on the cell content. If it's numeric, all data is in rows and the table col names (VariableNames) are just 'Var x', if the cells contain text, the first row in the range is taken as variable names for the table and thus is not part of the data. Not what I assumed. I think it's worth a try.
Sebastian Neubauer
Sebastian Neubauer il 22 Feb 2023
I got the answer to use the 'UseExcel' option.
"[...] This requires to set import optional Name-Value pair 'UseExcel', 'on' when calling readtable. An enhancement request has already been created in our internal documetnation and is under consideration for a future release. Until then, setting 'UseExcel' is mandatory when using named ranges.[...]"

Accedi per commentare.

Tag

Prodotti


Release

R2022b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by