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.

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

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

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: 'US-ASCII'

   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:
                     DataLine: 2
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0

Examine the Type property of variables TaxiIn and TaxiOut.

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

    WhitespaceRule
    Type
    FillValue
    Name
    QuoteRule
    TreatAsMissing

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. 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®.

Example: 'myFile.xlsx'

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

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 the following: 'datetime', 'text', or 'exceldatenum'. The value 'exceldatenum' is only applicable for spreadsheet files, and is not allowed for text files.

ValueType for Date and Time Data
'datetime'

MATLAB datetime data type

For more information, see datetime.

'text'

Cell array of character vectors

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

Example: 'DatetimeType','text'

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

Example: 'NumVariables',10

Data Types: single | 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

Parameters for Spreadsheet Files Only

collapse all

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

Portion of the worksheet to read, indicated as a rectangular area specified by a comma separated pair consisting of 'Range' and a character vector in one of these forms.

Ways to specify Range Description

'Corner1:Corner2'

Rectangular Range

Specify the range using the syntax 'Corner1:Corner2', where Corner1 and Corner2 are two opposing corners that define the region. For example, 'D2:H4' represents the 3-by-5 rectangular region between the two corners D2 and H4 on the worksheet. The 'Range' name-value pair argument is not case-sensitive, and uses Excel A1 reference style (see Excel help).

Example: 'Range','Corner1:Corner2'

''

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.

'Row1:Row2'

Row Range

You can identify the range by specifying the beginning and ending rows using Excel row designators. Then readtable automatically detects the used column range within the designated rows. For instance, the importing function interprets the range specification '1:7' as an instruction to read all columns in the used range in rows 1 through 7 (inclusive).

Example: 'Range','1:7'

'Column1:Column2'

Column Range

You can identify the range by specifying the beginning and ending columns using Excel column designators. Then readtable automatically detects the used row range within the designated columns. For instance, the importing function interprets the range specification 'A:F' as an instruction to read all rows in the used range in columns A through F (inclusive).

Example: 'Range','A:F'

'NamedRange'

Excel’s Named Range

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'

Parameters for Text Files Only

collapse all

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

Example: '|'

Example: {';','*'}

Data Types: char | cell

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

Example: ' _'

Example: '?!.,'

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

Example: '\n'

Example: '\r\n'

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

Data Types: char | 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 like 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: 'system' uses the system default encoding.

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

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

Example: {'/*'}

Data Types: char | cell

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

Was this topic helpful?