readtimetable

Create timetable from file

Description

example

TT = readtimetable(filename) creates a timetable by reading column-oriented data from a file.

readtimetable 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

readtimetable creates one variable in TT for each column in the file and reads variable names from the first row of the file.

readtimetable sets the first column of type datetime or duration in the tabular data to be the row times of the timetable. The remaining columns become variables of the timetable.

example

TT = readtimetable(filename,opts) additionally uses the import options opts.

example

TT = readtimetable(___,Name,Value) creates a timetable from a file with additional options specified by one or more name-value pair arguments. Use any of the input arguments from the previous syntaxes before specifying the name-value pairs.

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 readtimetable supports only these name-value pairs:

  • Text and spreadsheet parameters — ReadVariableNames, RowTimes, SampleRate, TimeStep, StartTime

  • Text only parameters — DateLocale, Encoding

  • Spreadsheet only parameters — Sheet, UseExcel

Examples

collapse all

Create a table from the comma-separated text file.

TT = readtimetable('outages.csv');

Display a summary of the table. When creating a timetable, if you do not specify any parameters for row times, then the readtimetable function detects and designates the first datetime or duration variable in the data, OutageTime, as the row times variable. The remaining variables become the variables of the timetable.

summary(TT)
RowTimes:

    OutageTime: 1468x1 datetime
        Values:
            Min         01-Feb-2002 12:18:00 
            Median      18-Mar-2010 21:05:30 
            Max         15-Jan-2014 02:41:00 

Variables:

    Region: 1468x1 cell array of character vectors

    Loss: 1468x1 double

        Values:

            Min             0       
            Median          180.26  
            Max             23418   
            NumMissing      604     

    Customers: 1468x1 double

        Values:

            Min             0           
            Median          75765       
            Max             5.9689e+06  
            NumMissing      328         

    RestorationTime: 1468x1 datetime

        Values:

            Min             07-Feb-2002 16:50:00  
            Median          31-Mar-2010 10:54:00  
            Max             18-Sep-2042 23:31:00  
            NumMissing      29                    

    Cause: 1468x1 cell array of character vectors

Detect import options for a text file, specify the variable types, and then create a timetable from the data.

Create an import options object from a file and examine the variable options.

opts = detectImportOptions('outages.csv');
opts.VariableOptions
ans = 
  1x6 heterogeneous VariableImportOptions (TextVariableImportOptions, DatetimeVariableImportOptions, NumericVariableImportOptions) array with properties:

    Name
    Type
    FillValue
    TreatAsMissing
    QuoteRule
    Prefixes
    Suffixes
    EmptyFieldRule

Modify the options object to specify the desired datatypes for the variables in the data. Change the datatypes for the variables Region and Cause to categorical.

opts = setvartype(opts,{'Region','Cause'},{'categorical','categorical'});

Use readtimetable along with the options object to import the timetable. Then display a summary of the timetable.

TT = readtimetable('outages.csv',opts);
summary(TT)
RowTimes:

    OutageTime: 1468x1 datetime
        Values:
            Min         01-Feb-2002 12:18:00 
            Median      18-Mar-2010 21:05:30 
            Max         15-Jan-2014 02:41:00 

Variables:

    Region: 1468x1 categorical

        Values:

            MidWest        142   
            NorthEast      557   
            SouthEast      389   
            SouthWest       26   
            West           354   

    Loss: 1468x1 double

        Values:

            Min             0       
            Median          180.26  
            Max             23418   
            NumMissing      604     

    Customers: 1468x1 double

        Values:

            Min             0           
            Median          75765       
            Max             5.9689e+06  
            NumMissing      328         

    RestorationTime: 1468x1 datetime

        Values:

            Min             07-Feb-2002 16:50:00  
            Median          31-Mar-2010 10:54:00  
            Max             18-Sep-2042 23:31:00  
            NumMissing      29                    

    Cause: 1468x1 categorical

        Values:

            attack                294  
            earthquake              2  
            energy emergency      188  
            equipment fault       156  
            fire                   25  
            severe storm          338  
            thunder storm         201  
            unknown                24  
            wind                   95  
            winter storm          145  

Read a table from the comma-separated text file and create a timetable with a row times variable of your choice.

Create an import options object and preview the tabular data.

opts = detectImportOptions('outages.csv');
preview('outages.csv',opts)
ans=8×6 table
       Region             OutageTime          Loss     Customers       RestorationTime              Cause       
    _____________    ____________________    ______    __________    ____________________    ___________________

    {'SouthWest'}    01-Feb-2002 12:18:00    458.98    1.8202e+06    07-Feb-2002 16:50:00    {'winter storm'   }
    {'SouthEast'}    23-Jan-2003 00:49:00    530.14    2.1204e+05                     NaT    {'winter storm'   }
    {'SouthEast'}    07-Feb-2003 21:15:00     289.4    1.4294e+05    17-Feb-2003 08:14:00    {'winter storm'   }
    {'West'     }    06-Apr-2004 05:44:00    434.81    3.4037e+05    06-Apr-2004 06:10:00    {'equipment fault'}
    {'MidWest'  }    16-Mar-2002 06:18:00    186.44    2.1275e+05    18-Mar-2002 23:23:00    {'severe storm'   }
    {'West'     }    18-Jun-2003 02:49:00         0             0    18-Jun-2003 10:54:00    {'attack'         }
    {'West'     }    20-Jun-2004 14:39:00    231.29           NaN    20-Jun-2004 19:16:00    {'equipment fault'}
    {'West'     }    06-Jun-2002 19:28:00    311.86           NaN    07-Jun-2002 00:51:00    {'equipment fault'}

