Main Content

detectImportOptions

Create import options based on file content

collapse all in page

Syntax

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

Description

opts = detectImportOptions(filename) locates a table in a file and returns its import options. 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, an XMLImportOptions object if filename is an XML file, an HTMLImportOptions object if filename is an HTML file, and a WordDocumentImportOptions object if filename is a Microsoft® Word document.

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.

example

Examples

collapse all

Open Live Script

Configure how readtable interprets your file using an import options object. For example, use an import options object to read only specified variables from a spreadsheet file.

First, create an import options object from a file by using detectImportOptions to detect aspects of your spreadsheet file, including variable names and types. In this case, detectImportOptions creates a SpreadsheetImportOptions object.

opts = detectImportOptions("patients.xls")
opts = 
  SpreadsheetImportOptions with properties:

   Sheet Properties:
                        Sheet: ''

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
         MergedCellColumnRule: 'placeleft'
            MergedCellRowRule: 'placetop'

   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: [1-by-10 matlab.io.VariableImportOptions] 
	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

Specify which variables to import by modifying the import options object. Then, import the specified variables using readtable with the import options object. Display the first 5 rows of the table.

opts.SelectedVariableNames = ["Systolic","Diastolic"];
T = readtable("patients.xls",opts);
T(1:5,:)
ans=5×2 table
    Systolic    Diastolic
    ________    _________

      124          93    
      109          77    
      125          83    
      117          75    
      122          80
Open Live Script

Configure how readtable interprets your file using an import options object. For example, use an import options object to read only a subset of a text file.

