MATLAB: How to import the data from a password protected XLSX file in MATLAB

dataimportMATLABpasswordprotectedxlsx

How to import the data from a password protected XLSX file in MATLAB?

I have to import data from a password protected XLSX file. How can I do that in MATLAB?

Best Answer

To import the data from the password protected XLSX file you can use any of the following possible solutions:

1. You can import the data into MATLAB interactively, using the Import Tool. Please refer to the following link for more information on using the Import Tool:

https://www.mathworks.com/help/releases/R2017a/matlab/ref/importtool-app.html

Here, once you select the XLSX file to import the data, you need to enter the password for the file in the Password window that comes up.

2. You can create a table from the file using the "readtable" function. Here, after executing the following command in the Command Window, you need to enter the password for the file (specified in the filename) in the Password window that comes up:

>> T = readtable(filename);

Starting with R2018b, you will need to manually direct MATLAB to use Excel to open the file:

>> T = readtable(filename, 'UseExcel', true);

Please refer to the following documentation for more information on using the "readtable" function:

https://www.mathworks.com/help/releases/R2017a/matlab/ref/readtable.html

3. You can access the spreadsheet data from MATLAB by running the Excel application in an Automation server process using the "actxserver" function. Please refer to the attached example script, "passwordProtectedRead.m" that demonstrates reading the data from a password protected XLSX file, "passwordExample.xlsx", using the "actxserver" function. Also, please refer to the following link for more information on reading spreadsheet data using Excel as Automation server:

https://www.mathworks.com/help/releases/R2017a/matlab/matlab_external/example-reading-excel-spreadsheet-data.html#bqeom1z

Related Solutions

MATLAB: How to create Excel spreadsheet from HTML report

Attached (and also copy-pasted below) is an example script that illustrates one such approach to convert the HTML file to an Excel spreadsheet as per the following workflow:

1) Read in the HTML file

2) Remove specific sections/patterns of text (via regular expression search and replacement)

3) Parse the remaining text based on a specified delimiter and format

4) Export the result to an Excel Spreadsheet

Please note that this example is provided simply to illustrate the application of these functions and outline an example of a general approach. This example code would likely need to be adapted and tuned for a particular workflow and use-case, since the implementation is highly dependent on the requirements therein.

%%Read in HTML file.
filenameHTML = 'Example.html';
txt = fileread('Example.html');
%%Remove HTML tags, header text, and last section (pertaining to images).
txt = regexprep(txt,'<script.*?/script>','');
txt = regexprep(txt,'<style.*?/style>','');
txt = regexprep(txt,'<.*?>','');
txt = regexprep(txt,'.*#\n','');
txt = regexprep(txt,'--.*?\n','');
txt = regexprep(txt,'\n\n.*','');
%%Set up delimiters and format specification to read columns of data as text:
% For more information, see the TEXTSCAN documentation.
delimiter = {' = '};
formatSpec = '%q%q%[^\n\r]';
%%Read columns of data according to the format.
dataArray = textscan(txt, formatSpec, 'Delimiter', delimiter, ...
    'TextType', 'char',  'ReturnOnError', false);
raw = repmat({''},length(dataArray{1}),length(dataArray)-1); %preallocation before loop
for col = 1:(length(dataArray)-1)
    raw(1:length(dataArray{col}),col) = dataArray{col};
end;
%%Write data to Excel spreadsheet.
filenameSpreadsheet = 'Example.xlsx';
xlswrite(filenameSpreadsheet,raw)

The following links provide additional information and references to the functions that I used, along with their syntax, arguments, parameters, and options.

https://www.mathworks.com/help/releases/R2018a/matlab/ref/fileread.html

https://www.mathworks.com/help/releases/R2018a/matlab/ref/regexprep.html

https://www.mathworks.com/matlabcentral/answers/973-how-do-i-read-text-from-html-file

https://www.mathworks.com/help/releases/R2018a/matlab/ref/textscan.html

https://www.mathworks.com/help/releases/R2018a/matlab/ref/xlswrite.html

MATLAB: Does “readtable” improperly read Excel data with “N/A” values, row names, and headers

To customize the way MATLAB imports your table from Excel, supply "readtable" with a second argument that specifies the import options, "opts":

https://www.mathworks.com/help/matlab/ref/readtable.html?s_tid=doc_ta#d117e1212973

To generate these options, use the "detectImportOptions" function, making sure to include the .xlsm extension at the end of your filename:

https://www.mathworks.com/help/matlab/ref/detectimportoptions.html?s_tid=doc_ta#d117e309599

This should take care of most of your issues, including converting all your data to numbers and changing "N/A" values to NaNs. However, there are several additional Name/Value pairs you will have to pass into the "detectImportOptions" function. Specifically, you will need to specify where your variable names, row names, and data entries start using some of the "Parameters for Spreadsheet Files Only" listed in the documentation:

https://www.mathworks.com/help/matlab/ref/detectimportoptions.html?s_tid=doc_ta#d117e309599

Here are the properties you will need to specify:

Set "DataRange" to "B2" since your data start in cell "B2" of your Excel sheet
Set "RowNamesRange" to "A2" since your row names start in cell "B2" of your Excel sheet
Set "VariableNamesRange" to "B1" since your column headers start in cell "B2" of your Excel sheet

Make sure to specify these properties as Name/Value pairs when initially calling the "detectImportOptions" function.

Here's an example of how to implement the steps above using the attached file "example.xlsm":

filename = "example.xlsm";
opts = detectImportOptions(filename,'DataRange','B2','RowNamesRange','A2','VariableNamesRange','B1');
tb = readtable(filename,opts)

Note how different this is from the initial attempt to use "readtable" without the input options object:

filename = "example.xlsm";
tb = readtable(filename)

Best Answer

Related Solutions

MATLAB: How to create Excel spreadsheet from HTML report

MATLAB: Does “readtable” improperly read Excel data with “N/A” values, row names, and headers

Related Question