Documentation

This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English verison of the page.

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

SpreadsheetImportOptions

Import options object for Spreadsheets

expand all in page

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. Use a SpreadsheetImportOptions object to query the current (detected) values of import properties or to assign new values based on your import needs.

Create Object

Create a SpreadsheetImportOptions object using the detectImportOptions function.

Properties

expand all

Variable Properties

Variable names, specified as a cell array of character vectors. The VariableNames property contains the names to use when importing variables.

If the data contains N variables, but no variable names are detected, then the VariableNames property contains {'Var1','Var2',...,'VarN'}.

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 | cell

Variable data types, specified as a cell array of character vectors. The VariableTypes property designates the data types to use when importing variables. When assigning new values, specify VariableTypes as a cell array of valid data type names.

To update the VariableTypes property, use the setvartype function.

Example: opts.VariableTypes returns the current (detected) variable data types.

Example: opts = setvartype(opts,'Height',{'double'}) changes the data type of the variable Height to double.

Data Types: cell | single | double | int8 | int16 | int32 | int64 | uint8 | uint16 | uint32 | uint64 | logical | char | categorical | datetime

Subset of variables to import, specified as a character vector, a cell array of character vectors, 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.

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 | logical | char | cell

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 to read from, specified as an empty character array, a character vector 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.

SpecificationBehavior
'' (default)Import data from the first sheet.
nameImport data from the matching sheet name, regardless of order of sheets in the spreadsheet file.
integerImport data from sheet in the position denoted by the integer, regardless of the sheet names in the spreadsheet file.

Example: 'Sheet7'

Example: 7

Data Types: char | single | double

Location of the data to be imported, specified as a character vector, a positive scalar integer, or an empty character array. Specify DataRange as one of the values in this table.

Specified byBehavior

'Cell'

Specify the starting cell for the data, using Excel® A1 notation. For example, A5 is the identifier for the cell at the intersection of column A and row 5.

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.

Example: 'A5'

'Corner1:Corner2'

Rectangular Range

Specify the exact range to read using the rectangular range form, where Corner1 and Corner2 are two opposing corners that define the region to read.

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 NumVariables property.

Example: 'A5:K50'

'Row1:Row2'

Row Range

Specify 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: '5:500'

'Column1:Column2'

Column Range

Specify 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 NumVariables property.

Example: 'A:K'

n

Number Index

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: 5

''

Unspecified or Empty

Do not fetch any data.

Example: ''

Data Types: char | single | double

Location of row names, specified as a character vector, positive scalar integer, or an empty character array. Specify RowNamesRange as one of the values in this table.

Specified byBehavior

'Cell'

Specify the starting cell for the data, using Excel A1 notation. For example, A5 is the identifier for the cell at the intersection of column A and row 5.

The importing function identifies a name for each variable in the data.

Example: 'A5'

'Corner1:Corner2'

Rectangular Range

Specify the exact range to read using the rectangular range form, where Corner1 and Corner2 are two opposing corners that define the region to read.

The number of rows contained in RowNamesRange must match the number of data rows, and the range indicated by RowNamesRange must span only one column.

Example: '5:50'

'Column1:Column2'

Column Range

Specify range by identifying the beginning and ending columns using Excel column letters or numbers.

Row names must be in a single row.

Example: 'A:K'

n

Number Index

Specify the column containing the row names using a positive scalar column index.

Example: 5

''

Unspecified or Empty

Indicate that there are no row names.

Example: ''

Data Types: char | single | double

Location of variable names, specified as a character vector, positive scalar integer, or an empty character array. Specify VariableNamesRange as one of the values in this table.

Specified byBehavior

'Cell'

Specify the starting cell for the data, using Excel A1 notation. For example, A5 is the identifier for the cell at the intersection of column A and row 5.

The importing function reads a name for each variable in the data.

Example: 'A5'

'Corner1:Corner2'

Rectangular Range

Specify the exact range to read using the rectangular range form, where Corner1 and Corner2 are two opposing corners that define the region to read.

The number of columns must match the number specified in the NumVariables property, and the range must span only one row.

Example: 'A5:K5'

'Row1:Row2'

Row Range

Specify range by identifying the beginning and ending rows using Excel row numbers.

Must be a single row.

Example: '5:5'

n

Number Index