First, create an import options object by using detectImportOptions to detect aspects of your text file, including variable names and types, delimiters, and white-space characters. In this case, detectImportOptions creates a DelimitedTextImportOptions object.

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

   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
       TrailingDelimitersRule: 'ignore'
                EmptyLineRule: 'skip'
                     Encoding: 'ISO-8859-1'

   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: [1-by-29 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

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

Specify the subset of variables to import by modifying the import options object. Then, import the subset of data using readtable with the import options object.

opts.SelectedVariableNames = ["TaxiIn","TaxiOut"];
T = readtable("airlinesmall.csv",opts);
Open Live Script

Detect import options for a Microsoft Word document file, specify the table to import, and then read the data.

The file MaintenanceReport.docx contains two tables. The last row of the second table contains a cell with merged columns that do not match the table variables.

Detect the import options using the detectImportOptions function. Specify to read from the second table by setting TableIndex to 2.

filename = "MaintenanceReport.docx";
opts = detectImportOptions(filename,'TableIndex',2)
opts = 
  WordDocumentImportOptions with properties:

   Replacement Properties:
                MissingRule: "fill"
            ImportErrorRule: "fill"
               EmptyRowRule: "skip"
       MergedCellColumnRule: "placeleft"
          MergedCellRowRule: "placetop"
           ExtraColumnsRule: "addvars"

   Variable Import Properties: Set types by name using setvartype
              VariableNames: ["Description"    "Category"    "Urgency"    "Resolution"    "Cost"]
              VariableTypes: ["string"    "string"    "string"    "string"    "string"]
      SelectedVariableNames: ["Description"    "Category"    "Urgency"    "Resolution"    "Cost"]
            VariableOptions: [1-by-5 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
         VariableNamingRule: "preserve"

   Location Properties:
              TableSelector: "(//w:tbl)[2]"
                   DataRows: [2 Inf]
           VariableNamesRow: 1
           VariableUnitsRow: 0
    VariableDescriptionsRow: 0
             RowNamesColumn: 0

To skip reading rows that have cells with merged columns, set the MergedCellColumnRule property to 'omitrow'.

opts.MergedCellColumnRule = 'omitrow';

Read the table from the Microsoft Word document file using the readtable function with the options object.

filename = "MaintenanceReport.docx";
T = readtable(filename,opts)
T=3×5 table
                                 Description                                       Category          Urgency         Resolution          Cost  
    _____________________________________________________________________    ____________________    ________    __________________    ________

    "Items are occasionally getting stuck in the scanner spools."            "Mechanical Failure"    "Medium"    "Readjust Machine"    "$45"   
    "Loud rattling and banging sounds are coming from assembler pistons."    "Mechanical Failure"    "Medium"    "Readjust Machine"    "$35"   
    "There are cuts to the power when starting the plant."                   "Electronic Failure"    "High"      "Full Replacement"    "$16200"
Open Live Script

Detect import options for an HTML file, specify the table to import, and then read the data.

Detect the import options of the first table from the URL https://www.mathworks.com/help/matlab/text-files.html containing the text "readtable". Detect the import options using the detectImportOptions function and specify the table to read using the XPath query "//TABLE[contains(.,'readtable')]". Specify to not read variable names by setting ReadVariableNames to false.

url = "https://www.mathworks.com/help/matlab/text-files.html";
opts = detectImportOptions(url,'TableSelector',"//TABLE[contains(.,'readtable')]",'ReadVariableNames',false)
opts = 
  HTMLImportOptions with properties:

   Replacement Properties:
                MissingRule: "fill"
            ImportErrorRule: "fill"
               EmptyRowRule: "skip"
       MergedCellColumnRule: "placeleft"
          MergedCellRowRule: "placetop"
           ExtraColumnsRule: "addvars"

   Variable Import Properties: Set types by name using setvartype
              VariableNames: ["Var1"    "Var2"]
              VariableTypes: ["string"    "string"]
      SelectedVariableNames: ["Var1"    "Var2"]
            VariableOptions: Show all 2 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts
         VariableNamingRule: "preserve"

   Location Properties:
              TableSelector: "//TABLE[contains(.,'readtable')]"
                   DataRows: [1 Inf]
           VariableNamesRow: 0
           VariableUnitsRow: 0
    VariableDescriptionsRow: 0
             RowNamesColumn: 0

Read the table using the readtable function.

T = readtable(url,opts)
T=4×2 table
          Var1                      Var2            
    ________________    ____________________________

    "readtable"         "Create table from file"    
    "writetable"        "Write table to file"       
    "readtimetable"     "Create timetable from file"
    "writetimetable"    "Write timetable to file"
Open Live Script

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)
T: 1468x2 table

Variables:

    Region: cell array of character vectors
    Cause: cell array of character vectors

Statistics for applicable variables:

              NumMissing

    Region        0     
    Cause         0

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)
T: 1468x2 table

Variables:

    Region: string
    Cause: string

Statistics for applicable variables:

              NumMissing

    Region        0     
    Cause         0
Open Live Script

Import the contents of an XML file into a table.

The students.xml file has seven sibling nodes named Student, which each contain the same child nodes and attributes. 

