Main Content

readtable

Create table from file

Description

example

T = readtable(filename) creates a table by reading column oriented data from a file.

readtable determines the file format from the file extension:

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

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

  • .xml for Extensible Markup Language (XML) files

  • .docx for Microsoft® Word document files

  • .html, .xhtml, or .htm for Hypertext Markup Language (HTML) files

For text and spreadsheet files, readtable creates one variable in T for each column in the file and reads variable names from the first row of the file. For XML files, readtable creates one variable in T for each element or attribute node detected as a table variable. Variable names correspond to element and attribute names. For Microsoft Word document files, readtable, by default, imports data from the first table in the document and creates one variable in T for each column in the file and reads variable names from the first row of the table. For HTML files, readtable, by default, imports data from the first <TABLE> element and creates one variable in T for each column in the file and reads variable names from the first row of the table.

By default, readtable creates variables that have data types that are appropriate for the data values detected in each column of the input file.

example

T = readtable(filename,opts) creates a table using the import options opts.

example

T = readtable(___,Name,Value) creates a table from a file with additional options specified by one or more name-value pair arguments. For example, you can specify whether readtable reads the first row of the file as variable names or as data.

To set specific import options for your data, you can either use the opts object or you can specify name-value pairs. When you specify name-value pairs in addition to opts, then readtable supports only these name-value pairs:

  • Text and spreadsheet parameters — ReadVariableNames, ReadRowNames

  • Text only parameters — DateLocale, Encoding

  • Spreadsheet only parameters — Sheet, UseExcel

Examples

collapse all

Load the file myCsvTable.dat and preview its contents in a text editor. A screen shot is shown below. Notice that the file contains comma-separated column oriented data.

filename = 'myCsvTable.dat';

Create a table from the comma-separated text file. The resulting table T contains one variable for each column in the file and readtable treats the entries in the first line of the file as variable names.

T = readtable(filename)
T=5×6 table
      LastName      Gender    Age    Height    Weight    Smoker
    ____________    ______    ___    ______    ______    ______

    {'Smith'   }    {'M'}     38       71       176        1   
    {'Johnson' }    {'M'}     43       69       163        0   
    {'Williams'}    {'F'}     38       64       131        0   
    {'Jones'   }    {'F'}     40       67       133        0   
    {'Brown'   }    {'F'}     49       64       119        0   

Starting in R2020a, the readtable function read an input file as though it automatically called the detectImportOptions function on the file. It can detect data types, discard extra header lines, and fill in missing values.

For example, preview the file headersAndMissing.txt in a text editor. The file has a line with column names and another line with headers. The last two rows have gaps where the previous rows have data values.

Create a table from the file. The readtable function discards the headers. Also, it fills in gaps with appropriate missing values—a NaN value for numeric variables, and an empty character vector for text.

T = readtable('headersAndMissing.txt')
T=5×6 table
     LastName        Gender      Age    Height    Weight    Smoker
    ___________    __________    ___    ______    ______    ______

    {'Wu'     }    {'M'     }     38      71       176        1   
    {'Johnson'}    {'M'     }     43      69       163        0   
    {'Sanchez'}    {'F'     }     38      64       131        0   
    {'Brown'  }    {'F'     }    NaN      67       133        0   
    {'Picard' }    {0x0 char}    NaN      64       119        0   

To restore the default behavior from previous releases, specify the 'Format','auto' name-value pair argument. readtable reads the headers as data, and as a result, it converts all the table variables to text.

T = readtable('headersAndMissing.txt','Format','auto')
T=6×6 table
     LastName        Gender         Age        Height     Weight       Smoker   
    ___________    __________    __________    _______    _______    ___________

    {'string' }    {'string'}    {'int'   }    {'int'}    {'int'}    {'boolean'}
    {'Wu'     }    {'M'     }    {'38'    }    {'71' }    {'176'}    {'1'      }
    {'Johnson'}    {'M'     }    {'43'    }    {'69' }    {'163'}    {'0'      }
    {'Sanchez'}    {'F'     }    {'38'    }    {'64' }    {'131'}    {'0'      }
    {'Brown'  }    {'F'     }    {0x0 char}    {'67' }    {'133'}    {'0'      }
    {'Picard' }    {0x0 char}    {0x0 char}    {'64' }    {'119'}    {'0'      }

