Documentation

This is machine translation

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

readtable

Create table from file

Syntax

  • T = readtable(filename)
    example
  • T = readtable(filename,Name,Value)
    example

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 if the entire column is numeric, or cell arrays of strings if 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.

Examples

collapse all

Create Table from Text File

Create a file, myCsvTable.dat, that contains the following comma-separated column oriented data.

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

Create a table from the comma-separated text file.

T = readtable('myCsvTable.dat')
T = 

     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     

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.

Create Table from Text File without Column Headings

Create a file, mySpaceDelimTable.txt, that contains the following space delimited, column oriented data.

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

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

T = readtable('mySpaceDelimTable.txt',...
    'Delimiter',' ','ReadVariableNames',false)
T = 

    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.

Create and Format Table from Text File

Create a file, myCsvTable.dat, that contains the following comma-separated column oriented data.

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

Create a table from the comma-separated text file. Format the first two variables as strings, the third variable as uint32, and the next two variables as double-precision, floating-point numbers. Format the last variable as a string.

T = readtable('myCsvTable.dat','Format','%s%s%u%f%f%s')
T = 

     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 strings, %f for double, and %u for uint32.

Read Foreign-Language Dates from Text File

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

Create a sample file named myfile.txt that contains comma-separated values. The first column of values contains dates in German and the second and third columns are numeric values.

fileID = fopen('myfile.txt','w','n','ISO-8859-15');
fprintf(fileID,'1 Januar 2014, 20.2, 100.5 \n');
fprintf(fileID,'1 Februar 2014, 21.6, 102.7 \n');
fprintf(fileID,'1 März 2014, 20.7, 99.8 \n');
fclose(fileID);

The sample file looks like this:

1 Januar 2014, 20.2, 100.5
1 Februar 2014, 21.6, 102.7
1 März 2014, 20.7, 99.8

Read the sample file using readtable. The conversion specifiers are %D for a date 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('myfile.txt','ReadVariableNames',false,...
    'Format','%{dd MMMM yyyy}D %f %f',...
    'FileEncoding','ISO-8859-15',...
    'DateLocale','de_DE')
T = 

          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 Table from Spreadsheet Including Row Names

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 = 

                 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 = 

    'LastName'    'Variable'

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

Read Specific Range of Data from Spreadsheet

Create a table from the 5-by-3 rectangular region between the two corners C2 and E6 on the spreadsheet patients.xls. 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.

Related Examples

Input Arguments

collapse all

filename — Name of file to readstring

Name of the file to read, specified as 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 the empty string (for a string-valued 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'

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.

Delimited Text Files and Spreadsheet Files

'FileType' — Type of file'text' | 'spreadsheet'

Type of file, specified as the comma-separated pair consisting of 'FileType' and the string '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'

'ReadVariableNames' — Indicator for reading first row as variable namestrue | false | 1 | 0

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.

'ReadRowNames' — Indicator for reading the first column as row namesfalse (default) | true | 0 | 1

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.

'TreatAsEmpty' — Strings to treat as empty valuestring | cell array of strings

Strings to treat as empty value, specified as the comma-separated pair consisting of 'TreatAsEmpty' and a string or a cell array of strings. Table elements corresponding to these strings 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' sets N/A within numeric columns to NaN.

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

Delimited Text Files Only

When reading delimited text files, you can specify any of the name-value pair arguments listed here. Also, if you specify the 'Format' name-value pair argument, you can specify any of the name-value pair arguments accepted by the textscan function.

'Delimiter' — Field delimiter characterstring

Field delimiter character, specified as the comma-separated pair consisting of 'Delimiter' and one of the following strings:

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'

'HeaderLines' — Number of lines to skip at beginning of filepositive integer

Number of lines to skip at beginning of 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

'Format' — Format of columns in filestring of one or more conversion specifiers

Format of the columns in the file, specified as the comma-separated pair consisting of 'Format' and 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 a string and omits double quotation marks (") if appropriate.

By default, the variables created are either double or cell array of strings, depending on the data. If the entire column is numeric, variables are imported asdouble. If any element in a column is not numeric, the variables are imported as cell arrays of strings.

'DateLocale' — Locale for reading datesstring

Locale for reading dates, specified as the comma-separated pair consisting of 'DateLocale' and a string. The string takes the form xx_YY, where xx is a lowercase ISO 639-1 two-letter code indicating a language, and YY is an uppercase ISO 3166-1 alpha-2 code indicating a country. 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.

Example: 'DateLocale','ja_JP'

'FileEncoding' — Character encoding scheme'UTF-8' | 'ISO-8859-1' | 'windows-1251' | 'windows-1252' | ...

Character encoding scheme associated with the file, specified as the comma-separated pair consisting of 'FileEncoding' and one of the following strings.

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

 

The default encoding is system-dependent.

Data Types: char

Spreadsheet Files Only

'Sheet' — Worksheet to read1 (default) | positive integer indicating worksheet index | string containing worksheet name

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

Example: 'Sheet', 2

'Range' — Rectangular portion of worksheet to readstring

Rectangular portion of the worksheet to read, specified as the comma-separated pair consisting of 'Range' and a string in one of the following forms.

Form of the Value of RangeDescription

'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. Strings that contain only white space are considered data and are 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 '1:7' as, 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 'A:F' as, 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'

  • If you specify a range larger than the used range in the spreadsheet, then readtable automatically detects and adjusts the range to import only the parts that actually contain data.

  • If you specify a range smaller than the used range in the spreadsheet, then readtable imports only the subset that fits in your specified range.

'Basic' — Indicator for reading in basic modetrue | false | 1 | 0

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 treatment of date and time data.

Basic

false

true

Supported file formats

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

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

Date/time import type and format

Date string

A date string is a character vector composed of fields related to a specific date and/or time. The content of the imported strings depends on the values in the imported range:

  • If all values are dates only, then the imported string contains only dates.

  • If any value contains time data, then all imported strings contain time.

  • If any time value contains millisecond data, then all the imported strings contain data to the millisecond precision.

To convert date strings to MATLAB® date and time data type, see datetime.

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.

To convert Excel serial date numbers to MATLAB date and time data type, see datetime.

Support for interactive features such as formulas and macros

Yes

No

Output Arguments

collapse all

T — Output tabletable

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 Table Properties.

Introduced in R2013b

Was this topic helpful?