MATLAB - Using XLSREAD to Import Excel Data

Microsoft Excel is a common data format, so it’s a good idea to learn how to work with it in the MATLAB environment. There are a couple of ways to import Excel data, and we’ll discuss how to use the XLSREAD command to do it.

Contents

• XLSREAD - When Data Contains Numbers
• XLSREAD - When Data Contains Header Lines, Column Headers, and Numbers
• XLSREAD - When Data Contains Both Numbers and Strings
• Other Options for XLSREAD
• Next Time: XLSWRITE

XLSREAD - When Data is Numerical

When the Excel data consists entirely of numbers, importing the data to MATLAB is straightforward. For example, take the Excel file, test.xls, shown below:

We can import the data into MATLAB using the following command:

%import the excel data into MATLAB [numericalData] = xlsread('test.xls'); 

numericalData =      1.0000   50.0000   66.1000     2.0000  100.0000   32.4000     3.0000  150.0000   70.5000     4.0000  200.0000   33.8000     5.0000  250.0000   14.0000     6.0000  300.0000  187.7000     7.0000  350.0000  105.4000     8.0000  400.0000   88.1000     9.0000  450.0000  102.6000    10.0000  500.0000   64.6000 

XLSREAD - When Data Contains Header Lines, Column Headers, and Numbers

Most of the time, you will probably be dealing with Excel data that contains header lines and column headers, as shown below. In this instance, it’s going to take a little more work to get the data that you want.

%import the excel data into MATLAB [numericalData, textData] = xlsread('test.xls'); 

numericalData =      1.0000   50.0000   66.1000     2.0000  100.0000   32.4000     3.0000  150.0000   70.5000     4.0000  200.0000   33.8000     5.0000  250.0000   14.0000     6.0000  300.0000  187.7000     7.0000  350.0000  105.4000     8.0000  400.0000   88.1000     9.0000  450.0000  102.6000    10.0000  500.0000   64.6000  textData =       [1x46 char]       ''         ''     [1x49 char]       ''         ''              ''       ''         ''     'Sample'       'Gen'    'Power' 

%use the following code to verify the contents of the cell array textData{1:2,1} 

Sure enough, it matches!

ans =  This data set was created on April 10th, 2009.  ans =  Blinkdagger.com owns all rights to this data set. 

XLSREAD - When Data Contains Both Numbers and Strings

What happens when your data consists of both numbers and strings, as shown in the example below? When you have a mixture of data types, the best way to import the Excel data is in the raw data format. In this format, the data is stored into a cell array.

%xlsread allows you to store the raw data into a cell array [numericalData, textData, rawData] = xlsread('test.xls'); 

rawData =       [1x46 char]    [NaN]    [     NaN]    [   NaN]     [1x49 char]    [NaN]    [     NaN]    [   NaN]     [      NaN]    [NaN]    [     NaN]    [   NaN]     'Sample'       'Gen'    'Power'       'Color'     [        1]    [ 50]    [ 66.1000]    'Green'     [        2]    [100]    [ 32.4000]    'Red'     [        3]    [150]    [ 70.5000]    'Blue'     [        4]    [200]    [ 33.8000]    'Green'     [        5]    [250]    [      14]    'Green'     [        6]    [300]    [187.7000]    'Blue'     [        7]    [350]    [105.4000]    'Red'     [        8]    [400]    [ 88.1000]    'Red'     [        9]    [450]    [102.6000]    'Black'     [       10]    [500]    [ 64.6000]    'Yellow' 

At this point, it will take a lot of cell array manipulation to get the data into the format that you want. Read on for some more tips on importing data!

Other Options for XLSREAD

• Specify WorkSheet - Let’s say you have the following Excel file. Notice that the data is on Sheet 2 now.

  

  By default, XLSREAD reads the first sheet within the Excel File. You can specify which sheet you want to read the data from by doing the following:

  %specify which sheet you want to import data from [numericalData] = xlsread('test.xls','Sheet1'); 

• Specify Range - Let’s say you have the following Excel file, and you only want to extract the information in the red rectangle.

  

  You can do this by using the following code:

  %specify the sheet and the range you want to import [numericalData] = xlsread('test.xls','Sheet1','A9:C14'); 

• Cut and paste directly into MATLAB: This one doesn’t have anything to do with XLSREAD. It’s a technique that I use sometimes when I need to import data quickly. First, I create an empty variable, and then open up the array editor.

  data = [];  %opens up the data variable in the Variable Editor openvar data; 

  Alternatively, you can open up the Variable Editor by double clicking on the data variable from within the main workspace.



Finally, paste the data back into the empty array