For more information, see the Compatibility Considerations section on this page.

Preview the file mySpaceDelimTable.txt in a text editor. A screen shot is shown below. Notice that the file contains space delimited, column oriented data.

Create a table from the space delimited text file. The readtable function assigns the default variable names Var1 to Var5 because the file does not contain detectable column names in its first row.

T = readtable('mySpaceDelimTable.txt')
T=3×5 table
    Var1     Var2    Var3     Var4       Var5   
    _____    ____    ____    ______    _________

    {'M'}     45      45     {'NY'}    {'true' }
    {'F'}     41      32     {'CA'}    {'false'}
    {'M'}     40      34     {'MA'}    {'false'}

Load the file myCsvTable.dat and preview its contents in a text editor. A screen shot is shown below. Notice that the file contains comma-separated column oriented data.

filename = 'myCsvTable.dat';

Create a table from the comma-separated text file. Import the first two columns as character vectors, the third column as uint32, and the next two columns as double-precision, floating-point numbers. Import the entries of the last column as character vectors.

T = readtable(filename,'Format','%s%s%u%f%f%s')
T=5×6 table
      LastName      Gender    Age    Height    Weight    Smoker
    ____________    ______    ___    ______    ______    ______

    {'Smith'   }    {'M'}     38       71       176      {'1'} 
    {'Johnson' }    {'M'}     43       69       163      {'0'} 
    {'Williams'}    {'F'}     38       64       131      {'0'} 
    {'Jones'   }    {'F'}     40       67       133      {'0'} 
    {'Brown'   }    {'F'}     49       64       119      {'0'} 

The conversion specifiers are %s for a cell array of character vectors, %f for double, and %u for uint32.

Read German dates from a file and add them to a table as English dates.

Preview the file german_dates.txt in a text editor. A screen shot is shown below. Notice that the first column of values contains dates in German and the second and third columns are numeric values.

Read the sample file using readtable. The conversion specifiers is %D dates and %f for floating-point values. Specify the file encoding using the FileEncoding name-value pair argument. Specify the format and locale of the dates using the DateLocale name-value pair argument.

T = readtable('german_dates.txt',...
    'Format','%{dd MMMM yyyy}D %f %f',...
    'FileEncoding','ISO-8859-15',...
    'DateLocale','de_DE')
T=3×3 table
          Var1          Var2    Var3 
    ________________    ____    _____

     01 January 2014    20.2    100.5
    01 February 2014    21.6    102.7
       01 March 2014    20.7     99.8

Create a table from a spreadsheet that contains variable names in the first row and row names in the first column.

T = readtable('patients.xls','ReadRowNames',true);

Display the first five rows and first four variables of the table.

