Documentation

This is machine translation

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

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

readtable

Create table from file

Syntax

T = readtable(filename)
T = readtable(filename,Name,Value)
T = readtable(filename,opts)
T = readtable(filename,opts,Name,Value)

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

readtable creates one variable in T for each column in the file and reads variable names from the first row of the file. By default, the variables created are double when the entire column is numeric, or cell arrays of character vectors when any element in a column is not numeric.

example

T = readtable(filename,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.

example

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

T = readtable(filename,opts,Name,Value) creates a table using the import options and with additional options specified by one or more of these name-value pair arguments: ReadVariableNames, ReadRowNames, DateLocale, Encoding, Sheet, and Basic.

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 = fullfile(matlabroot,'examples','matlab','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=5x6 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     

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

filename = fullfile(matlabroot,'examples','matlab','mySpaceDelimTable.txt');

Create a table from the space delimited text file that does not contain variable names as column headings.

T = readtable(filename,...
    'Delimiter',' ','ReadVariableNames',false)
T=3x5 table
    Var1    Var2    Var3    Var4     Var5  
    ____    ____    ____    ____    _______

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

T contains default variable names.

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 = fullfile(matlabroot,'examples','matlab','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=5x6 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.

Load the file german_dates.txt and preview its contents 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.

filename = fullfile(matlabroot,'examples','matlab','german_dates.txt');

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(filename,'ReadVariableNames',false,...
    'Format','%{dd MMMM yyyy}D %f %f',...
    'FileEncoding','ISO-8859-15',...
    'DateLocale','de_DE')
T=3x3 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=5x4 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 array
    {'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'
                EmptyLineRule: 'skip'
                     Encoding: 'US-ASCII'

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

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

   Location Properties:
                     DataLine: 2
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0

Examine the Type property of variables TaxiIn and TaxiOut.

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

    WhitespaceRule
    Type
    FillValue
    Name
    QuoteRule
    TreatAsMissing

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

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

Specify the subset of variables to import and examine.

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

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

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

    TaxiIn: 123523x1 double

        Values:

            Min            0      
            Median         5      
            Max            1451   
            NumMissing     37383  

    TaxiOut: 123523x1 double

        Values:

            Min            0       
            Median         13      
            Max            755     
            NumMissing     37364   

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

Create an import options object from a file.

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

   Sheet Properties:
                        Sheet: ''

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

   Variable Import Properties: 	Set types by name using setvartype
                VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
                VariableTypes: {'char', 'char', 'double' ... and 7 more}
        SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
              VariableOptions: Show all 10 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts

   Range Properties:
                    DataRange: 'A2' (Start Cell)
           VariableNamesRange: 'A1'
                RowNamesRange: ''
           VariableUnitsRange: ''
    VariableDescriptionsRange: ''

Modify the options object to specify which variables to import.

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

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

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

    Systolic: 100x1 double

        Values:

            Min       109       
            Median    122       
            Max       138       

    Diastolic: 100x1 double

        Values:

            Min         68       
            Median    81.5       
            Max         99       

Input Arguments

collapse all

Name of the file to read, specified as a character vector or a string. If filename includes the file extension, then readtable 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, readtable reads any Excel spreadsheet file format recognized by your version of Excel. If your system does not have Excel for Windows, readtable operates in basic import mode, and reads only .xls, .xlsx, .xlsm, .xltx, and .xltm files.

For a delimited text file, readtable 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. readtable ignores insignificant white space in the file.

Example: 'myFile.xlsx' or "myFile.xlsx"

Data Types: char | string

File import options, specified as an SpreadsheetImportOptions, DelimitedTextImportOptions or FixedWidthImportOptions 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
Text filesDelimitedTextImportOptions object
Fixed-width text filesFixedWidthImportOptions object

Name-Value Pair Arguments

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

Example: 'ReadVariableNames',false indicates that the first row of the file does not correspond to variable names.

When reading:

  • Text files, only these parameter names apply: FileType, ReadVariableNames, ReadRowNames, TreatAsEmpty, DatetimeType, Delimiter, HeaderLines, Format, EmptyValue, MultipleDelimsAsOne, CollectOutput, CommentStyle, ExpChars, EndOfLine, DateLocale, and Encoding.

  • Spreadsheet files, only these parameter names apply: FileType, ReadVariableNames, ReadRowNames, TreatAsEmpty, DatetimeType, Sheet, Range, Basic, and TextType.

  • Text or Spreadsheet files with the opts import options, only these parameter names apply: ReadVariableNames, ReadRowNames, DateLocale, Encoding, Sheet, and Basic.

Text and Spreadsheet Files

collapse all

Type of file, specified as the comma-separated pair consisting of 'FileType' and a character vector or string containing 'text' or 'spreadsheet'.

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

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

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

Example: 'FileType','text'

Example: 'FileType',"text"

Data Types: char | string

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.

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.

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

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

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

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

Data Types: char | string

Output data type of text, specified as the comma-separated pair consisting of 'TextType' and the character vector or string containing 'char' or 'string'. If you specify the value 'char', then readtable returns text as a cell array of character vectors. If you specify the value 'string', then readtable returns text as an array of type string.

Data Types: char | string

Type for imported date and time data, specified as the comma-separated pair consisting of 'DatetimeType' and one of these character vectors or strings: '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 'char', then readtable returns dates as a cell array of character vectors.

  • If 'TextType' is 'string', then readtable 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','text'

Example: 'DatetimeType',"datetime"

Data Types: char | string

Text Files Only

collapse all

Field delimiter character, specified as the comma-separated pair consisting of 'Delimiter' and a character vector or a string containing one of the following specifiers.

Specifier

Field Delimiter

','

'comma'

Comma

' '

'space'

Space

'\t'

'tab'

Tab

';'

'semi'

Semicolon

'|'

'bar'

Vertical bar

unspecified

If unspecified, readtable automatically detects the delimiter.

Example: 'Delimiter','space'

Example: 'Delimiter',"space"

Data Types: char | string

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

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 of one or more conversion specifiers. 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.

By default, the variables created are either double or cell array of character vectors, 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.

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

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.

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',{'/*', '*/'}

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.

Data Types: char | string

End-of-line characters, specified as the comma-separated pair consisting of 'EndOfLine' 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: 'EndOfLine',':'

Example: "EndOfLine",":"

Data Types: char | string

Locale for reading dates, specified as the comma-separated pair consisting of 'DateLocale' and a character vector or a string 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

For a list of common values for the locale, see the Locale name-value pair argument for the datetime function.

When using the %D format specifier to read text as datetime values, use DateLocale to specify the locale in which readtable 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 readtable function uses the specified value for DateLocale argument, overriding the locale defined in the import options.

Example: 'DateLocale','ja_JP'

Example: 'DateLocale',"ja_JP"

Character encoding scheme associated with the file, specified as the comma-separated pair consisting of 'Encoding' and 'system' or a standard character encoding scheme name like one of the values in this table. When you do not specify any encoding or specify encoding as 'system', the readtable function uses your system default encoding to read the file.

'Big5'

'ISO-8859-1'

'windows-847'

'Big5-HKSCS'

'ISO-8859-2'

'windows-949'

'CP949'

'ISO-8859-3'

'windows-1250'

'EUC-KR'

'ISO-8859-4'

'windows-1251'

'EUC-JP'

'ISO-8859-5'

'windows-1252'

'EUC-TW'

'ISO-8859-6'

'windows-1253'

'GB18030'

'ISO-8859-7'

'windows-1254'

'GB2312'

'ISO-8859-8'

'windows-1255'

'GBK'

'ISO-8859-9'

'windows-1256'

'IBM866'

'ISO-8859-11'

'windows-1257'

'KOI8-R'

'ISO-8859-13'

'windows-1258'

'KOI8-U'

'ISO-8859-15'

'US-ASCII'

 

'Macintosh'

'UTF-8'

 

'Shift_JIS'

 

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

Example: 'Encoding', 'UTF-8'

Example: "Encoding", "UTF-8"

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

Data Types: char | string

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 [status,sheets] = xlsfinfo(filename). For more information, see xlsfinfo.

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 in one of the following forms.

Ways to specify Range Description

'Corner1:Corner2'

Rectangular Range

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

Example: 'Range','Corner1:Corner2'

''

Unspecified or Empty

If unspecified, 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'

Excel’s Named Range

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

Example: 'Range','myTable'

Example: 'Range', 'A1:F10'

Example: 'Range', "A1:F10"

Data Types: char | string

Indicator for reading in basic mode, specified as the comma-separated pair consisting of 'Basic' and either true, false, 1, or 0. On Windows without Excel, Mac, and Linux®, the only value allowed is the default value true. When operating in this mode, readtable functionality differs in the support of file formats and interactive features, such as formulas and macros.

Basic

false

true

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

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.

Introduced in R2013b

Was this topic helpful?