type students.xml
<?xml version="1.0" encoding="utf-8"?>
<Students>
    <Student ID="S11305">
        <Name FirstName="Priya" LastName="Thompson" />
        <Age>18</Age>
        <Year>Freshman</Year>
        <Address>
            <Street xmlns="https://www.mathworks.com">591 Spring Lane</Street>
            <City>Natick</City>
            <State>MA</State>
      </Address>
      <Major>Computer Science</Major>
      <Minor>English Literature</Minor>
   </Student>
   <Student ID="S23451">
        <Name FirstName="Conor" LastName="Cole" />
        <Age>18</Age>
        <Year>Freshman</Year>
        <Address>
            <Street xmlns="https://www.mathworks.com">4641 Pearl Street</Street>
            <City>San Francisco</City>
            <State>CA</State>
        </Address>
        <Major>Microbiology</Major>
        <Minor>Public Health</Minor>
    </Student>
    <Student ID="S119323">
        <Name FirstName="Morgan" LastName="Yang" />
        <Age>21</Age>
        <Year>Senior</Year>
        <Address>
            <Street xmlns="https://www.mathworks.com">30 Highland Road</Street>
            <City>Detriot</City>
            <State>MI</State>
        </Address>
        <Major>Political Science</Major>
   </Student>
   <Student ID="S201351">
        <Name FirstName="Salim" LastName="Copeland" />
        <Age>19</Age>
        <Year>Sophomore</Year>
        <Address>
            <Street xmlns="https://www.mathworks.com">3388 Moore Avenue</Street>
            <City>Fort Worth</City>
            <State>TX</State>
        </Address>
        <Major>Business</Major>
        <Minor>Japanese Language</Minor>
   </Student>
   <Student ID="S201351">
        <Name FirstName="Salim" LastName="Copeland" />
        <Age>20</Age>
        <Year>Sophomore</Year>
        <Address>
            <Street xmlns="https://www.mathworks.com">3388 Moore Avenue</Street>
            <City>Fort Worth</City>
            <State>TX</State>
        </Address>
        <Major>Business</Major>
        <Minor>Japanese Language</Minor>
    </Student>
    <Student ID="54600">
        <Name FirstName="Dania" LastName="Burt" />
        <Age>22</Age>
        <Year>Senior</Year>
        <Address>
            <Street xmlns="https://www.mathworks.com">22 Angie Drive</Street>
            <City>Los Angeles</City>
            <State>CA</State>
        </Address>
        <Major>Mechanical Engineering</Major>
        <Minor>Architecture</Minor>
   </Student>
    <Student ID="453197">
        <Name FirstName="Rikki" LastName="Gunn" />
        <Age>21</Age>
        <Year>Junior</Year>
        <Address>
            <Street xmlns="https://www.mathworks.com">65 Decatur Lane</Street>
            <City>Trenton</City>
            <State>ME</State>
        </Address>
        <Major>Economics</Major>
        <Minor>Art History</Minor>
   </Student>
</Students>

First, create an XMLImportOptions object by using detectImportOptions to detect aspects of your XML file. Read just the street names into a table by specifying the VariableSelectors name-value argument as the XPath expression of the Street element node. Register a custom namespace prefix to the existing namespace URL by setting the RegisteredNamespaces name-value argument.

opts = detectImportOptions("students.xml",RegisteredNamespaces=["myPrefix","https://www.mathworks.com"], ...
    VariableSelectors="//myPrefix:Street");

Then, import the specified variable using readtable with the import options object. 

T = readtable("students.xml",opts)
T=7×1 table
          Street       
    ___________________

    "591 Spring Lane"  
    "4641 Pearl Street"
    "30 Highland Road" 
    "3388 Moore Avenue"
    "3388 Moore Avenue"
    "22 Angie Drive"   
    "65 Decatur Lane"

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'

Internet URL

If the file is specified as an internet uniform resource locator (URL), then filename must contain the protocol type 'http://' or 'https://'.

Example: 'http://hostname/path_to_file/my_data.csv'

Remote Location

If the file is stored at a remote location, then filename must contain the full path of the file specified with the form:

scheme_name://path_to_file/my_file.ext

Based on the 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, .ods, .xml, .docx, .html, .xhtml, and .htm.

Note

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

Data Types: char | string

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Before R2021a, use commas to separate each name and value, and enclose Name in quotes.

Example: 'FileType','spreadsheet'

All Supported File Types

collapse all

Type of file, specified as the comma-separated pair consisting of 'FileType' and one of these values.

ValueImport Options for File
'spreadsheet'

Return a SpreadsheetImportOptions object.

'text'

Return a DelimitedTextImportOptions or FixedWidthImportOptions object depending on the layout of the text file.

'delimitedtext'

Return a DelimitedTextImportOptions object. Use this option for text files that have columns separated by delimiters.

'fixedwidth'

Return a FixedWidthImportOptions object. Use this option for text files that have columns of fixed widths.

'xml'