T(1:5,1:4)
ans=5×4 table
                  Gender      Age              Location               Height
                __________    ___    _____________________________    ______

    Smith       {'Male'  }    38     {'County General Hospital'  }      71  
    Johnson     {'Male'  }    43     {'VA Hospital'              }      69  
    Williams    {'Female'}    38     {'St. Mary's Medical Center'}      64  
    Jones       {'Female'}    40     {'VA Hospital'              }      67  
    Brown       {'Female'}    49     {'County General Hospital'  }      64  

View the DimensionNames property of the table.

T.Properties.DimensionNames
ans = 1x2 cell
    {'LastName'}    {'Variables'}

'LastName' is the name in the first column of the first row of the spreadsheet.

Create a table using data from a specified region of the spreadsheet patients.xls. Use the data from the 5-by-3 rectangular region between the corners C2 and E6. Do not use the first row of this region as variable names.

T = readtable('patients.xls',...
    'Range','C2:E6',...
    'ReadVariableNames',false)
T = 

    Var1               Var2                Var3
    ____    ___________________________    ____

    38      'County General Hospital'      71  
    43      'VA Hospital'                  69  
    38      'St. Mary's Medical Center'    64  
    40      'VA Hospital'                  67  
    49      'County General Hospital'      64  

T contains default variable names.

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'
       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: Show all 29 VariableOptions 
	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

Examine the Type property of the 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  

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

   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  

You can read tabular data while preserving variable names that include any characters, including spaces and non-ASCII characters. First, create a table with arbitrary variable names and write the table to a text file. Then, read the tabular data back while preserving the original variable names.

Create a table containing arbitrary variable names and write the tabular data to the file 'sampletable.txt'.

LastName = {'Sanchez';'Johnson';'Li';'Diaz';'Brown'};
Age = [38;43;38;40;49];
Smoker = logical([1;0;1;0;1]);
varNames = {'Last Name','Age','Smoker (1 or 0)'};
T = table(LastName,Age,Smoker,'VariableNames',varNames)
T=5×3 table
     Last Name     Age    Smoker (1 or 0)
    ___________    ___    _______________

    {'Sanchez'}    38          true      
    {'Johnson'}    43          false     
    {'Li'     }    38          true      
    {'Diaz'   }    40          false     
    {'Brown'  }    49          true      

writetable(T,'sampletable.txt')

Read the tabular data back using readtable. By default, MATLAB® converts any variable names that include spaces and non-ASCII characters into valid MATLAB® identifiers. For example, MATLAB® converts the variable names 'Last Name' to 'LastName', and 'Smoker (1 or 0)' to 'Smoker_1or0_'. To read the tabular data while preserving variable names, set the 'VariableNamingRule' parameter to preserve.

T_preserve = readtable('sampletable.txt',"VariableNamingRule","preserve")
T_preserve=5×3 table
     Last Name     Age    Smoker (1 or 0)
    ___________    ___    _______________

    {'Sanchez'}    38            1       
    {'Johnson'}    43            0       
    {'Li'     }    38            1       
    {'Diaz'   }    40            0       
    {'Brown'  }    49            1       

You can read hexadecimal and binary numbers from a file and store them as numeric variables in a table. The readtable function automatically reads hexadecimal and binary numbers when they include the 0x and 0b prefixes respectively. The numeric values are stored using integer data types. You can also use import options to read such numbers when they do not have prefixes.

Preview the hexAndBinary.txt file in a text editor. It has columns of hexadecimal and binary numbers with prefixes, and one column without.

Read the file using readtable. The function detects the numbers with 0x and 0b prefixes and stores them as integers. The third column does not have prefixes so its values are treated as text.

T = readtable('hexAndBinary.txt')
T=3×4 table
    Var1     Var2      Var3         Var4    
    _____    ____    ________    ___________

      255    255     {'C7F9'}    {'Device1'}
      256      4     {'05BF'}    {'Device2'}
    43981    129     {'F00F'}    {'Device3'}

The readtable function stores the numeric values in different integer classes (uint16 and uint8) because T.Var1 has a value that requires more than 8 bits of storage.

class(T.Var1)
ans = 
'uint16'
class(T.Var2)
ans = 
'uint8'

To specify the data types for storing the numeric values imported from hexadecimal and binary numbers, use the 'HexType' and 'BinaryType' name-value pair arguments. Store the values as signed 32-bit integers.

T = readtable('hexAndBinary.txt','HexType','int32','BinaryType','int32');
class(T.Var1)
ans = 
'int32'
class(T.Var2)
ans = 
'int32'

You can use import options to detect hexadecimal and binary numbers without prefixes, and specify storage for them. Create an import options object for hexAndBinary.txt.

opts = detectImportOptions('hexAndBinary.txt')
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: 'UTF-8'

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

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'Var1', 'Var2', 'Var3' ... and 1 more}
                VariableTypes: {'auto', 'auto', 'char' ... and 1 more}
        SelectedVariableNames: {'Var1', 'Var2', 'Var3' ... and 1 more}
              VariableOptions: Show all 4 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

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

To specify that the third column should be imported as hexadecimal values, despite the lack of a prefix, use the setvaropts function to modify the variable type for the third variable of the table. Set the variable type to int32.

opts = setvaropts(opts,3,'NumberSystem','hex','Type','int32')
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: 'UTF-8'

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

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'Var1', 'Var2', 'Var3' ... and 1 more}
                VariableTypes: {'auto', 'auto', 'int32' ... and 1 more}
        SelectedVariableNames: {'Var1', 'Var2', 'Var3' ... and 1 more}
              VariableOptions: Show all 4 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

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