Create a timetable by specifying the RestorationTime variable to be the row times variable for the timetable. Then, display a summary of the timetable.

TT = readtimetable('outages.csv','RowTimes','RestorationTime');
summary(TT)
RowTimes:

    RestorationTime: 1468x1 datetime
        Values:
            Min             07-Feb-2002 16:50:00 
            Median          31-Mar-2010 10:54:00 
            Max             18-Sep-2042 23:31:00 
            NumMissing      29                   

Variables:

    Region: 1468x1 cell array of character vectors

    OutageTime: 1468x1 datetime

        Values:

            Min       01-Feb-2002 12:18:00
            Median    18-Mar-2010 21:05:30
            Max       15-Jan-2014 02:41:00

    Loss: 1468x1 double

        Values:

            Min             0       
            Median          180.26  
            Max             23418   
            NumMissing      604     

    Customers: 1468x1 double

        Values:

            Min             0           
            Median          75765       
            Max             5.9689e+06  
            NumMissing      328         

    Cause: 1468x1 cell array of character vectors

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'

Remote Location

If the file is stored at a remote location, then filename must contain the full path of the file specified as a uniform resource locator (URL) of the form:

scheme_name://path_to_file/my_file.ext

Based on your 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 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 quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: 'NumHeaderLines',5 indicates that the first five lines that precede the tabular data are header lines.

Text and Spreadsheet Files

collapse all

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

Data Types: char | string

Number of header lines in the file, specified as the comma-separated pair consisting of 'NumHeaderLines' and a positive integer. If unspecified, the importing function automatically detects the number of header lines in the file.

Example: 'NumHeaderLines',7

Data Types: single | double

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

Data Types: single | double

Portion of the data to read from text or spreadsheet files, specified as the comma separated pair consisting of 'Range' and a character vector, string scalar, or numeric vector in one of these forms.

Ways to specify RangeDescription

Starting Cell

'Cell' or [row col]

Specify the starting cell for the data as a character vector or string scalar or a two element numeric vector.

  • Character vector or string scalar containing a column letter and row number using Excel A1 notation. For example, A5 is the identifier for the cell at the intersection of column A and row 5.

  • Two element numeric vector of the form [row col] indicating the starting row and column.

Using the starting cell, the importing function automatically detects the extent of the data by beginning the import at the start cell and ending at the last empty row or footer range.

Example: 'A5' or [5 1]

Rectangular Range

'Corner1:Corner2' or [r1 c1 r2 c2]

Specify the exact range to read using the rectangular range in one of these forms.

  • 'Corner1:Corner2' — Specify the range using Corner1 and Corner2 which are the two opposing corners that define the region to read in Excel A1 notation. For example, 'C2:N15'.

  • [r1 c1 r2 c2] — Specify the range using a four element numeric vector containing start-row, start-column, end-row, and end-column. For example, [2 3 15 13].

The importing function only reads the data contained in the specified range. Any empty fields within the specified range are imported as missing cells.

Row Range or Column Range

'Row1:Row2' or 'Column1:Column2'

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

Using the specified row range, the importing function automatically detects the column extent by reading from the first nonempty column to the end of the data, and creates one variable per column.

Example: '5:500'

Alternatively, specify the range by identifying the beginning and ending columns using Excel column letters or numbers.

Using the specified column range, the import function automatically detects the row extent by reading from the first nonempty row to the end of the data or the footer range.

The number of columns in the specified range must match the number specified in the ExpectedNumVariables property.

Example: 'A:K'

Starting Row Number

n

Specify the first row containing the data using the positive scalar row index.

Using the specified row index, the importing function automatically detects the extent of the data by reading from the specified first row to the end of the data or the footer range.

Example:5

Excel’s Named Range

'NamedRange'

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

Example: 'Range','myTable'

Unspecified or Empty

''

If unspecified, the importing function automatically detects the used range.

Example: 'Range',''

Note: Used Range refers to the rectangular portion of the spreadsheet that actually contains data. The importing function automatically detects the used range by trimming any leading and trailing rows and columns that do not contain data. Text that is only white space is considered data and is captured within the used range.

Data Types: char | string | double

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.

Data Types: char | string

Text to interpret as missing data, specified as a character vector, string scalar, cell array of character vectors, or string array.

Example: 'TreatAsMissing',{'NA','TBD'} instructs the importing function to treat any occurrence of NA or TBD as a missing fields.

Data Types: char | string | cell

