This is machine translation

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

Note: This page has been translated by MathWorks. Click here to see
To view all translated materials including this page, select Country from the country navigator on the bottom of this page.

detectImportOptions

Create import options based on file content

Syntax

opts = detectImportOptions(filename)
opts = detectImportOptions(filename,Name,Value)

Description

example

opts = detectImportOptions(filename) locates a table in a file and returns the import options for importing the table. You can modify the options object and use it with readtable to control how MATLAB® imports tabular data. The type of the options returned depends on the file extension. For example, the function returns a SpreadsheetImportOptions object if filename is a spreadsheet file. However, the function returns a DelimitedTextImportOptions or FixedWidthImportOptions object if filename is a text file.

example

opts = detectImportOptions(filename,Name,Value) locates a table in a file with the help of additional parameters specified by one or more Name,Value pair arguments.

Examples

collapse all

Detect 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 using setvartype
                VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
                VariableTypes: {'char', 'char', 'double' ... and 7 more}
        SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
              VariableOptions: Show all 10 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts

   Range Properties:
                    DataRange: 'A2' (Start Cell)
           VariableNamesRange: 'A1'
                RowNamesRange: ''
           VariableUnitsRange: ''
    VariableDescriptionsRange: '' 
	To display a preview of the table, use preview

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: 100x1 double

        Values:

            Min          109    
            Median       122    
            Max          138    

    Diastolic: 100x1 double

        Values:

            Min            68    
            Median       81.5    
            Max            99    

Create import options, tailor the data types for multiple variables, and then read the data.

Create an import options object from a text file.

opts = detectImportOptions('airlinesmall.csv')
opts = 
  DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
                EmptyLineRule: 'skip'
                     Encoding: 'UTF-8'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'Year', 'Month', 'DayofMonth' ... and 26 more}
                VariableTypes: {'double', 'double', 'double' ... and 26 more}
        SelectedVariableNames: {'Year', 'Month', 'DayofMonth' ... and 26 more}
              VariableOptions: Show all 29 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts

   Location Properties:
                    DataLines: [2 Inf]
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview

Examine the Type property of variables TaxiIn and TaxiOut.

getvaropts(opts,{'TaxiIn','TaxiOut'})
ans = 
  1x2 TextVariableImportOptions array with properties:

    Name
    Type
    FillValue
    TreatAsMissing
    QuoteRule
    Prefixes
    Suffixes
    EmptyFieldRule
    WhitespaceRule

Change the type of the variables TaxiIn and TaxiOut to double.

 opts = setvartype(opts,{'TaxiIn','TaxiOut'},'double');

Specify the subset of variables to import and examine.

opts.SelectedVariableNames = {'TaxiIn','TaxiOut'};

Use the readtable function along with the options object to import the selected variables. Display a summary of the table.

T = readtable('airlinesmall.csv',opts);
summary(T)
Variables:

    TaxiIn: 123523x1 double

        Values:

            Min            0      
            Median         5      
            Max            1451   
            NumMissing     37383  

    TaxiOut: 123523x1 double

        Values:

            Min             0      
            Median          13     
            Max             755    
            NumMissing      37364  

Import text data as a string data type by specifying import options.

Create an options object for the file.

opts = detectImportOptions('outages.csv');

Specify which variables to import using readtable, and then show a summary. The data type of the selected variables is char.

opts.SelectedVariableNames = {'Region','Cause'};
T = readtable('outages.csv',opts);
summary(T)
Variables:

    Region: 1468x1 cell array of character vectors

    Cause: 1468x1 cell array of character vectors

Import text data as a string data type, and then create import options by specifying the TextType name-value pair.

opts = detectImportOptions('outages.csv','TextType','string');

Specify which variables to import using readtable, and then show a summary. The data type of the selected variables is now string.

opts.SelectedVariableNames = {'Region','Cause'};
T = readtable('outages.csv',opts);
summary(T)
Variables:

    Region: 1468x1 string

    Cause: 1468x1 string

