5 Tips for Importing and Referencing Excel Sheets in MATLAB

Working with Excel spreadsheets in MATLAB can streamline your data analysis process, especially when dealing with large datasets or when you require importing financial data, scientific measurements, or any other structured information for analysis. MATLAB provides multiple functions and techniques to read from, write to, and manipulate Excel files, making it a powerful tool for both data scientists and engineers. Here are five tips to help you effectively import and reference Excel sheets in MATLAB.

1. Using MATLAB's Built-in Functions for Importing Excel Files

MATLAB offers a suite of functions like xlsread and readtable which can be used to import data from Excel files. Here's how you can proceed:

• xlsread: This function reads numerical, text, and empty cell data. Use the following syntax:

[num,txt,raw] = xlsread(filename,sheet,xlRange);

💡 Note: num contains numeric data, txt contains string data, and raw provides unprocessed cell data as it appears in Excel.

• readtable: For structured data, readtable creates a table variable in MATLAB, which can be especially useful when your Excel sheet has headers:

T = readtable(‘filename.xlsx’, ‘Sheet’, ‘Sheet1’);

2. Handling Large Datasets

When dealing with large datasets, importing the whole spreadsheet at once might not be memory-efficient. Here are some tips:

• Import only a part of the dataset using the xlRange parameter:

data = xlsread('bigdataset.xlsx', 'Sheet1', 'A1:D50');

• If you need dynamic import based on conditions or you're dealing with significantly large files, consider using readcell which can read data as a cell array:

data = readcell('largefile.xlsx');

• Use MATLAB's actxserver to open Excel directly:

e = actxserver('Excel.Application');
Workbook = e.Workbooks.Open('largefile.xlsx');
Range = Workbook.Sheets.Item(1).Range('A1:A100');
data = Range.Value;

🔍 Note: This method gives you more control but requires proper Excel COM support on your system.

3. Automating Imports and References

To make your data analysis more efficient, you can automate the process of importing data or referencing specific cells:

• Create scripts or functions to handle recurring imports or updates:

function importData(filename, sheet, range)
    data = xlsread(filename, sheet, range);
    % Further processing here
end

• Use MATLAB's ability to reference and name ranges in Excel for dynamic data access:

rangeToRead = 'NamedRange';
data = xlsread('dynamicdata.xlsx', rangeToRead);

4. Handling Data Types and Formating

Excel files can contain various data types. Here are some tips for dealing with them:

• Text Import: Use readtable or specify formats in xlsread to handle dates, times, or special formats:

T = readtable('data.xlsx', 'ReadVariableNames', false, 'DateLocale', 'en_US');

• Numeric Conversion: Ensure that numbers are correctly converted into MATLAB's numeric arrays:

data = xlsread('numericData.xlsx');
numData = cellfun(@str2double, data);

⚠️ Note: Be cautious with empty cells as they can be interpreted as NaN.

• Formatting: You can control how numbers are displayed in MATLAB using format:

format bank

5. Linking Data and MATLAB

To keep your MATLAB analysis up-to-date with real-time data:

• Use COM Automation to link to Excel:

e = actxserver('Excel.Application');
e.Visible = 1; % Make Excel visible
Workbook = e.Workbooks.Open('realtime.xls');
Sheet = Workbook.Sheets.Item(1);
linkID = actxcontrollink(Sheet.Range('A1'));
while true
    % Refresh data
    value = get(linkID, 'Value');
    % Do something with value
    pause(10); % Wait 10 seconds
end

🔗 Note: Linking requires both MATLAB and Excel to be running, which might not be suitable for background processes.

The integration of Excel with MATLAB significantly enhances your ability to manage and analyze data. By leveraging MATLAB's robust import tools, handling large datasets, automating processes, and linking with Excel, you can create a dynamic and efficient workflow that maximizes productivity. These five tips should serve as a guide to help you navigate the initial setup and ongoing use of Excel data within MATLAB. Remember to consider the memory footprint, especially with large files, and explore MATLAB's extensive documentation for more advanced techniques like array manipulation, file I/O operations, and automated data processing for your specific needs.

What’s the difference between xlsread and readtable?

+

xlsread primarily focuses on reading numerical, text, and raw cell data, providing separate outputs for each data type. readtable, on the other hand, reads data into a table format, which is beneficial for structured data with headers.

Can I import Excel files with MATLAB on Linux systems?

+

Yes, MATLAB’s xlsread and readtable functions work on Linux systems, but they might require additional libraries like POI or JExcelApi for Excel file support.

How do I handle dates and times when importing from Excel?

+

When importing dates and times, use readtable with the ‘DateLocale’ property to set the correct date format, or manually format the data post-import using MATLAB’s datetime function.