Read Spreadsheet Data into Table - MATLAB & Simulink - MathWorks Deutschland (2024)

• readtable — Read a single worksheet.

• spreadsheetDatastore — Read multiple worksheets or files.

Read All Data from Worksheet

Call readtable to read all the data in the worksheet called 2008, and then display only the first 10 rows and columns. Specify the worksheet name using the Sheet name-value pair argument. If your data is on the first worksheet in the file, you do not need to specify Sheet.

T = readtable('airlinesmall_subset.xlsx','Sheet','2008');T(1:10,1:10)

ans=10×10 table Year Month DayofMonth DayOfWeek DepTime CRSDepTime ArrTime CRSArrTime UniqueCarrier FlightNum ____ _____ __________ _________ _______ __________ _______ __________ _____________ _________ 2008 1 3 4 1012 1010 1136 1135 {'WN'} 752 2008 1 4 5 1303 1300 1411 1415 {'WN'} 1161 2008 1 6 7 2134 2115 2242 2220 {'WN'} 1830 2008 1 7 1 1734 1655 54 30 {'WN'} 302 2008 1 8 2 1750 1755 2018 2035 {'WN'} 1305 2008 1 9 3 640 645 855 905 {'WN'} 896 2008 1 10 4 1943 1945 2039 2040 {'WN'} 120 2008 1 11 5 1303 1305 1401 1400 {'WN'} 1685 2008 1 13 7 1226 1230 1415 1400 {'WN'} 1118 2008 1 14 1 1337 1340 1623 1630 {'WN'} 730 

Read Selected Range from Specific Worksheet

From the worksheet named 1996, read only 10 rows of data from the first 5 columns by specifying a range, 'A1:E11'. The readtable function returns a 10-by-5 table.

T_selected = readtable('airlinesmall_subset.xlsx','Sheet','1996','Range','A1:E11')

T_selected=10×5 table Year Month DayofMonth DayOfWeek DepTime ____ _____ __________ _________ _______ 1996 1 18 4 2117 1996 1 12 5 1252 1996 1 16 2 1441 1996 1 1 1 2258 1996 1 4 4 1814 1996 1 31 3 1822 1996 1 18 4 729 1996 1 26 5 1704 1996 1 11 4 1858 1996 1 7 7 2100 

Convert Variables to Datetimes, Durations, or Categoricals

During the import process, readtable automatically detects the data types of the variables. However, if your data contains nonstandard dates, durations, or repeated labels, then you can convert those variables to their correct data types. Converting variables to their correct data types lets you perform efficient computations and comparisons and improves memory usage. For instance, represent the variables Year, Month, and DayofMonth as one datetime variable, the UniqueCarrier as categorical, and ArrDelay as duration in minutes.

data = T(:,{'Year','Month','DayofMonth','UniqueCarrier','ArrDelay'});data.Date = datetime(data.Year,data.Month,data.DayofMonth);data.UniqueCarrier = categorical(data.UniqueCarrier);data.ArrDelay = minutes(data.ArrDelay);

Find the day of the year with the longest delay, and then display the date.

ind = find(data.ArrDelay == max(data.ArrDelay));data.Date(ind)

ans = datetime 07-Apr-2008

Read All Worksheets from Spreadsheet File

A datastore is useful for processing arbitrarily large amounts of data that are spread across multiple worksheets or multiple spreadsheet files. You can perform data import and data processing through the datastore.

Create a datastore from the collection of worksheets in airlinesmall_subset.xlsx, select the variables to import, and then preview the data.

ds = spreadsheetDatastore('airlinesmall_subset.xlsx');ds.SelectedVariableNames = {'Year','Month','DayofMonth','UniqueCarrier','ArrDelay'};preview(ds)

ans=8×5 table Year Month DayofMonth UniqueCarrier ArrDelay ____ _____ __________ _____________ ________ 1996 1 18 {'HP'} 6 1996 1 12 {'HP'} 11 1996 1 16 {'HP'} -13 1996 1 1 {'HP'} 1 1996 1 4 {'US'} -9 1996 1 31 {'US'} 9 1996 1 18 {'US'} -2 1996 1 26 {'NW'} -10 

Before importing data, you can specify what data types to use. For this example, import UniqueCarrier as a categorical variable.

 ds.SelectedVariableTypes(4) = {'categorical'};

Import data using the readall or read functions. The readall function requires that all the data fit into memory, which is true for the sample data. After the import, compute the maximum arrival delay for this dataset.

alldata = readall(ds);max(alldata.ArrDelay)/60

ans = 15.2333

For large data sets, import portions of the file using the read function. For more information, see Read Collection or Sequence of Spreadsheet Files.