Input Arguments

collapse all

Name of the file to read, specified as a character vector or string scalar.

Depending on the location of your file, filename can take on one of these forms.

Location

Form

Current folder or folder on the MATLAB path

Specify the name of the file in filename.

Example: 'myFile.txt'

File in a folder

If the file is not in the current folder or in a folder on the MATLAB path, then specify the full or relative path name in filename.

Example: 'C:\myFolder\myFile.xlsx'

Example: '\imgDir\myFile.txt'

Remote Location

If the file is stored at a remote location, then filename must contain the full path of the file specified as an internationalized resource identifier (IRI) of the form:

scheme_name://path_to_file/my_file.ext

Based on your remote location, scheme_name can be one of the values in this table.

Remote Locationscheme_name
Amazon S3™s3
Windows Azure® Blob Storagewasb, wasbs
HDFS™hdfs

For more information, see Work with Remote Data.

Example: 's3://bucketname/path_to_file/my_file.csv'

If filename includes the file extension, then detectImportOptions determines the file format from the extension. Otherwise, you must specify the 'FileType' name-value pair to indicate the type of file.

The detectImportOptions function supports these file extensions: .txt, .dat, .csv, .xls, .xlsb, .xlsm, .xlsx, .xltm, .xltx, and .ods.

Note

File extensions .xlsb and .ods are only supported on platforms with Excel® for Windows®.

Data Types: char | string

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: 'FileType','text'

Parameters

collapse all

Type of file, specified as the comma-separated pair consisting of 'FileType' and either 'text' or 'spreadsheet'.

Specify the 'FileType' name-value pair argument when the filename does not include the file extension or if the extension is other than one of these:

  • .txt, .dat, or .csv for delimited text files

  • .xls, .xlsb, .xlsm, .xlsx, .xltm, .xltx, or .ods for spreadsheet files

Note

File extensions .xlsb and .ods are only supported on platforms with Excel for Windows.

Example: 'FileType','text'

Data Types: char | string

Type for imported text data, specified as the comma-separated pair consisting of 'TextType' and either 'char' or 'string'.

  • 'char' — Import text data into MATLAB as character vectors.

  • 'string' — Import text data into MATLAB as string arrays.

Example: 'TextType','char'

Type for imported date and time data, specified as the comma-separated pair consisting of 'DatetimeType' and one of these values: 'datetime', 'text', or 'exceldatenum'. The value 'exceldatenum' is applicable only for spreadsheet files, and is not valid for text files.

ValueType for Imported Date and Time Data
'datetime'

MATLAB datetime data type

For more information, see datetime.

'text'

If 'DatetimeType' is specified as 'text', then the type for imported date and time data depends on the value specified in the 'TextType' parameter:

  • If 'TextType' is set to 'char', then the importing function returns dates as a cell array of character vectors.

  • If 'TextType' is set to 'string', then the importing function returns dates as an array of strings.

'exceldatenum'

Excel serial date numbers

A serial date number is a single number equal to the number of days from a given reference date. Excel serial date numbers use a different reference date than MATLAB serial date numbers. For more information on Excel dates, see https://support.microsoft.com/en-us/kb/214330.

Data Types: char | string

Expected number of variables, specified as the comma-separated pair consisting of 'ExpectedNumVariables' and a positive integer. If unspecified, the importing function automatically detects the number of variables.

Data Types: single | double

Portion of the data to read from text or spreadsheet files, specified as the comma separated pair consisting of 'Range' and a character vector, string scalar, or numeric vector in one of these forms.

Ways to specify RangeDescription

Starting Cell

'Cell' or [row col]

Specify the starting cell for the data as a character vector or string scalar or a two element numeric vector.

  • Character vector or string scalar containing a column letter and row number using Excel A1 notation. For example, A5 is the identifier for the cell at the intersection of column A and row 5.

  • Two element numeric vector of the form [row col] indicating the starting row and column.

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' or [5 1]