Return an XMLImportOptions object. Use this option for XML files.

'worddocument'

Return a WordDocumentImportOptions object. Use this option for Microsoft Word document files.

'html'

Return a HTMLImportOptions object. Use this option for HTML files.

Use the 'FileType' name-value pair argument when filename does not include the file extension, or when the extension is not one of these:

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

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

  • .xml, for XML files

  • .docx for Microsoft Word document files

  • .html, .xhtml, or .htm for HTML files

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 one of these values:

  • "string" — Import text data as string arrays.

  • "char" — Import text data as character vectors.

Example: "TextType","char"

Type for imported date and time data, specified as one of these values:

ValueDescription
"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

The value "exceldatenum" is applicable only for spreadsheet files, and is not valid for text files. 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 Differences between the 1900 and the 1904 date system in Excel.

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

Flag to preserve variable names, specified as either "modify" or "preserve".

  • "modify" — Convert invalid variable names (as determined by the isvarname 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

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

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

'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

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

'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

HTTP or HTTPS request options, specified as a weboptions object. The weboptions object determines how to import data when the specified filename is an internet URL containing the protocol type "http://" or "https://".

Text and Spreadsheet Files

collapse all

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

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

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

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

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 byBehavior

'Cell' or n

Starting Cell or Starting Row

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.

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: 'A5' or 5

'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' or 'Column1:Column2'

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

Example: 'A:K'

[n1 n2; n3 n4;...]

Multiple Row Ranges

Specify multiple row ranges to read with an N-by-2 array containing N different row ranges.

A valid array of multiple row ranges must:

  • Specify line ranges in an increasing order, that is the first row range specified in the array appears in the file before the other row ranges.

  • Contain only non-overlapping row ranges.

Use of Inf is only supported to indicate the last range in the numeric array specifying multiple row ranges. For example, [1 3; 5 6; 8 Inf].

Example: [1 3; 5 6; 8 Inf]

''

Unspecified or Empty

Do not fetch any data.

Example: ''

Data Types: char | string | cell | single | double

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

'Row1:Row2'

Row Range

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

Row names must be in a single column.

Example: '5:50'

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, string scalar, 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 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 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 | string | single | double

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 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 | string | single | double

Since R2024b

Rule for cells merged across columns, specified as one of the values in this table.

Import RuleBehavior
"placeleft"

Place the data in the leftmost cell and fill the remaining cells with the contents of the FillValue property.

You can specify the FillValue property in the VariableImportOptions object of the variable being imported. For more information on setting the FillValue property, see setvaropts.

"placeright"

Place the data in the rightmost cell and fill the remaining cells with the contents of the FillValue property.

You can specify the FillValue property in the VariableImportOptions object of the variable being imported. For more information on setting the FillValue property, see setvaropts.

"duplicate"

Duplicate the data in all cells.

"omitrow"Omit rows where merged cells occur.
"error"Display an error message and cancel the import operation.

Since R2024b

Rule for cells merged across rows, specified as one of the values in this table.

Import RuleBehavior
"placetop"

Place the data in the top cell and fill the remaining cells with the contents of the FillValue property.

You can specify the FillValue property in the VariableImportOptions object of the variable being imported. For more information on setting the FillValue property, see setvaropts.

"placebottom"

Place the data in the bottom cell and fill the remaining cells with the contents of the FillValue property.

You can specify the FillValue property in the VariableImportOptions object of the variable being imported. For more information on setting the FillValue property, see setvaropts.

"duplicate"

Duplicate the data in all cells.

"omitvar"Omit variables where merged cells occur.
"error"Display an error message and cancel the import operation.

Parameters for Text Files Only

collapse all

Field delimiter characters in a delimited text file, specified as a string array, character vector, or cell array of character vectors.

Example: "Delimiter","|"

Example: "Delimiter",[";","*"]

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

ValueBehavior
"keep"Keep the delimiter.
"ignore"Ignore the delimiter.
"error"Return an error and cancel the import operation.

Procedure to manage trailing delimiters in a delimited text file, 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.

Procedure to manage consecutive delimiters in a delimited text file, specified as one of the values in this table.

ValueBehavior
"split"Split the consecutive delimiters into multiple fields.
"join"Join the delimiters into one delimiter.
"error"Return an error and cancel the import operation.

Field widths of variables in a fixed-width text file, specified as a vector of positive integer values. Each positive integer in the vector corresponds to the number of characters in a field that makes up the variable. The VariableWidths property contains an entry corresponding to each variable specified in the VariableNames property.

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 string array, character vector, or cell array of character vectors.

Example: "LineEnding","\n"

Example: "LineEnding","\r\n"

Example: "LineEnding",["\b",":"]

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.

When you do not specify any encoding, the function uses automatic character set detection to determine the encoding when reading the file.

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

Data Types: char | string

Style of comments, specified as a string array, character vector, or cell array of character vectors. For single- and multi-line comments, the starting identifier must be the first non-white-space character. For single-line comments, specify a single identifier to treat lines starting with the identifier as comments. For multi-line comments, lines from the starting (first) identifier to the ending (second) identifier are treated as comments. No more than two character vectors of identifiers can be specified.

For example, to ignore the line following a percent symbol as the first non-white-space character, specify CommentStyle as "%".

Example: "CommentStyle",["/*"]

Example: "CommentStyle",["/*","*/"]

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 extra columns in the data, specified as one of the values in this table.

Extra Columns RuleBehavior
'addvars'

To import extra columns, create new variables. If there are N extra columns, then import new variables as 'ExtraVar1', 'ExtraVar2',..., 'ExtraVarN'. Extra columns of data are imported as if their VariableTypes are char.

'ignore'Ignore the extra columns of data.
'wrap'Wrap the extra columns of data to new records. This action does not change the number of variables.
'error'Display an error message and abort the import operation.

Data Types: char | string

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

Locale for reading dates, specified as the comma-separated pair consisting of 'DateLocale' and a character vector or a string scalar of the form xx_YY, where:

  • YY is an uppercase ISO 3166-1 alpha-2 code indicating a country.

  • xx is a lowercase ISO 639-1 two-letter code indicating a language.

This table lists some common values for the locale.

Locale LanguageCountry
'de_DE'GermanGermany
'en_GB'EnglishUnited Kingdom
'en_US'EnglishUnited States
'es_ES'SpanishSpain
'fr_FR'FrenchFrance
'it_IT'ItalianItaly
'ja_JP'JapaneseJapan
'ko_KR'KoreanKorea
'nl_NL'DutchNetherlands
'zh_CN'Chinese (simplified)China

When using the %D format specifier to read text as datetime values, use DateLocale to specify the locale in which the importing function should interpret month and day-of-week names and abbreviations.

If you specify the DateLocale argument in addition to opts the import options, then the importing function uses the specified value for the DateLocale argument, overriding the locale defined in the import options.

Example: 'DateLocale','ja_JP'

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 data type of hexadecimal data, specified as the comma-separated pair consisting of 'HexType' and one of the values listed in the table.

The input file represents hexadecimal values as text, using either 0x or 0X as a prefix and the characters 0-9, a-f, and A-F as digits. (Uppercase and lowercase letters represent the same digits—for example, '0xf' and '0xF' both represent 15.)

The importing function converts the hexadecimal values to the data type specified by the value of 'HexType'.

Value of 'HexType'

Data Type of Output Table Variables

'auto'

data type detected automatically

'text'

unaltered input text

'int8'

8-bit integer, signed

'int16'

16-bit integer, signed

'int32'

32-bit integer, signed

'int64'

64-bit integer, signed

'uint8'

8-bit integer, unsigned

'uint16'

16-bit integer, unsigned

'uint32'

32-bit integer, unsigned

'uint64'

64-bit integer, unsigned

Example: 'HexType','uint16' converts text representing hexadecimal values (such as '0xFF') to unsigned 16-bit integers (such as 255) in the output table.

Data Types: char | string

Output data type of binary data, specified as the comma-separated pair consisting of 'BinaryType' and one of the values listed in the table.

The input file represents binary values as text, using either 0b or 0B as a prefix and the characters 0 and 1 as digits.

The importing function converts the binary values to the data type specified by the value of 'BinaryType'.

Value of 'BinaryType'

Data Type of Output Table Variables

'auto'

data type detected automatically

'text'

unaltered input text

'int8'

8-bit integer, signed

'int16'

16-bit integer, signed

'int32'

32-bit integer, signed

'int64'

64-bit integer, signed

'uint8'

8-bit integer, unsigned

'uint16'

16-bit integer, unsigned

'uint32'

32-bit integer, unsigned

'uint64'

64-bit integer, unsigned

Example: 'BinaryType','uint16' converts text representing binary values (such as '0b11111111') to unsigned 16-bit integers (such as 255) in the output table.

Data Types: char | string

XML Files Only

collapse all

Import attributes, specified as the comma-separated pair consisting of 'ImportAttributes' and either 1 (true) or 0 (false). If you specify false, then the reading function will not import the XML attributes in the input file as variables in the output table.

Example: 'ImportAttributes',false

Attribute suffix, specified as the comma-separated pair consisting of 'AttributeSuffix' and either a character vector or string scalar. This argument specifies the suffix the reading function appends to all table variables that correspond to attributes in the input XML file. If you do not specify 'AttributeSuffix', then the reading function defaults to appending the suffix 'Attribute' to all variable names corresponding to attributes in the input XML file.

Example: 'AttributeSuffix','_att'

Table row XML node name, specified as the comma-separated pair consisting of 'RowNodeName' and either a character vector or string scalar. This argument specifies the XML node name that delineates rows of the output table.

Example: 'RowNodeName','XMLNodeName'

Table row XPath expression, specified as a character vector or string scalar that the reading function uses to select individual rows of the output table. You must specify RowSelector as a valid XPath version 1.0 expression.

Example: 'RowSelector','/RootNode/ChildNode'

Table variable XML node names, specified as the comma-separated pair consisting of 'VariableNodeNames' and either a cell array of character vectors or string array. This argument specifies the XML node name that the reading function uses to identify the XML nodes to read as variables in the output table.

Example: 'VariableNodeNames',{'XMLNodeName1','XMLNodeName2'}

Example: 'VariableNodeNames',"XMLNodeName"

Example: 'VariableNodeNames',["XMLNodeName1","XMLNodeName2"]

Table variable XPath expressions, specified as a cell array of character vectors or string array that the reading function uses to select table variables. You must specify VariableSelectors as valid XPath version 1.0 expressions.

Example: 'VariableSelectors',{'/RootNode/ChildNode'}

Example: 'VariableSelectors',"/RootNode/ChildNode"

Example: 'VariableSelectors',["/RootNode/ChildNode1","/RootNode/ChildNode2"]

Table XML node name, specified as the comma-separated pair consisting of 'TableNodeName' and either a character vector or string scalar. This argument specifies the node in the input structure that the reading function should read to a table.

Example: 'TableNodeName','NodeName'

Variable units XPath, specified as a character vector or string scalar that the reading function uses to select the table variable units. You must specify VariableUnitsSelector as a valid XPath version 1.0 expression.

Example: 'VariableUnitsSelector','/RootNode/ChildNode'

Variable descriptions XPath expression, specified as a character vector or string scalar that the reading function reads uses to select the table variable descriptions. You must specify VariableDescriptionsSelector as a valid XPath version 1.0 expression.

Example: 'VariableDescriptionsSelector','/RootNode/ChildNode'

Table row names XPath expression, specified as a character vector or string scalar that the reading function uses to select the names of the table rows. You must specify RowNamesSelector as a valid XPath version 1.0 expression.

Example: 'RowNamesSelector','/RootNode/ChildNode'

Procedure to handle repeated XML nodes in a given row of a table, specified as 'addcol', 'ignore', or 'error'.

Repeated Node Rule

Behavior

'addcol'

Add columns for the repeated nodes under the variable header in the table. Specifying the value of 'RepeatedNodeRule' as 'addcol' does not create a separate variable in the table for the repeated node.

'ignore'

Skip importing the repeated nodes.

'error'Display an error message and abort the import operation.

Example: 'RepeatedNodeRule','ignore'

Set of registered XML namespace prefixes, specified as the comma-separated pair consisting of RegisteredNamespaces and an array of prefixes. The reading function uses these prefixes when evaluating XPath expressions on an XML file. Specify the namespace prefixes and their associated URLs as an Nx2 string array. RegisteredNamespaces can be used when you also evaluate an XPath expression specified by a selector name-value argument, such as StructSelector for readstruct, or VariableSelectors for readtable and readtimetable.

By default, the reading function automatically detects namespace prefixes to register for use in XPath evaluation, but you can also register new namespace prefixes using the RegisteredNamespaces name-value argument. You might register a new namespace prefix when an XML node has a namespace URL, but no declared namespace prefix in the XML file.

For example, evaluate an XPath expression on an XML file called example.xml that does not contain a namespace prefix. Specify 'RegisteredNamespaces' as ["myprefix", "https://www.mathworks.com"] to assign the prefix myprefix to the URL https://www.mathworks.com.

T = readtable("example.xml", "VariableSelector", "/myprefix:Data",...
 "RegisteredNamespaces", ["myprefix", "https://www.mathworks.com"])

Example: 'RegisteredNamespaces',["myprefix", "https://www.mathworks.com"]

Microsoft Word Document and HTML Files Only

collapse all

Index of table to read from Microsoft Word document or HTML file containing multiple tables, specified as a positive integer.

When you specify TableIndex, the software automatically sets TableSelector to the equivalent XPath expression.

Data Types: single | double | int8 | int16 | int32 | int64 | uint8 | uint16 | uint32 | uint64

Rule for cells merged across columns, specified as one of the values in this table.

Import RuleBehavior
"placeleft"

Place the data in the leftmost cell and fill the remaining cells with the contents of the FillValue property.

You can specify the FillValue property in the VariableImportOptions object of the variable being imported. For more information on setting the FillValue property, see setvaropts.

"placeright"

Place the data in the rightmost cell and fill the remaining cells with the contents of the FillValue property.

You can specify the FillValue property in the VariableImportOptions object of the variable being imported. For more information on setting the FillValue property, see setvaropts.

"duplicate"

Duplicate the data in all cells.

"omitrow"Omit rows where merged cells occur.
"error"Display an error message and cancel the import operation.

Rule for cells merged across rows, specified as one of the values in this table.

Import RuleBehavior
"placetop"

Place the data in the top cell and fill the remaining cells with the contents of the FillValue property.

You can specify the FillValue property in the VariableImportOptions object of the variable being imported. For more information on setting the FillValue property, see setvaropts.

"placebottom"

Place the data in the bottom cell and fill the remaining cells with the contents of the FillValue property.

You can specify the FillValue property in the VariableImportOptions object of the variable being imported. For more information on setting the FillValue property, see setvaropts.

"duplicate"

Duplicate the data in all cells.

"omitvar"Omit variables where merged cells occur.
"error"Display an error message and cancel the import operation.

Row containing variable names, specified as a nonnegative integer.

  • If you do not specify VariableNamesRow, then the software reads variable names according to the ReadVariableNames argument.

  • If VariableNamesRow is 0, then the software does not import the variable names.

  • Otherwise, the software imports the variable names from the specified row.

Data Types: single | double | int8 | int16 | int32 | int64 | uint8 | uint16 | uint32 | uint64

Row containing variable units, specified as a nonnegative integer.

If VariableUnitsRow is 0, then the software does not import the variable units. Otherwise, the software imports the variable units from the specified row.

Data Types: single | double | int8 | int16 | int32 | int64 | uint8 | uint16 | uint32 | uint64

Row containing variable descriptions, specified as a nonnegative integer.

If VariableDescriptionsRow is 0, then the software does not import the variable descriptions. Otherwise, the software imports the variable descriptions from the specified row.

Data Types: single | double | int8 | int16 | int32 | int64 | uint8 | uint16 | uint32 | uint64

Rule to apply to empty rows in the table, specified as one of the following:

  • "skip" – Skip empty rows.

  • "read" – Read empty rows.

  • "error" – Ignore empty rows during table detection and error when reading.

Rule to apply to empty columns in the table, specified as one of the following:

  • "skip" – Skip empty columns.

  • "read" – Read empty columns.

  • "error" – Ignore empty columns during table detection and error when reading.

Microsoft Word Document, HTML, and XML Files Only

collapse all

Table data XPath expression, specified as a character vector or string scalar that the reading function uses to select the output table data. You must specify TableSelector as a valid XPath version 1.0 expression.

Example: 'TableSelector','/RootNode/ChildNode'

Output Arguments

collapse all

Import options for the specified file, returned as a SpreadsheetImportOptions, DelimitedTextImportOptions, FixedWidthImportOptions, or XMLImportOptions 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. For XML files (.xml), detectImportOptions returns an XMLImportOptions object.

Tips

  • Updating Property Values After Creating the Import Options Object: Use of dot notation is not recommended to update the properties of the import options object created by detectImportOptions. When you set properties using dot notation, MATLAB does not re-detect all the import options for the file. Therefore, to update and re-detect all the properties, you must specify the new values by using name-value arguments. For example, update the value for the ConsecutiveDelimitersRule property and re-detect the import options as follows. 

    opts = detectImportOptions(__,'ConsecutiveDelimitersRule','join')

  • Use XPath selectors to specify which elements of the XML input document to import. For example, suppose you want to import the XML file myFile.xml, which has the following structure:

    <data>
    <table category="ones">
        <var>1</var>
        <var>2</var>
    </table>
    <table category="tens">
        <var>10</var>
        <var>20</var>
    </table>
</data>
    This table provides the XPath syntaxes that are supported for XPath selector name-value arguments, such as VariableSelectors or TableSelector.

    Selection OperationSyntaxExampleResult
    Select every node whose name matches the node you want to select, regardless of its location in the document.Prefix the name with two forward slashes (//). 
    data = readtable('myFile.xml', 'VariableSelectors', '//var')
    		 
    data =

  4×1 table

    var
    ___

     1 
     2 
    10 
    20
    Read the value of an attribute belonging to an element node.Prefix the attribute with an at sign (@). 
    data = readtable('myFile.xml', 'VariableSelectors', '//table/@category')
    		 
    data =

  2×1 table

    categoryAttribute
    _________________

         "ones"      
         "tens"
    Select a specific node in a set of nodes.Provide the index of the node you want to select in square brackets ([]). 
    data = readtable('myFile.xml', 'TableSelector', '//table[1]')
    		 
    data =

  2×1 table

    var
    ___

     1 
     2
    Specify precedence of operations.Add parentheses around the expression you want to evaluate first. 
    data = readtable('myFile.xml', 'VariableSelectors', '//table/var[1]')
    		 
    data =

  2×1 table

    var
    ___

     1 
    10 
    data = readtable('myFile.xml', 'VariableSelectors', '(//table/var)[1]')
    		 
    data =

  table

    var
    ___

     1

Version History

Introduced in R2016b

expand all

See Also

| | | | | |

Topics
Go to top of page