Importing Excel Spreadsheets :: Importing Data (MATLAB®)

Learn more about MATLAB

Importing Excel Spreadsheets

There are several ways to read data from an Excel^® spreadsheet file into the MATLAB workspace:

Some import options require that your system includes Excel for Windows^®. For more information, see Excel Version Support.

Note Excel and MATLAB can store dates as strings (such as '10/31/96') or numbers (such as 35369 or 729329). If your file includes numeric dates, see Converting Dates.

Pasting Excel Data from the Clipboard

Paste Excel data from the clipboard into MATLAB using one of the following methods:

Select Edit > Paste to Workspace.
Call uiimport -pastespecial.
Open an existing variable in the MATLAB Variable Editor, and select Edit > Paste Excel Data.

Selecting the Spreadsheet and Variables Interactively

The Import Wizard is a graphical user interface that helps you find a file, specify a worksheet, and define the variables to use in the workspace. For worksheets with row or column headers, the Import Wizard allows you to create variables based on the rows or columns. However, you cannot specify a range of data to import.

To start the Import Wizard, use one of the following methods:

Select File > Import Data.
Double-click a file name in the Current Folder browser.
Call uiimport.

Reading a Specific Worksheet and Range of Data

Consider the file climate.xls created with xlswrite as follows:

d = {'Time', 'Temp';
     12 98;
     13 99;
     14 97}

xlswrite('climate.xls', d, 'Temperatures');

To import the numeric data into a matrix, use xlsread with a single return argument. xlsread ignores any leading row or column of text in the numeric result:

ndata = xlsread('climate.xls', 'Temperatures')

ndata =
    12    98
    13    99
    14    97

To import both numeric data and text data, specify two return values for xlsread:

[ndata, headertext] = xlsread('climate.xls', 'Temperatures')

ndata =
    12    98
    13    99
    14    97

headertext = 
    'Time'    'Temp'

To read only the first row of data, specify the range:

firstrow = xlsread('climate.xls', 'Temperatures', 'A2:B2')

firstrow =
    12    98

Getting Information about a Spreadsheet

To determine whether a file contains a readable Excel spreadsheet, use the xlsfinfo function . For readable files, xlsfinfo returns the string 'Microsoft Excel Spreadsheet'. Otherwise, it returns an empty string ('').

You also can use xlsfinfo to identify the names of the worksheets in the file, and to obtain the file format reported by Excel.

For example, retrieve information on the spreadsheet climate.xls:

[type, sheets] = xlsfinfo('climate.xls')

type =
Microsoft Excel Spreadsheet
sheets = 
    'Sheet1'    'Sheet2'    'Sheet3'    'Temperatures'

Reading Data from All Worksheets into a Structure

The importdata function reads data from an Excel file into a structure. Continuing the example in Reading a Specific Worksheet and Range of Data, where the data includes column headers, a call of the form

climate = importdata('climate.xls')      % with column headers

returns the nested structure array

climate = 
          data: [1x1 struct]
      textdata: [1x1 struct]
    colheaders: [1x1 struct]

Structures created from Excel files with row headers include the field rowheaders, which also contains a 1-by-1 structure.

The structure named data contains one field for each worksheet with numeric data. The other structures contain one field for each worksheet with text cells or headers. In this case:

climate.data = 
    Temperatures: [3x2 double]

climate.textdata = 
    Temperatures: {'Time'  'Temp'}

climate.colheaders = 
    Temperatures: {'Time'  'Temp'}

If the Excel file contains only numeric data (no row or column headers, and no inner cells with text), the output structure is simpler. importdata returns a 1-by-1 structure, with one field for each worksheet with data.

For example, if the Temperatures worksheet in climate_nums.xls does not include column headers, the call

ndata = importdata('climate_nums.xls')      % only numeric data

returns

ndata = 
    Temperatures: [3x2 double]

Excel Version Support

If your system has Excel for Windows installed, including the COM server (part of the typical installation of Excel):

All MATLAB import options support XLS, XLSX, XLSB, and XLSM formats. xlsread also imports HTML-based formats.
If you have Excel 2003 installed, but want to read a 2007 format (such as XLSX, XLSB, or XLSM), install the Office 2007 Compatibility Pack.
xlsread includes an option to open Excel and select the range of data interactively. To use this option, call xlsread with the following syntax:
```
mydata = xlsread(filename, -1)
```

Restrictions

If your system does not have Excel for Windows installed, or the COM server is not available:

The Import Wizard, importdata, and xlsread only read XLS files compatible with Excel 97-2003.
You can specify a worksheet to read in the Excel file with the xlsread function, but you cannot specify a range of data. See the xlsread reference page for additional information.

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

Converting Dates

In both MATLAB and Excel applications, dates can be represented as character strings or numeric values. For example, May 31, 2009, can be represented as the character string '05/31/09' or as the numeric value 733924. Within MATLAB, The datestr and datenum functions allow you to convert easily between string and numeric representations.

If you import a spreadsheet with dates stored as strings, you do not need to convert the dates before processing in MATLAB.

However, if you import a spreadsheet with dates stored as numbers, you must convert the dates. Both Excel and MATLAB represent numeric dates as a number of serial days elapsed from a specific reference date, but the applications use different reference dates.

The following table lists the reference dates for MATLAB and Excel. For more information on the 1900 and 1904 date systems, see the Excel help.

Application	Reference Date
MATLAB	January 0, 0000
Excel for Windows	January 1, 1900
Excel for the Macintosh^®	January 2, 1904

Example — Importing an Excel File with Numeric Dates

Consider the hypothetical file weight_log.xls with

Date       Weight
10/31/96   174.8
11/29/96   179.3
12/30/96   190.4
01/31/97   185.7

To import this file, first convert the dates within Excel to a numeric format. In Windows, the file now appears as

Date       Weight
35369      174.8
35398      175.3
35429      190.4
35461      185.7

Import the file:

wt = xlsread('weight_log.xls');

Convert the dates to the MATLAB reference date. If the file uses the 1900 date system (the default in Excel for Windows):

datecol = 1;
wt(:,datecol) = wt(:,datecol) + datenum('30-Dec-1899');

If the file uses the 1904 date system (the default in Excel for the Macintosh):

datecol = 1;
wt(:,datecol) = wt(:,datecol) + datenum('01-Jan-1904');

Importing XML Documents

Importing Scientific Data Files

Recommended Products

Get MATLAB trial software

Includes the most popular MATLAB recorded presentations with Q&A sessions led by MATLAB experts.

Get the Interactive Kit