This article is a quick tutorial on using the xlsread MATLAB command to import data from an Excel file into MATLAB. Specifically, you’ll learn to:
- Extract data from Excel in MATLAB
- Remove irrelevant content from your extract
- Import data from a specific sheet
- Select the Excel file to read
- Extract different formats
How to Extract Data from Excel in MATLAB
Xlsread Basic Syntax
Here’s the basic syntax to import data from Excel into MATLAB:
[numbers text textAndNumbers] = xlsread(excelFileName)
- numbers: numbers contained in your Excel file.
- text: text contained in your Excel file.
- textAndNumbers: text and numbers contained in your Excel file.
Xlsread MATLAB Example
First, let’s look at some details about how this works through an actual example. Let’s say I have the following Excel file named “excelFile.xlsx:”
Then, applying the previous basic syntax to read it, we get:
excelFileName = 'excelFile.xlsx';[numbers text textAndNumbers] = xlsread(excelFileName);
So, let’s examine the outputs of the xlsread MATLAB command:
As you can see, the three outputs are not of the same data type:
- “numbers” is an array: this means that you can access the content of an element using parentheses: numbers(i)
- “text” is a cell array: this means that you can access the content of a cell using braces: text{i}
- “numbersAndText” is a cell array: this means that you can access the content of a cell using braces: numbersAndText{i}
Remove Irrelevant Content from Your Extract
- Notice that for the “numbers” variable, the first and last rows have been removed, and the text has been replaced by “NaN” value. You can get rid of the “NaN” values with the following:
numbers = numbers(~isnan(numbers)); % delete numbers that are not "NaN"
The “numbers” variable then becomes:
- The numbers contained in the “text” variable have been replaced by empty cells. You can get rid of the empty cells with the following:
text = text(~cellfun('isempty', text)); % delete empty cells
The “text” variable then becomes:
Import Data from a Specific Sheet
What If the Content Is in Another Sheet?
Let’s use the previous Excel file “excelFile.xlsx” with the content on a sheet named “Sheet1” (which is the default name of a sheet if you create an Excel file).
Moreover, if you add a sheet to the Excel file, the name of the sheet will be “Sheet2.” So, let’s do that and move the first sheet (with the previous content) to the right of the second sheet and save the file:
Then, we’ll apply the xlsread MATLAB command as we did previously:
excelFileName = 'excelFile.xlsx';[numbers text textAndNumbers] = xlsread(excelFileName);
By default, xlsread will read the sheet located at the very left of the Excel file, namely “Sheet2.”
Since “Sheet2” is empty, it makes sense that the extracted content is empty. So, how do you read “Sheet1”?
Finding a Sheet
First, there are two ways to specify the sheet to read using the xlsread MATLAB command:
- Using the number of the sheet:
[numbers text textAndNumbers] = xlsread(excelFileName, sheetNumber);
The number of the sheet is “2” here because we want to read the second sheet (counting from the left). So, giving the “sheetNumber” variable the value “1” is equivalent to not using the second argument of the xlsread MATLAB command. Here’s how to extract the desired sheet:
sheetNumber = 2; % second sheet counting from the leftexcelFileName = 'excelFile.xlsx';[numbers text textAndNumbers] = xlsread(excelFileName, sheetNumber);
- Using the name of the sheet:
[numbers text textAndNumbers] = xlsread(excelFileName, sheetName);
The sheet name in that example is “Sheet1,” so you can use it as the second argument if you want to extract the second sheet:
sheetName = 'Sheet1';excelFileName = 'excelFile.xlsx';[numbers text textAndNumbers] = xlsread(excelFileName, sheetName);
In both cases, if the sheet number or the sheet name you’re referring to doesn’t exist, you’ll get the following error:
Specific Problems and Solutions
Ask the User to Select an Excel File
Using the uigetfile MATLAB command, you can ask the user to find and select the desired excel file to be read:
[fileName, pathName] = uigetfile({'*.xlsx';'*.xls'}, 'Choose an Excel file');
You can then use the “fileName” and the “pathName” (respectively the name of the selected file and its location) to read your Excel file.
There are 2 ways of doing that:
- Moving to the file’s location:
[fileName, pathName] = uigetfile({'*.xlsx';'*.xls'}, 'Choose an Excel file');currentFolder = pwd; % save the current locationcd(pathName)[numbers text textAndNumbers] = xlsread(fileName)cd(currentFolder);
We use the pwd MATLAB command to save the current location from before, move to the Excel file’s location, perform the extraction, and move back to the initial location.
- Specifying the location:
[fileName, pathName] = uigetfile({'*.xlsx';'*.xls'}, 'Choose an Excel file');fullFileExcelFile = fullfile(pathName, fileName); % create the path to the Excel file[numbers text textAndNumbers] = xlsread(fullFileExcelFile);
fullfile creates the path by adding ‘/’ or ‘\’ between the file name and the path name (you could do it yourself with something like [pathName ‘/’ fileName], but this can be a little bit trickier depending on whether you use a UNIX or Windows platform).
Be Careful About the Format of the Cells You’re Extracting
First, if the numbers contained in your Excel file are formatted as strings, you’ll get a cell array when extracting. For example, by adding a single quotation mark to the left of every number in the Excel file “excelFile.xlsx” that we’ve used previously, we are formatting them as strings:
And if we import the data from the Excel file now, we get:
Since the numbers have been formatted as strings in the Excel file, there are no numbers anymore, the “numbers” variable is empty and the “text” and “textAndNumbers” variables have become identical.
The Empty Cell Issue
If you have empty cells in your Excel file before the first row, the xlsread MATLAB command will get rid of them. This is a problem if you want to write the content back to the Excel file (e.g. to modify a value) because you won’t be able to know where to write it.
Unfortunately, there is no easy way to get that information using xlsread. If you have this issue, you can refer to this article for a workaround: https://realtechnologytools.com/matlab-row-number/
Key takeaways:
- To read an Excel file, use:
[numbers text textAndNumbers] = xlsread(excelFileName);
- numbers: array of the numbers in the file, access an element using numbers(i)
- text: cell array of the text in the file, access an element using text{i}
- textAndNumbers: cell array of the text and numbers in the file, access an element using textAndNumbers{i}
- To remove irrelevant values from your extract, use:
numbers = numbers(~isnan(numbers)); % delete numbers that are "NaN"text = text(~cellfun('isempty', text)); % delete empty cells
- To import a specific sheet in MATLAB, use:
[numbers text textAndNumbers] = xlsread(excelFileName, sheetName); % use the sheet name[numbers text textAndNumbers] = xlsread(excelFileName, sheetNumber); % use the sheet number
- There are 2 ways to ask the user to select an Excel file and read it:
- By moving to the file’s location:
[fileName, pathName] = uigetfile({'*.xlsx';'*.xls'}, 'Choose an Excel file');currentFolder = pwd; % save the current locationcd(pathName)[numbers text textAndNumbers] = xlsread(fileName)cd(currentFolder);
- By specifying the location:
[fileName, pathName] = uigetfile({'*.xlsx';'*.xls'}, 'Choose an Excel file');fullFileExcelFile = fullfile(pathName, fileName); % create the path to the Excel file[numbers text textAndNumbers] = xlsread(fullFileExcelFile);
- By moving to the file’s location:
- If the numbers in your Excel file are formatted as strings (e.g. using a single quotation mark at the left of a number in the Excel file), then they will be extracted in the “text” variable rather than in the “numbers” variable.
You can find more information about the xlsread MATLAB function in the MathWorks documentation: https://fr.mathworks.com/help/matlab/ref/xlsread.html
🌲 If you want to learn more about the practical tips that helped me stop wasting time doing mindless work, such as working with Excel files and writing reports, I wrote a small reference book about it:
