Lesson 8.2: Excel files in MATLAB Video Lecture | Introduction to Programming with MATLAB - IT & Software

[MUSIC]
Microsoft Excel is a widely used tool for
analyzing data.
That's why so many programs support
the reading and writing of Excel files.
And MATLAB does too, with two built-in
functions called xlsread and xlswrite.
Here we see a spreadsheet open in Excel.
It contains monthly weather data for
Nashville, Tennessee.
As you can see, it contains not only
numerical data, but also various labels
for month names and weather data,
such as temperature and precipitation.
Assuming this data is saved in a file
called Nashville_climate.xlsx,
we can easily load it into MATLAB.
We do that by calling
the xlsread function with three
output arguments, num, txt, and raw.
The only input argument here is a string.
It's the name of the file.
You may be wondering,
why are there three different outputs?
Well, MATLAB's nice enough to separate
the numbers from the text for us.
Numbers in the Excel file are collected
and returned in the first output argument.
Text is returned in the second
output argument, and finally,
the third argument contains everything.
Depending on what you need,
you can use any or all of these outputs.
Let's look at the first argument, num.
Num is a matrix of doubles,
no surprise there.
But an interesting aspect
of num is it's size,
which is smaller than
the size of the spreadsheet.
What MATLAB does here is find the smallest
rectangle of cells in the spreadsheet that
contains all the numbers.
That rectangle may be only a subset
of all the cells in the spreadsheet,
as it is here in this example.
In this case it makes up 14 rows and
3 columns, so that's the size of num.
But there's a hitch with this plan.
MATLAB picks up all the cells
from this rectangular area, and
some of those cells may not
contain numerical data,
like this two by three non-numerical
array in the middle of our example.
It contains three empty cells
In three cells with text,
none of which is numerical data.
For these cells, MATLAB assigns
a special value in the output argument,
NaN, which means not a number.
So in this example, it puts a two
by three array of NaN, or nans,
in the middle of the output argument.
Text is returned in a similar fashion.
This time, the smallest rectangular area
is selected that contains
all the text cells.
The type of this second output argument,
TXT, is a cell array instead
of an array of characters.
That's the only way to return text.
Because there can be different
numbers of characters in the cell, so
it's not possible to return it as
a rectangular character array.
Now numbers turn into empty strings.
But what about those
strange looking entries,
1 by 30 char and
1 by 40 char at the top left?
With eight corresponding strings,
they're simply too long to print
conveniently in the command window.
So MATLAB just prints the type,
the dimensions plus the element type.
But don't worry,
all those data are there in those cells.
Finally, if we need everything
from the spreadsheet in one array,
we get that in the third argument.
It's a cell array, that's the union
of the previous two arrays.
All the data are here in one cell array,
containing doubles and strings.
And this time,
it's the smallest rectangle that
contains everything in the spreadsheet.
Okay.
Let's try this out in MATLAB.
Well, it's easy to implement pretty
much anything in PowerPoint.
But et me show you that it's really this
easy to handle Excel files in MATLAB.
First, let's make sure we have
the data we need in this directory.
What I want is this
Nashville climate data.
There it is right there.
It's an Excel file with
the ending xlsx here.
So first thing I want to do is
just read the numbers in the file.
To do that, we'll give just one
output argument instead of all three.
And there's our function.
Gee, that's a lot to type, and I'm lazy.
So what I'm going to do is just copy.
And paste.
There.
There's all our data.
How cool is that?
A single command, and
we have all the numeric data in the Excel
spreadsheet loaded into a MATLAB variable.
If we want both numerical and
textual data, we can do this.
There are the numbers,
there are the strings.
With these empty strings,
where there are no strings.
So, that's how you get both of them.
If you're not interested in
the numerical data at all and
you just want the textual data,
you can do that, too.
There's a new syntax we
haven't mentioned before,
that allows you to skip
an output argument.
Looks like this.
Instead of temps here,
I put a tilde and a comma.
You got to have the comma if
you're going to use a tilde.
And so this time,
we didn't get the numerical data,
just the second set of data.
And this works with any function
that has multiple output arguments.
And if all we care about is the entire
file, that last argument, we can do this.
Two tildes.
We don't want the numerical,
we don't want the text,
we want everything and
there is everything in that format,
where not a number is an empty cell, and
here is numbers over here and
here is strings and so on.
Often, we want to read from a specified
area within an Excel workbook.
Here's how to do that.
Here we read the data
from just a single cell.
The second argument here
specifies the sheet.
You can use the number, just like I
did here, one for f, the first sheet,
two for the second, and so on.
Or you can specify the name of the sheet
using a string in the second argument.
The third argument is
the name of the cell.
And you can specify
an area using this syntax.
Here we use Excel's own notation in the
third argument for a rectangle of cells.
Note that that colon right here
is not MATLAB's colon operator,
it's just a character in the string.
It indicates that T15 is the upper
left hand corner of the rectangular
array we want, and
E17 is the lower right hand corner.
We can also write data tech cell
files using the xlswrite function,
at least on Windows we can.
On a Mac, MATLAB provides
a somewhat limited alternative,
xlswrite, writes text files
instead of Excel files.
Only numeric arrays can be written.
And each row is written
on a separate line,
and the numbers on a row
are separated by commas.
This format, which is called the comma
separated value format or CSV,
is written and
read by many applications including Excel.
Because of this limitation,
we're not going to cover xlswrite here.
But the function is just as easy
to use as the xlsread function.
Only thing to keep in mind is that
you can overwrite the contents of
an existing file.
You won't get any warning.
The good news, however, is that you only
modify the cells that you actually write.
The other cells will not be affected.
Try Help xlswrite, or
read the book for more details.
[MUSIC]