Rectangular Range

'Corner1:Corner2' or [r1 c1 r2 c2]

Specify the exact range to read using the rectangular range in one of these forms.

  • 'Corner1:Corner2' — Specify the range using Corner1 and Corner2 which are the two opposing corners that define the region to read in Excel A1 notation. For example, 'C2:N15'.

  • [r1 c1 r2 c2] — Specify the range using a four element numeric vector containing start-row, start-column, end-row, and end-column. For example, [2 3 15 13].

The importing function only reads the data contained in the specified range. Any empty fields within the specified range are imported as missing cells.

Row Range or Column Range

'Row1:Row2' or 'Column1:Column2'

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

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

Example: 'A:K'

Starting Row Number

n

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

Excel’s Named Range

'NamedRange'

In Excel, you can create names to identify ranges in the spreadsheet. For instance, you can select a rectangular portion of the spreadsheet and call it 'myTable'. If such named ranges exist in a spreadsheet, then the importing function can read that range using its name.

Example: 'Range','myTable'

Unspecified or Empty

''

If unspecified, the importing function automatically detects the used range.

Example: 'Range',''

Note: Used Range refers to the rectangular portion of the spreadsheet that actually contains data. The importing function automatically detects the used range by trimming any leading and trailing rows and columns that do not contain data. Text that is only white space is considered data and is captured within the used range.

Data Types: char | string | double

Number of header lines in the file, specified as the comma-separated pair consisting of 'NumHeaderLines' and a positive integer. If unspecified, the importing function automatically detects the number of header lines in the file.

Example: 'NumHeaderLines',7

Data Types: single | double

Indicator for reading the first row as variable names, specified as the comma-separated pair consisting of 'ReadVariableNames' and either true or false. If unspecified, readtable automatically detects the presence of variable names.

Indicator

Description

true

Use when the first row of the region to read contains the variable names for the table. readtable creates a variable, with the detected variable name, for each column in T.

false

Use when the first row of the region to read contains data in the table. readtable creates default variable names of the form 'Var1',...,'VarN', where N is the number of variables.

unspecified When left unspecified, the importing function automatically detects true or false and proceeds accordingly.

Data Types: logical

Indicator for reading first column as row names, specified as the comma-separated pair consisting of 'ReadRowNames' and either false or true.

Indicator

Description

false

Use when the first column of the region to read contains data, and not the row names for the table.

true

Use when the first column of the region to read contains the row names for the table.

unspecifiedWhen left unspecified, the importing function assumes false.

Data Types: logical

Parameters for Spreadsheet Files Only

collapse all

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.

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.

Data Types: char | string | single | double

Parameters for Text Files Only

collapse all

Field delimiter characters, specified as a character vector, string scalar, cell array of character vectors, or string array.

Example: 'Delimiter','|'

Example: 'Delimiter',{';','*'}

Data Types: char | string | cell

Characters to treat as white space, specified as a character vector or string scalar containing one or more characters.

Example: 'Whitespace',' _'

Example: 'Whitespace','?!.,'

End-of-line characters, specified as a character vector, string scalar, cell array of character vectors, or string array.

Example: 'LineEnding','\n'

Example: 'LineEnding','\r\n'

Example: 'LineEnding',{'\b',':'}

Data Types: char | string | cell

Character encoding scheme associated with the file, specified as the comma-separated pair consisting of 'Encoding' and 'system' or a standard character encoding scheme name, such as one of the values in this table.

'Big5'

'ISO-8859-1'

'windows-847'

'Big5-HKSCS'

'ISO-8859-2'

'windows-949'

'CP949'

'ISO-8859-3'

'windows-1250'

'EUC-KR'

'ISO-8859-4'

'windows-1251'

'EUC-JP'

'ISO-8859-5'

'windows-1252'

