Skip to Main Content Skip to Search
Product Documentation

Importing Spreadsheets

On this page…

Ways to Import Spreadsheets

Select Spreadsheet Data Interactively

Import a Worksheet or Range with xlsread

Import All Worksheets in a File with importdata

System Requirements for Importing Spreadsheets

When to Convert Dates from Excel Files

Ways to Import Spreadsheets

There are several ways to read data from a spreadsheet file into the MATLAB workspace:

Alternatively, paste data from the clipboard into MATLAB using one of the following methods:

Some import options require that your system includes Excel for Windows. For more information, see System Requirements for Importing Spreadsheets.

Back to Top

Select Spreadsheet Data Interactively

This example shows how to import data from a spreadsheet or comma-separated value (CSV) file into the workspace with the Spreadsheet Import Tool. The worksheet in this example includes three columns of data labeled Station, Temp, and Date:

Station   Temp    Date
12          98    9/22/2010
13        x       10/23/2010
14          97    12/1/2010

Select File > Import Data. Alternatively, in the Current Folder browser, double-click the name of a file with an extension of .xls, .xlsx, .xlsb, .xlsm, or .csv. The Spreadsheet Import Tool opens.

Select the data range and the type of variable to create (matrix, column vectors, or cell array). For example, the data in the previous figure corresponds to a 3-by-3 matrix named untitled. You can edit the variable name within the tab, and you can select noncontiguous sections of data for the same variable.

If you choose to import as a matrix or column vectors, the tool highlights any nonnumeric data in the worksheet. Each highlight color corresponds to a proposed rule to make the data fit into a numeric array. You can add, remove, reorder, or edit rules, such as changing the replacement value from 0 to NaN, as shown.

All rules apply to the imported data only, and do not change the data in the file. You must specify rules any time the range includes nonnumeric data and you are importing into a matrix or column vectors.

Any cells that contain #Error? correspond to formula errors in your spreadsheet file, such as division by zero. The Import Tool regards these cells as nonnumeric.

For more information on interacting with the Import Tool, watch this video demo.

Import Data from Multiple Spreadsheets

If you plan to perform the same import operation on multiple files, you can generate code from the Import Tool to make it easier to repeat the operation. On all platforms, the Import Tool can generate a program script that you can edit and run to import the files. On Microsoft Windows systems with Excel software, the Import Tool can generate a function that you can call for each file.

For example, suppose you have a set of spreadsheets in the current folder named myfile01.xlsx through myfile25.xlsx, and you want to import the same range of data, A2:G100, from the first worksheet in each file. Generate code to import the entire set of files as follows:

  1. Open one of the files in the Import Tool.

  2. From the Import button, select Generate Function. The Import Tool generates code similar to the following excerpt, and opens the code in the Editor.

    function data = importfile(workbookFile, sheetName, range)
%IMPORTFILE    Import numeric data from a spreadsheet
...

  3. Save the function.

  4. In a separate program file or at the command line, create a for loop to import data from each spreadsheet into a cell array named myData:

    numFiles = 25;
range = 'A2:G100';
sheet = 1;
myData = cell(1,numFiles);

for fileNum = 1:numFiles
    fileName = sprintf('myfile%02d.xlsx',fileNum);
    myData{fileNum} = importfile(fileName,sheet,range);
end

Each cell in myData contains an array of data from the corresponding worksheet. For example, myData{1} contains the data from the first file, myfile01.xlsx.

Back to Top

Import a Worksheet or Range with xlsread

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

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

xlswrite('climate.xlsx', 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.xlsx', '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.xlsx', '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.xlsx', '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 a nonempty string, such as '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.xlsx:

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

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

Back to Top

Import All Worksheets in a File with importdata

The importdata function reads data from an Excel file into a structure. Continuing the example in Import a Worksheet or Range with xlsread, where the data includes column headers, a call of the form

climate = importdata('climate.xlsx')      % 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.xlsx does not include column headers, the call

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

returns

ndata = 
    Temperatures: [3x2 double]

Back to Top

System Requirements for Importing Spreadsheets

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

Restrictions

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

Back to Top

When to Convert Dates from Excel Files

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 on a system with Excel for Windows, or if you use the Spreadsheet Import Tool, you do not need to convert the dates before processing in MATLAB.

However, if you use xlsread or importdata to import a spreadsheet with dates stored as numbers, or if your system does not have Excel for Windows, 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.

ApplicationReference Date
MATLABJanuary 0, 0000
Excel for WindowsJanuary 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');

Back to Top

  

Importing XML DocumentsImporting XML Documents Importing Scientific Data FilesImporting 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

 © 1984-2012- The MathWorks, Inc.    -   Site Help   -   Patents   -   Trademarks   -   Privacy Policy   -   Preventing Piracy   -   RSS