Read the file and import the third column as numeric values, not text.

T = readtable('hexAndBinary.txt',opts)
T=3×4 table
    Var1     Var2    Var3        Var4    
    _____    ____    _____    ___________

      255    255     51193    {'Device1'}
      256      4      1471    {'Device2'}
    43981    129     61455    {'Device3'}

Create XML import options for an XML file, specify the variables to import, and then read the data.

The XML file students.xml has four 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>

Create an XMLImportOptions object from a file. Specify the value of VariableSelectors as //@FirstName to select the FirstName element node to import as a table variable.

opts = xmlImportOptions("VariableSelectors","//@FirstName")
opts = 
  XMLImportOptions with properties:

   Replacement Properties:
                     MissingRule: "fill"
                 ImportErrorRule: "fill"
                RepeatedNodeRule: "addcol"

   Variable Import Properties: Set types by name using setvartype
                   VariableNames: "Var1"
                   VariableTypes: "char"
           SelectedVariableNames: "Var1"
                 VariableOptions: Show all 1 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts
              VariableNamingRule: "preserve"

   Location Properties:
                   TableSelector: <missing>
                     RowSelector: <missing>
               VariableSelectors: "//@FirstName"
           VariableUnitsSelector: <missing>
    VariableDescriptionsSelector: <missing>
                RowNamesSelector: <missing>
            RegisteredNamespaces: [0x2 string]

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

T = readtable("students.xml",opts)
T=7×1 table
       Var1   
    __________

    {'Priya' }
    {'Conor' }
    {'Morgan'}
    {'Salim' }
    {'Salim' }
    {'Dania' }
    {'Rikki' }

Register a custom XML namespace prefix to the existing namespace URL in the input file using the RegisteredNamespaces name-value argument.

Create an XMLImportOptions object from an XML file. Specify the XPath expression of the Street element node as the value of 'VariableSelectors', and register the prefix myPrefix to the URL belonging to the Street node.

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

   Replacement Properties:
                     MissingRule: "fill"
                 ImportErrorRule: "fill"
                RepeatedNodeRule: "addcol"

   Variable Import Properties: Set types by name using setvartype
                   VariableNames: "Street"
                   VariableTypes: "string"
           SelectedVariableNames: "Street"
                 VariableOptions: Show all 1 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts
              VariableNamingRule: "preserve"

   Location Properties:
                   TableSelector: <missing>
                     RowSelector: <missing>
               VariableSelectors: "//myPrefix:Street"
           VariableUnitsSelector: <missing>
    VariableDescriptionsSelector: <missing>
                RowNamesSelector: <missing>
            RegisteredNamespaces: ["myPrefix"    "https://www.mathworks.com"]

Use the readtable function along with the options object to import the selected variable.

T2 = readtable("students.xml",opts)
T2=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"  

The readtable function, by default, reads the first table from a Microsoft Word document file. 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.

Read the second table by setting the TableIndex option to 2. To skip rows that have cells with merged columns, set the MergedCellColumnRule option to 'omitrow'.

filename = "MaintenanceReport.docx";
T = readtable(filename,'TableIndex',2,'MergedCellColumnRule','omitrow')
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"

Alternatively, you can select a table using an XPath selector using the TableSelector option. To select the Microsoft Word document table that contains the text "Description", use the XPath selector "//w:tbl[contains(.,'Description')]".

T = readtable(filename, ...
    'TableSelector',"//w:tbl[contains(.,'Description')]", ...
    'MergedCellColumnRule','omitrow')
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"

Read the first table from the URL https://www.mathworks.com/help/matlab/text-files.html that contains the text "readtable" using the XPath selector "//TABLE[contains(.,'readtable')]". The table does not have a header row, so set the ReadVariableNames option to false.

url = "https://www.mathworks.com/help/matlab/text-files.html";
T = readtable(url,'TableSelector',"//TABLE[contains(.,'readtable')]", ...
    'ReadVariableNames',false)
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"   

Input Arguments

collapse all