Read the first row as variable names, specified as the comma-separated pair consisting of 'ReadVariableNames' and either true or false. If unspecified, the importing function 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. The importing function 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. The importing function creates default variable names of the form 'Var1',...,'VarN', where N is the number of variables.

Unspecified When left unspecified, the importing function automatically detects true or false and proceeds accordingly.

When you specify ReadVariableNames name-value pair in addition to opts, then the importing function proceeds as follows.

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

  • If ReadVariableNames is set to false, then the importing function reads the variable names from the VariableNames property of the import options object.

Data Types: logical

Row times variable, specified as the comma-separated pair consisting of 'RowTimes' and a variable name or a time vector.

  • Variable name must be a character vector or string scalar containing the name of any variable in the input table that contains datetime or duration values. The variable specified by the variable name provides row time labels for the rows. The remaining variables of the input table become the variables of the timetable.

  • Time vector must be a datetime vector or a duration vector. The number of elements of time vector must equal the number of rows of the input table. The time values in the time vector do not need to be unique, sorted, or regular. All the variables of the input table become variables of the timetable.

Data Types: char | string | datetime | duration

Sample rate for row times, specified as the comma-separated pair consisting of 'SampleRate' and a positive numeric scalar. The sample rate is the number of samples per second (Hz) of the time vector of the output timetable.

When you use 'SampleRate' to specify the row time vector of the timetable, the default first row time (start time) is zero second. To set a start time other than zero, specify the'StartTime' name-value pair.

Data Types: double

Time step between row times, specified as the comma-separated pair consisting of 'TimeStep' and a duration scalar or calendarDuration scalar. The value of the 'TimeStep' parameter specifies the length of time between consecutive row times. The importing function uses the time step value to calculate regularly spaced row times.

When you use 'TimeStep' to specify the row time vector of the timetable, the default first row time (start time) is zero second. To set a start time other than zero, specify the 'StartTime' name-value pair.

If the 'TimeStep' is a calendar duration value, then the 'StartTime' must be a datetime value.

Data Types: duration | calendarDuration

Start time of the row times, specified as the comma-separated pair consisting of StartTime and a datetime scalar or duration scalar.

To define the time vector for the timetable, use 'StartTime' with either the 'SampleRate' or the 'TimeStep' name-value pair arguments.

The data type of the start time, dictates the data type of the row time vector.

  • If the start time is a datetime value, then the row times of the timetable are datetime values.

  • If the start time is a duration value, then the row times are durations.

Data Types: datetime | duration

Flag to preserve variable names, specified as the comma-separated pair consisting of PreserveVariableNames and either true, or false.

  • true — Preserve variable names that are not valid MATLAB identifiers such as variable names that include spaces and non-ASCII characters.

  • false — Convert invalid variable names (as determined by the isvarname function) to valid MATLAB identifiers.

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 PreserveVariableNames to true.

Text Files Only

collapse all

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

Example: 'Delimiter','|'

Example: 'Delimiter',{';','*'}

Data Types: char | string | cell

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

Example: 'Whitespace',' _'

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

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

Example: 'LineEnding','\n'

Example: 'LineEnding','\r\n'

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

Data Types: char | string | cell

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

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

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

Data Types: char | string | cell

Character encoding scheme associated with the file, specified as the comma-separated pair consisting of 'Encoding' and 'system' or a standard character encoding scheme name, such as one of the values in this table.

'Big5'

'ISO-8859-1'

'windows-847'

'Big5-HKSCS'

'ISO-8859-2'

'windows-949'

'CP949'

'ISO-8859-3'

'windows-1250'

'EUC-KR'

'ISO-8859-4'

'windows-1251'

'EUC-JP'

'ISO-8859-5'

'windows-1252'

'EUC-TW'

'ISO-8859-6'

'windows-1253'

'GB18030'

'ISO-8859-7'

'windows-1254'

'GB2312'

'ISO-8859-8'

'windows-1255'

'GBK'

'ISO-8859-9'

'windows-1256'

'IBM866'

'ISO-8859-11'

'windows-1257'

'KOI8-R'

'ISO-8859-13'

'windows-1258'

'KOI8-U'

'ISO-8859-15'

'US-ASCII'

 

'Macintosh'

'UTF-8'

 

'Shift_JIS'

 

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

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

Data Types: char | string

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.

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

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.

Data Types: char | string

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.

Spreadsheet Files Only

collapse all

Sheet to read from, specified as an empty character array, a character vector or string scalar containing the sheet name, or a positive scalar integer denoting the sheet index. Based on the value specified for the Sheet property, the import function behaves as described in the table.

SpecificationBehavior
'' (default)Import data from the first sheet.
NameImport data from the matching sheet name, regardless of order of sheets in the spreadsheet file.
IntegerImport data from sheet in the position denoted by the integer, regardless of the sheet names in the spreadsheet file.

Data Types: char | string | single | double

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.

Output Arguments

collapse all

Output timetable. The timetable can store metadata such as descriptions, variable units, variable names, and row times. For more information, see the Properties sections of timetable.

Introduced in R2019a