The New Spreadsheet Import Tool 1

Posted by Michael Katz, November 7, 2011

One of the most exciting new features in MATLAB R2011b is the Spreadsheet Import Tool. This tool makes it easy to import data from Microsoft Excel and comma-separated value (CSV) files. The spreadsheet import tool allows you to preview the file and then select the range and format of the data to import. To get started it's as easy as double-clicking the any .csv or .xls file in the Folder Browser.

For this example, I went to to get a random CSV file. It's an awesome place for getting sample data sets or to answer burning questions like "how much Sorghum did the US import in the 70′s?"

Here's what the new tool looks like when I use it to import a CSV file:

By default the tool wants to import the data as a matrix, using zeroes to fill in gaps. By using the toolbar, you can quickly customize this process. The first drop-down lets you choose between matrix, column vector, or cell array for the data type. The matrix and cell options will import into one variable, and the column vector will create a variable for each selected column. The variables can be quickly renamed by typing in the trapezoidal area at the top of the selection.

This tool is highly interactive. You can quickly choose a subrange using all the normal multi-select gestures (e.g. shift & control click) for your platform. The selections don't have to be contiguous, but each column has to have the same rows selected, which the tool enforces when you drag around a selection. You can also select a whole row or column by click on its respective header.

You also have several options for deciding how to treat blank or non-numeric data. You can set up rules to have rows or columns containing such data automatically excluded, or to have that data replaced by a number, Inf, or NaN. When you do this, those cells are a highlighted in different colors with the new values super-imposed over the original. This allows you to quickly scan the data to make sure you get the desired results.

Finally you can import immediately into the workspace or generate a script or function that would allow you to process other files in the same manner. This is particularly useful if you work with multiple data sets generated in the same fashion.

For a more dynamic look at how to use this tool, watch the short video.

Data Tools