'EUC-TW'

'ISO-8859-6'

'windows-1253'

'GB18030'

'ISO-8859-7'

'windows-1254'

'GB2312'

'ISO-8859-8'

'windows-1255'

'GBK'

'ISO-8859-9'

'windows-1256'

'IBM866'

'ISO-8859-11'

'windows-1257'

'KOI8-R'

'ISO-8859-13'

'windows-1258'

'KOI8-U'

'ISO-8859-15'

'US-ASCII'

 

'Macintosh'

'UTF-8'

 

'Shift_JIS'

 

Example: 'Encoding','system' uses the system default encoding.

Data Types: char | string

Style of comments, specified as a character vector, string scalar, cell array of character vectors, or string array.

For example, to ignore the text following a percent sign on the same line, specify CommentStyle as '%'.

Example: 'CommentStyle',{'/*'}

Data Types: char | string | cell

Output data type of duration data from text files, specified as the comma-separated pair consisting of 'DurationType' and either 'duration' or 'text'.

ValueType for Imported Duration Data
'duration'

MATLAB duration data type

For more information, see duration.

'text'

If 'DurationType' is specified as 'text', then the type for imported duration data depends on the value specified in the 'TextType' parameter:

  • If 'TextType' is set to 'char', then the importing function returns duration data as a cell array of character vectors.

  • If 'TextType' is set to 'string', then the importing function returns duration data as an array of strings.

Data Types: char | string

Procedure to handle consecutive delimiters, specified as one of the values in this table.

Consecutive Delimiters RuleBehavior
'split'Split the consecutive delimiters into multiple fields.
'join'Join the delimiters into one delimiter.
'error'Return an error and abort the import operation.

Data Types: char | string

Procedure to manage leading delimiters, specified as one of the values in this table.

Leading Delimiters RuleBehavior
'keep'Keep the delimiter.
'ignore'Ignore the delimiter.
'error'Return an error and abort the import operation.

Text to interpret as missing data, specified as a character vector, string scalar, cell array of character vectors, or string array.

When the importing function finds missing instances, it uses the specification in the MissingRule property to determine the appropriate action.

Example: 'TreatAsMissing',{'NA','TBD'} instructs the importing function to treat any occurrence of NA or TBD as a missing fields.

Data Types: char | string | cell

Characters that indicate the thousands grouping in numeric variables, specified as a character vector or string scalar. The thousands grouping characters act as visual separators, grouping the number at every three place values. The importing function uses the characters in the ThousandsSeparator property to interpret the numbers being imported.

Data Types: char | string

Characters indicating the decimal separator in numeric variables, specified as a character vector or string scalar. The importing function uses the DecimalSeparator property to distinguish the integer part of a number from the decimal part.

When converting to integer data types, numbers with a decimal part are rounded to the nearest integer.

Data Types: char | string

Remove nonnumeric characters from a numeric variable, specified as a logical true or false.

Data Types: logical

Output Arguments

collapse all

Import options for the specified file, returned as a SpreadsheetImportOptions object or a DelimitedTextImportOptions object. The type of options object depends on the type of file specified. For text files (.txt, .dat, or .csv), the detectImportOptions function returns a DelimitedTextImportOptions or FixedWidthImportOptions object. For spreadsheet files (.xls, .xlsb, .xlsm, .xlsx, .xltm, .xltx, or .ods), the detectImportOptions function returns a SpreadsheetImportOptions object.

Based on the contents of the specified file, the detectImportOptions function automatically detects and sets these parameters.

For Spreadsheet FilesFor Text Files
VariableNames (if any)VariableNames (if any)
VariableTypesVariableTypes
DataRangeDataLine
VariableNamesRangeVariableNamesLine
 Delimiter
 LeadingDelimitersRule (if space is delimiter)
 ConsecutiveDelimitersRule (if space is delimiter)
 PartialFieldRule (for fixed-width files)

Introduced in R2016b