Specify the row containing the variable names using a positive scalar row index.

Example: 5

''

Unspecified or Empty

Indicate that there are no variable names.

Example: ''

Data Types: char | single | double

Location of variable descriptions, specified as a character vector, positive scalar integer, or an empty character array. Specify VariableDescriptionRange as one of the values in this table.

Specified byBehavior

'Cell'

Specify the starting cell for the data, using Excel A1 notation. For example, A5 is the identifier for the cell at the intersection of column A and row 5.

The importing function reads a description for each variable in the data.

Example: 'A5'

'Corner1:Corner2'

Rectangular Range

Specify the exact range to read using the rectangular range form, where Corner1 and Corner2 are two opposing corners that define the region to read.

The number of columns must match the number specified in the NumVariables property, and the range must span only one row.

Example: 'A5:K5'

'Row1:Row2'

Row Range

Specify range by identifying the beginning and ending rows using Excel row numbers.

Must be a single row.

Example: '5:5'

n

Number Index

Specify the row containing the descriptions using a positive scalar row index.

Example: 5

''

Unspecified or Empty

Indicate that there are no variable descriptions.

Example: ''

Data Types: char | single | double

Location of variable units, specified as a character vector, positive scalar integer, or an empty character array. Specify VariableUnitsRange as one of the values in this table.

Specified byBehavior

'Cell'

Specify the starting cell for the data, using Excel A1 notation. For example, A5 is the identifier for the cell at the intersection of column A and row 5.

The importing function reads a unit for each variable in the data.

Example: 'A5'

'Corner1:Corner2'

Rectangular Range

Specify the exact range to read using the rectangular range form, where Corner1 and Corner2 are two opposing corners that define the region to read.

The number of columns must match the number specified in the NumVariables property, and the range must span only one row.

Example: 'A5:K5'

'Row1:Row2'

Row Range

Specify range by identifying the beginning and ending rows using Excel row numbers.

Must be a single row.

Example: '5:5'

n

Number Index

Specify the row containing the data units using a positive scalar row index.

Example: 5

''

Unspecified or Empty

Indicate that there are no variable units.

Example: ''

Data Types: char | single | double

Replacement Rules

Procedure to manage missing data, specified as one of the values in this table.

Missing RuleBehavior
'fill'

Replace missing data with the contents of the FillValue property.

The FillValue property is specified in the VariableImportOptions object of the variable being imported. For more information on accessing the FillValue property, see getvaropts.

'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';

Procedure to handle import errors, specified as one of the values in this table.

Import Error RuleBehavior
'fill'

Replace the data where the error occurred with the contents of the FillValue property.

The FillValue property is specified in the VariableImportOptions object of the variable being imported. For more information on accessing the FillValue property, see getvaropts.

'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';

Object Functions

getvaropts Get variable import options
setvaropts Set variable import options
setvartype Set variable data types

Examples

expand all

Open Script

Create import options for a spreadsheet file, specify the variables to import, and then read the data.

Create an import options object from a file.

opts = detectImportOptions('patients.xls')

opts = 

  SpreadsheetImportOptions with properties:

   Sheet Properties:
                        Sheet: ''

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'

   Variable Import Properties:   Set types by name with setvartype
                VariableNames: {1×10 cell}
                VariableTypes: {1×10 cell}
        SelectedVariableNames: {1×10 cell}

   Range Properties:
                    DataRange: 'A2'
           VariableNamesRange: 'A1'
                RowNamesRange: ''
           VariableUnitsRange: ''
    VariableDescriptionsRange: ''

          VariableOptions: [1×10 VariableOptions]
    Access VariableOptions sub-properties using setvaropts/getvaropts

Modify the options object to specify which variables to import.

opts.SelectedVariableNames = {'Systolic','Diastolic'};

Use readtable along with the options object to import the specified variables.

T = readtable('patients.xls',opts);
summary(T)

Variables:

    Systolic: 100×1 double
        Values:

            min       109       
            median    122       
            max       138       

    Diastolic: 100×1 double
        Values:

            min         68       
            median    81.5       
            max         99

See Also

|

Introduced in R2016b

Was this topic helpful?
MATLAB Documentation
Other Documentation
Support
Tackling Big Data with MATLAB

Tackling Big Data with MATLAB

Watch now