Name of the file to read, specified as a character vector or a 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: 'dataDir\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 the importing function determines the file format from the extension. Otherwise, you must specify the 'FileType' name-value pair arguments to indicate the type of file.

  • On Windows® systems with Microsoft Excel® software, the importing function reads any Excel spreadsheet file format recognized by your version of Excel.

  • If your system does not have Excel for Windows or if you are using MATLAB Online™, the importing function operates with the UseExcel property set to false, and reads only .xls, .xlsx, .xlsm, .xltx, and .xltm files.

  • For delimited text files, the importing function converts empty fields in the file to either NaN (for a numeric variable) or an empty character vector (for a text variable). All lines in the text file must have the same number of delimiters. The importing function ignores insignificant white space in the file.

Data Types: char | string

File import options, specified as a SpreadsheetImportOptions, DelimitedTextImportOptions, FixedWidthImportOptions, XMLImportOptions, WordDocumentImportOptions, or HTMLDocumentImportOptions object created by the detectImportOptions function. The opts object contains properties that control the data import process. For more information on the properties of each object, see the appropriate object page.

Type of FilesOutput
Spreadsheet filesSpreadsheetImportOptions object (only available for the Sheet, DataRange, and VariableNames properties)
Text filesDelimitedTextImportOptions object
Fixed-width text filesFixedWidthImportOptions object
XML filesXMLImportOptions object
Microsoft Word document filesWordDocumentImportOptions object
HTML filesHTMLImportOptions object

Name-Value 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: 'ReadVariableNames',false indicates that the first row of the file does not correspond to variable names.
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
'spreadsheet'

SpreadsheetImportOptions

'text'

DelimitedTextImportOptions or FixedWidthImportOptions depending on the layout of the text file.

'delimitedtext'

DelimitedTextImportOptions, use this option for text files that have columns separated by delimiters.

'fixedwidth'

FixedWidthImportOptions, use this option for text files that have columns of fixed widths.

'xml'

XMLImportOptions, use this option for XML files.

'worddocument'

WordDocumentImportOptions, use this option for Microsoft Word document files.

'html'

HTMLImportOptions, 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

Example: 'FileType','text'

Data Types: char | string

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

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, readtable assumes false.

Note: If both the 'ReadVariableNames' and 'ReadRowNames' logical indicators are true, then readtable saves the name in the first column of the first row of the region to read as the first dimension name in the property, T.Properties.DimensionNames.

If you specify the ReadRowNames argument in addition to opts the import options , then the readtable behavior changes based on the specification:

  • If ReadRowNames is true, then read the row names from the specified file by using the RowNamesRange or the RowNameColumn property of the import options object.

  • If ReadRowNames is false, then do not import row names.

Example: 'ReadRowNames',true

Placeholder text to treat as an empty value, specified as the comma-separated pair consisting of 'TreatAsMissing' and a character vector, cell array of character vectors, string, or string array. Table elements corresponding to these characters are set to NaN.

'TreatAsMissing' only applies to numeric columns in the table, and readtable does not accept numeric literals, such as '-99'.

Example: 'TreatAsMissing','N/A' or 'TreatAsMissing',"N/A" sets N/A within numeric columns to NaN.

Example: 'TreatAsMissing',{'.','NA','N/A'} or 'TreatAsMissing',[".","NA","N/A"] sets ., NA and N/A within numeric columns to NaN.

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.

Example: 'DatetimeType','datetime'

Data Types: char | string

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

Example: "VariableNamingRule","preserve"

Data Types: char | string

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

Missing RuleBehavior
'fill'

Replace missing data with the contents of the FillValue property.

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

'error'Stop importing and display an error message showing the missing record and field.
'omitrow'Omit rows that contain missing data.
'omitvar'Omit variables that contain missing data.

Example: '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 getvaropts.

'error'Stop importing and display an error message showing the error-causing record and field.
'omitrow'Omit rows where errors occur.
'omitvar'Omit variables where errors occur.

Example: 'ImportErrorRule','omitvar'

Data Types: char | string

Text and Spreadsheet Files

collapse all

