spreadsheetImportOptions
Import options object for Spreadsheets
Description
A SpreadsheetImportOptions
object enables you to specify
how MATLAB® imports tabular data from spreadsheet files. The object contains
properties that control the data import process, including the handling of errors and
missing data.
Creation
You can create a SpreadsheetImportOptions
object by using either
the detectImportOptions
function or the
spreadsheetImportOptions
function (described here):
Use
detectImportOptions
to detect and populate the import properties based on the contents of the spreadsheet specified infilename
.opts = detectImportOptions(filename);
Use
spreadsheetImportOptions
to define the import properties based on your import requirements.
Syntax
Description
opts = spreadsheetImportOptions
creates a
SpreadsheetImportOptions
object with one variable.
opts = spreadsheetImportOptions('NumVariables',
creates the object with the number of variables specified in
numVars
)numVars
.
opts = spreadsheetImportOptions(___,
specifies additional properties for
Name,Value
)SpreadsheetImportOptions
object using one or more
name-value pair arguments.
Input Arguments
numVars
— Number of variables
positive scalar integer
Number of variables, specified as a positive scalar integer.
Properties
Variable Properties
VariableNames
— Variable names
cell array of character vectors | string array
Variable names, specified as a cell array of character vectors or string array. The
VariableNames
property contains the names to use when importing
variables.
If the data contains N
variables, but no variable names are specified, then
the VariableNames
property contains
{'Var1','Var2',...,'VarN'}
.
To support invalid MATLAB identifiers as variable names, such as variable names containing spaces
and non-ASCII characters, set the value of VariableNamingRule
to
'preserve'
.
Example: opts.VariableNames
returns the current
(detected) variable names.
Example: opts.VariableNames(3)
= {'Height'}
changes the name of the third variable to Height
.
Data Types: char
| string
| cell
VariableNamingRule
— Flag to preserve variable names
"modify"
(default) | "preserve"
Flag to preserve variable names, specified as either "modify"
or
"preserve"
.
"modify"
— Convert invalid variable names (as determined by theisvarname
function) to valid MATLAB identifiers."preserve"
— Preserve variable names that are not valid MATLAB identifiers such as variable names that include spaces and non-ASCII characters.
Starting in R2019b, variable names and row names can include any characters, including
spaces and non-ASCII characters. Also, they can start with any characters, not just
letters. Variable and row names do not have to be valid MATLAB identifiers (as determined by the isvarname
function). To preserve these variable names and row names, set
the value of VariableNamingRule
to "preserve"
.
Variable names are not refreshed when the value of VariableNamingRule
is changed from "modify"
to "preserve"
.
Data Types: char
| string
VariableTypes
— Data types of variable
cell array of character vectors | string array
Data type of variable, specified as a cell array of character vectors, or string array
containing a set of valid data type names. The VariableTypes
property
designates the data types to use when importing variables.
To update the VariableTypes
property, use the setvartype
function.
Example: opts.VariableTypes
returns the current variable data
types.
Example: opts = setvartype(opts,'Height',{'double'})
changes the
data type of the variable Height
to
double
.
SelectedVariableNames
— Subset of variables to import
character vector | string scalar | cell array of character vectors | string array | array of numeric indices
Subset of variables to import, specified as a character vector, string scalar, cell array of character vectors, string array or an array of numeric indices.
SelectedVariableNames
must be a subset of
names contained in the VariableNames
property.
By default, SelectedVariableNames
contains all
the variable names from the VariableNames
property,
which means that all variables are imported.
Use the SelectedVariableNames
property to
import only the variables of interest. Specify a subset of variables
using the SelectedVariableNames
property and use readtable
to import only that subset.
To support invalid MATLAB identifiers as variable names, such as variable names
containing spaces and non-ASCII characters, set the value of
VariableNamingRule
to
'preserve'
.
Example: opts.SelectedVariableNames = {'Height','LastName'}
selects
only two variables, Height
and LastName
,
for the import operation.
Example: opts.SelectedVariableNames
= [1 5]
selects only two variables, the first variable and
the fifth variable, for the import operation.
Example: T = readtable(filename,opts)
returns
a table containing only the variables specified in the SelectedVariableNames
property
of the opts
object.
Data Types: uint16
| uint32
| uint64
| char
| string
| cell
VariableOptions
— Type specific variable import options
array of variable import options objects
Type specific variable import options, returned as an array
of variable import options objects. The array contains an object corresponding
to each variable specified in the VariableNames
property.
Each object in the array contains properties that support the importing
of data with a specific data type.
Variable options support these data types: numeric, text, logical
, datetime
,
or categorical
.
To query the current (or detected) options for a variable, use
the getvaropts
function.
To set and customize options for a variable, use the setvaropts
function.
Example: opts.VariableOptions
returns a collection
of VariableImportOptions
objects, one corresponding
to each variable in the data.
Example: getvaropts(opts,'Height')
returns
the VariableImportOptions
object for the Height
variable.
Example: opts = setvaropts(opts,'Height','FillValue',0)
sets
the FillValue
property for the variable Height
to 0
.
Location Properties
Sheet
— Sheet to read from
''
empty character array (default) | character vector | string scalar | positive scalar integer
Sheet to read from, specified as an empty character array, a character vector or string scalar
containing the sheet name, or a positive scalar integer denoting the sheet
index. Based on the value specified for the Sheet
property, the import function behaves as described in the table.
Specification | Behavior |
---|---|
'' (default) | Import data from the first sheet. |
Name | Import data from the matching sheet name, regardless of order of sheets in the spreadsheet file. |
Integer | Import data from sheet in the position denoted by the integer, regardless of the sheet names in the spreadsheet file. |
Data Types: char
| string
| single
| double
DataRange
— Location of data
character vector | string scalar | positive scalar integer | array of positive scalar integers | cell array of character vector | string array
Location of data to be imported, specified as a character vector, string scalar, cell array of character vectors, string array, positive scalar integer or an N
-by-2
array of positive scalar integers. Specify DataRange
using one of these forms.
Specified by | Behavior |
---|---|
Starting Cell or Starting Row | Specify the starting cell for the data, using Excel® Using the starting cell, the importing function automatically detects the extent of the data, by beginning the import at the start cell and ending at the last empty row or footer range. Alternatively, specify the first row containing the data using the positive scalar row index. Using the specified row index, the importing function automatically detects the extent of the data by reading from the specified first row to the end of the data or the footer range. Example: |
Rectangular Range | Specify the exact range to read using the rectangular range form, where The importing function only reads the data contained in the specified range. Any empty fields within the specified range are imported as missing cells. The number of columns must match the number specified in the Example: |
Row Range or Column Range | Specify the range by identifying the beginning and ending rows using Excel row numbers. Using the specified row range, the importing function automatically detects the column extent by reading from the first nonempty column to the end of the data, and creates one variable per column. Example: Alternatively, specify the range by identifying the beginning and ending columns using Excel column letters or numbers. Using the specified column range, the import function automatically detects the row extent by reading from the first nonempty row to the end of the data or the footer range. The number of columns in the specified range must match the number specified in the Example: |
Multiple Row Ranges | Specify multiple row ranges to read with an A valid array of multiple row ranges must:
Use of Example: |
Unspecified or Empty | Do not fetch any data. Example: |
Data Types: char
| string
| cell
| single
| double
RowNamesRange
— Location of row names
character vector | string scalar | positive scalar integer | ''
empty character array
Location of row names, specified as a character vector, string scalar, positive scalar integer, or an empty character array. Specify RowNamesRange
as one of the values in this table.
Specified by | Behavior |
---|---|
| Specify the starting cell for the data, using Excel The importing function identifies a name for each variable in the data. Example: |
Rectangular Range | Specify the exact range to read using the rectangular range form, where The number of rows contained in Example: |
Row Range | Specify range by identifying the beginning and ending rows using Excel row numbers. Row names must be in a single column. Example: |
Number Index | Specify the column containing the row names using a positive scalar column index. Example: |
Unspecified or Empty | Indicate that there are no row names. Example: |
Data Types: char
| single
| double
VariableNamesRange
— Location of variable names
character vector | string scalar | positive scalar integer | ''
empty character array
Location of variable names, specified as a character vector, string scalar, positive scalar integer, or an empty character array. Specify VariableNamesRange
as one of the values in this table.
Specified by | Behavior |
---|---|
| Specify the starting cell for the data, using Excel The importing function reads a name for each variable in the data. Example: |
Rectangular Range | Specify the exact range to read using the rectangular range form, where The number of columns must match the number specified in the Example: |
Row Range | Specify range by identifying the beginning and ending rows using Excel row numbers. Must be a single row. Example: |
Number Index | Specify the row containing the variable names using a positive scalar row index. Example: |
Unspecified or Empty | Indicate that there are no variable names. Example: |
Data Types: char
| single
| double
VariableDescriptionsRange
— Location of variable descriptions
character vector | string scalar | ''
empty character array
Location of variable descriptions, specified as a character vector, string scalar, positive scalar integer, or an empty character array. Specify VariableDescriptionRange
as one of the values in this table.
Specified by | Behavior |
---|---|
| Specify the starting cell for the data, using Excel The importing function reads a description for each variable in the data. Example: |
Rectangular Range | Specify the exact range to read using the rectangular range form, where The number of columns must match the number specified in the Example: |
Row Range | Specify range by identifying the beginning and ending rows using Excel row numbers. Must be a single row. Example: |
Number Index | Specify the row containing the descriptions using a positive scalar row index. Example: |
Unspecified or Empty | Indicate that there are no variable descriptions. Example: |
Data Types: char
| string
| single
| double
VariableUnitsRange
— Location of variable units
character vector | string scalar | positive scalar integer | ''
empty character array
Location of variable units, specified as a character vector, string scalar, positive scalar integer, or an empty character array. Specify VariableUnitsRange
as one of the values in this table.
Specified by | Behavior |
---|---|
| Specify the starting cell for the data, using Excel The importing function reads a unit for each variable in the data. Example: |
Rectangular Range | Specify the exact range to read using the rectangular range form, where The number of columns must match the number specified in the Example: |
Row Range | Specify range by identifying the beginning and ending rows using Excel row numbers. Must be a single row. Example: |
Number Index | Specify the row containing the data units using a positive scalar row index. Example: |
Unspecified or Empty | Indicate that there are no variable units. Example: |
Data Types: char
| string
| single
| double
Replacement Rules
MissingRule
— Procedure to manage missing data
'fill'
(default) | 'error'
| 'omitrow'
| 'omitvar'
Procedure to manage missing data, specified as one of the values in this table.
Missing Rule | Behavior |
---|---|
'fill' | Replace missing data with the contents of the The |
'error' | Stop importing and display an error message showing the missing record and field. |
'omitrow' | Omit rows that contain missing data. |
'omitvar' | Omit variables that contain missing data. |
Example: opts.MissingRule = 'omitrow';
Data Types: char
| string
ImportErrorRule
— Procedure to handle import errors
'fill'
(default) | 'error'
| 'omitrow'
| 'omitvar'
Procedure to handle import errors, specified as one of the values in this table.
Import Error Rule | Behavior |
---|---|
'fill' | Replace the data where the error occurred with the contents of the
The
|
'error' | Stop importing and display an error message showing the error-causing record and field. |
'omitrow' | Omit rows where errors occur. |
'omitvar' | Omit variables where errors occur. |
Example: opts.ImportErrorRule = 'omitvar';
Data Types: char
| string
Object Functions
getvaropts | Get variable import options |
setvaropts | Set variable import options |
setvartype | Set variable data types |
preview | Preview eight rows from file using import options |
Examples
Define Import Options for Variable in Spreadsheet File
Create an import options object to read one variable from the file patients.xls
.
Initialize a SpreadsheetImportOptions
object, specify the variable name, variable type, and the data starting cell. Then, use the preview
function with the import options object to preview the data.
opts = spreadsheetImportOptions; opts.VariableNames = 'LastName'; opts.VariableTypes = 'categorical'; opts.DataRange = 'A2'; preview('patients.xls',opts)
ans=8×1 table
LastName
________
Smith
Johnson
Williams
Jones
Brown
Davis
Miller
Wilson
Import the variable from the file.
oneVar = readtable('patients.xls',opts); whos oneVar
Name Size Bytes Class Attributes oneVar 100x1 14309 table
Define Import Options for Multiple Variables in Spreadsheet File
Create an import options object to read multiple variables from patients.xls
.
Based on the contents of your file, define the number of variables, variable names, variable types, and the data starting location.
numVars = 7; varNames = {'LastName','Gender','Age','Location','Height','Weight','Smoker'} ; varTypes = {'char','categorical','int32','char','double','double','logical'} ; dataStartLoc = 'A2';
Initialize the import options object opts
using the spreadsheetImportOptions
function and your variable information.
opts = spreadsheetImportOptions('NumVariables',numVars,... 'VariableNames',varNames,... 'VariableTypes',varTypes,... 'DataRange', dataStartLoc);
Preview the data using the preview
function with the import options.
preview('patients.xls',opts)
ans=8×7 table
LastName Gender Age Location Height Weight Smoker
____________ ______ ___ _____________________________ ______ ______ ______
{'Smith' } Male 38 {'County General Hospital' } 71 176 true
{'Johnson' } Male 43 {'VA Hospital' } 69 163 false
{'Williams'} Female 38 {'St. Mary's Medical Center'} 64 131 false
{'Jones' } Female 40 {'VA Hospital' } 67 133 false
{'Brown' } Female 49 {'County General Hospital' } 64 119 false
{'Davis' } Female 46 {'St. Mary's Medical Center'} 68 142 false
{'Miller' } Female 33 {'VA Hospital' } 64 142 true
{'Wilson' } Male 40 {'VA Hospital' } 68 180 false
Import the data using readtable
.
T = readtable('patients.xls',opts); whos T
Name Size Bytes Class Attributes T 100x7 33987 table
Version History
Introduced in R2016bR2018b: Create options object using spreadsheetImportOptions
function
Use the spreadsheetImportOptions
function to create a
SpreadsheetImportOptions
object. Previously, you could create
this object only by using the detectImportOptions
function.
See Also
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
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)