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 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:
[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.
[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'
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.
[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. ass=”comment”>%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:
[numericalData] = xlsread('test.xls','Sheet2');
- 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:
[numericalData] = xlsread('test.xls','Sheet2','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 = [];
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 into the empty array.
Next Time: XLSWRITE
Next time, we will discuss the intricacies of XLSWRITE.