Indicator for reading the first row as variable names, specified as the comma-separated pair consisting of 'ReadVariableNames' and either true, false, 1, or 0. 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, readtable automatically detects true or false and proceeds accordingly.

Note: If both the 'ReadVariableNames' and 'ReadRowNames' logical indicators are true, then readtable saves the name in the first column of the first row of the region to read as the first dimension name in the property, T.Properties.DimensionNames.

If you specify the ReadVariableNames argument in addition to opts the import options, then the readtable behavior changes based on the specification:

  • If ReadVariableNames is true, then read the variable names from the specified file by using the VariableNamesRange or the VariableNamesLine property of the import options object.

  • If ReadVariableNames is false, then read the variable names from the VariableNames property of the import options object.

Example: 'ReadVariableNames',true

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.

Example: 'ExpectedNumVariables',5

Data Types: single | double

Text Files Only

collapse all

Field delimiter character, specified as the comma-separated pair consisting of 'Delimiter' and a character vector, a cell array of character vectors, or a string. Specify Delimiter using any valid character such as a comma ',' or a period '.'.

This table lists some commonly used field delimiter characters.

Specifier

Field Delimiter

','

'comma'

Comma

' '

'space'

Space

'\t'

'tab'

Tab

';'

'semi'

Semicolon

'|'

'bar'

Vertical bar

unspecified

If unspecified, readtable automatically detects the delimiter.

To treat consecutive delimiters as a single delimiter, specify Delimiter as a cell array of character vectors. In addition, you must also specify the MultipleDelimsAsOne option.

Example: 'Delimiter',',' or 'Delimiter','comma'

Data Types: char | string | cell

Procedure to manage leading 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.

Example: 'LeadingDelimitersRule','keep'

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.

Example: 'TrailingDelimitersRule','keep'

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

Example: 'ConsecutiveDelimitersRule','split'

Data Types: char | string

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.

Example: 'VariableWidths',[10,7,4,26,7]

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

Example: 'Whitespace',' _'

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

Procedure to handle empty lines in the data, specified as 'skip', 'read', or 'error'. The importing function interprets white space as empty.

Empty Line RuleBehavior
'skip'Skip the empty lines.
'read'Import the empty lines. The importing function parses the empty line using the values specified in VariableWidths, VariableOptions, MissingRule, and other relevant properties, such as Whitespace.
'error'Display an error message and abort the import operation.

Example: 'EmptyLineRule','skip'

Data Types: char | string

Variable names location, specified as a positive scalar integer. The VariableNamesLine property specifies the line number where variable names are located.

If VariableNamesLine is specified as 0, then do not import the variable names. Otherwise, import the variable names from the specified line.

Example: 'VariableNamesLine',6

Data Types: single | double | uint8 | uint16 | uint32 | uint64

Procedure to handle partial fields in the data, specified as one of the values in this table.

Partial Field RuleBehavior
'keep'

Keep the partial field data and convert the text to the appropriate data type.

In some cases, when the importing function is unable to interpret the partial data, a conversion error might occur.

'fill'

Replace missing data with the contents of the FillValue property.

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

'omitrow'Omit rows that contain partial data.
'omitvar'Omit variables that contain partial data.
'wrap'Begin reading the next line of characters.
'error'Display an error message and abort the import operation.

Example: 'PartialFieldRule','keep'

Data Types: char | string

Variable units location, specified as a positive scalar integer. The VariableUnitsLine property specifies the line number where variable units are located.

If VariableUnitsLine is specified as 0, then do not import the variable units. Otherwise, import the variable units from the specified line.

Example: 'VariableUnitsLine',8

Data Types: single | double | uint8 | uint16 | uint32 | uint64

Variable description location, specified as a positive scalar integer. The VariableDescriptionsLine property specifies the line number where variable descriptions are located.

If VariableDescriptionsLine is specified as 0, then do not import the variable descriptions. Otherwise, import the variable descriptions from the specified line.

Example: 'VariableDescriptionsLine',7

Data Types: single | double | uint8 | uint16 | uint32 | uint64

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

NOTE: The extra columns are imported as text with data typechar.

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

Example: 'ExtraColumnsRule','addvars'

Data Types: char | string

Number of header lines to skip at the beginning of the file, specified as the comma-separated pair consisting of 'NumHeaderLines' and a positive integer. If unspecified, readtable automatically detects the number of lines to skip.

Example: 'NumHeaderLines',2

Data Types: single | double

Column format of the file, specified as the comma-separated pair consisting of 'Format' and a character vector or a string scalar having one or more conversion specifiers, or 'auto'. The conversion specifiers are the same as the specifiers accepted by the textscan function.

Specifying the format can significantly improve speed for some large files. If you do not specify a value for Format, then readtable uses %q to interpret nonnumeric columns. The %q specifier reads the text and omits double quotation marks (") if appropriate.

  • If you do not specify the 'Format' name-value pair, then the readtable function behaves as though you have used the results of the detectImportOptions function to import the data. For more information on the consequences of this behavior, see Compatibility Considerations.

  • If you specify 'Format','auto', then the variables created are double arrays, cell array of character vectors, or datetime arrays, depending on the data. If the entire column is numeric, variables are imported as double. If any element in a column is not numeric, the variables are imported as cell arrays of character vectors, or as datetime arrays if the values represent dates and times.

Example: 'Format','auto'

Data Types: char | string

Returned value for empty numeric fields in delimited text files, specified as the comma-separated pair consisting of 'EmptyValue' and a numeric scalar.

Example: 'EmptyValue',0

Multiple delimiter handling, specified as the comma-separated pair consisting of 'MultipleDelimsAsOne' and either true or false. If true, then the importing function treats consecutive delimiters as a single delimiter. Repeated delimiters separated by white-space are also treated as a single delimiter. You must also specify the Delimiter option.

Example: 'MultipleDelimsAsOne',1

Logical indicator determining data concatenation, specified as the comma-separated pair consisting of 'CollectOutput' and either true or false. If true, then the importing function concatenates consecutive output cells of the same fundamental MATLAB class into a single array.

Example: 'CollectOutput',true

Symbols designating text to ignore, specified as the comma-separated pair consisting of 'CommentStyle' and a character vector, cell array of character vectors, string, or string array.

For example, specify a character such as '%' to ignore text following the symbol on the same line. Specify a cell array of two character vectors, such as {'/*','*/'}, to ignore any text between those sequences.

MATLAB checks for comments only at the start of each field, not within a field.

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

Data Types: char | string

Exponent characters, specified as the comma-separated pair consisting of 'ExpChars' and a character vector or string. The default exponent characters are e, E, d, and D.

Example: 'ExpChars','eE'

Data Types: char | string

End-of-line characters, specified as the comma-separated pair consisting of 'LineEnding' and a character vector or string. The character vector must be '\r\n' or it must specify a single character. Common end-of-line characters are a newline character ('\n') or a carriage return ('\r'). If you specify '\r\n', then the importing function treats any of \r, \n, and the combination of the two (\r\n) as end-of-line characters.

The default end-of-line sequence is \n, \r, or \r\n, depending on the contents of your file.

If there are missing values and an end-of-line sequence at the end of the last line in a file, then the importing function returns empty values for those fields. This ensures that individual cells in output cell array, C, are the same size.

Example: 'LineEnding',':'

Data Types: char | string

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 indicating the decimal separator in numeric variables, specified as a character vector or string scalar. The importing function uses the characters specified in the DecimalSeparator name-value pair 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.

Example: If name-value pair is specified as 'DecimalSeparator',',', then the importing function imports the text "3,14159" as the number 3.14159.

Data Types: char | string

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 specified in the ThousandsSeparator name-value pair to interpret the numbers being imported.

Example: If name-value pair is specified as 'ThousandsSeparator',',', then the importing function imports the text "1,234,000" as 1234000.

Data Types: char | string

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

Example: If name-value pair is specified as 'TrimNonNumeric',true, then the importing function reads '$500/-' as 500.

Data Types: logical

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 readtable function uses automatic character set detection to determine the encoding when reading the file.

If you specify the 'Encoding' argument in addition to the import options, then the readtable function uses the specified value for 'Encoding', overriding the encoding defined in the import options.

Example: 'Encoding','UTF-8' uses UTF-8 as the encoding.

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

Data Types: char | string

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 'char', then the importing function returns duration data as a cell array of character vectors.

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

Example: 'DurationType','text'

Data Types: char | string

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

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

Example: If name-value pair is specified as 'TrimNonNumeric',true, then the importing function reads '$500/-' as 500.

Data Types: logical

Spreadsheet Files Only

collapse all

Worksheet to read, specified as the comma-separated pair consisting of 'Sheet' and a positive integer indicating the worksheet index or a character vector or string containing the worksheet name. The worksheet name cannot contain a colon (:). To determine the names of sheets in a spreadsheet file, use sheets = sheetnames(filename). For more information, see sheetnames.

If you specify the Sheet argument in addition to opts the import options, then the readtable function uses the specified value for Sheet argument, overriding the sheet name defined in the import options.

Example: 'Sheet', 2

Example: 'Sheet', 'MySheetName'

Example: 'Sheet', "MySheetName"

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

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

Ways to specify Range Description

'Cell' or [row col]

Starting Cell

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]

'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','D2:H4'

''

Unspecified or Empty

If unspecified, readtable automatically detects the used range.

Example: 'Range',''

Note: Used Range refers to the rectangular portion of the spreadsheet that actually contains data. readtable 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 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, readtable 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 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, readtable 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'

Named Range in Excel

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 readtable can read that range using its name.

Example: 'Range','myTable'

Example: 'Range', 'A1:F10'

Example: 'Range', "A1:F10"

Data Types: char | string

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

Example: 'DataRange', 'B2:H15'

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

Example: 'RowNamesRange', 'A1:H1'

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

Example: 'VariableNamesRange', 'A1:A15'

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

Example: 'VariableUnitsRange', 'A1:A5'

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

Example: 'VariableDescriptionsRange', 'B1:B15'

Data Types: char | string | single | double

Flag to start an instance of Microsoft Excel for Windows when reading spreadsheet data, specified as the comma-separated pair consisting of 'UseExcel' and either true, or false.

You can set the 'UseExcel' parameter to one of these values:

  • true — The importing function starts an instance of Microsoft Excel when reading the file.

  • false — The importing function does not start an instance of Microsoft Excel when reading the file. When operating in this mode, the importing function functionality differs in the support of file formats and interactive features, such as formulas and macros.

UseExcel

true

false

Supported file formats

.xls, .xlsx, .xlsm, .xltx, .xltm, .xlsb, .ods

.xls, .xlsx, .xlsm, .xltx, .xltm

Support for interactive features, such as formulas and macros

Yes

No

When reading from spreadsheet files on Windows platforms, if you want to start an instance of Microsoft Excel, then set the 'UseExcel' parameter to true.

Example: 'UseExcel',true

XML Files Only

collapse all

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'

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

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.

Example: 'TableIndex',2

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

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

Procedure to handle cells with merged columns, specified as one of the values in this table.

Import Error RuleBehavior
"placeleft"

Place the data in the left-most cell and fill the remaining cells with the contents of the FillValue property.

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

"placeright"

Place the data in the right-most cell and fill the remaining cells with the contents of the FillValue property.

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

"duplicate"

Duplicate the data in all cells.

"omitrow"Omit rows where errors occur.
"error"Display an error message and abort the import operation.

Example: 'MergedCellColumnRule',"placeright"

Procedure to handle cells with merged rows, specified as one of the values in this table.

Import Error RuleBehavior
"placetop"

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

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

"placebottom"

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

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

"duplicate"

Duplicate the data in all cells.

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

Example: 'MergedCellRowRule',"duplicate"

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.

Example: 'VariableNamesRow',2

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.

Example: 'VariableUnitsRow',3

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.

Example: 'VariableDescriptionsRow',4

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.

Example: "EmptyRowRule","read"

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.

Example: "EmptyColumnRule","error"

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

Output table, returned as a table. The table can store metadata such as descriptions, variable units, variable names, and row names. For more information, see the Properties section of table.

Tips

  • Large files in XLSX format sometimes load slowly. For better import and export performance, Microsoft recommends that you use the XLSB format.

  • 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 

Compatibility Considerations

expand all

Behavior changed in R2020a

Introduced in R2013b