Comparison of excel sheet data and output should be excel sheet for matched and mismatched data using simulink model.

I have two excel sheets, Data1 and Data2. I need to compare both the excel sheet and atlast I need to get excel sheet as an output for matched data and mismatched data using simulink model. Tell me how to read the excel sheet, compare it and and how to get the excel sheet as an output?

Answer 1

[Tejas]

Hello Divyashree,

To compare two Excel files in Simulink, create a .M script that reads the Excel files and extracts matched and mismatched data. This script can then be called from a 'MATLAB Function Block' within Simulink.

Below is an example code snippet that demonstrates how to load and compare the Excel files. This example performs a row-wise comparison and creates an Excel file with two sheets: one containing matched rows and the other containing mismatched rows.

function compareExcelSheets()
    
    % Read the excel sheets
    data1 = readtable('Data1.xlsx');
    data2 = readtable('Data2.xlsx');

    % Specify names of columns to consider for comparison
    columnsToCompare = {'Column_name_1', 'Column_name_2', 'Column_name_3'};  % Replace with your actual column names

    matchedData = [];
    mismatchedData = [];

    % Compare the data row by row
    for i = 1:min(height(data1), height(data2))
        isMatched = true;
        for col = columnsToCompare
            colName = col{1}; 
            if ~isequal(data1{i, colName}, data2{i, colName})
                isMatched = false;
                break;  
            end
        end

        % Add row to matched or mismatched data
        if isMatched
            matchedData = [matchedData; data1(i, :)];
        else
            mismatchedData = [mismatchedData; data1(i, :)];
        end
    end

    outputFileName = 'ComparisonResult.xlsx';
    writetable(matchedData, outputFileName, 'Sheet', 'MatchedData');
    writetable(mismatchedData, outputFileName, 'Sheet', 'MismatchedData');
end

In the 'MATLAB Function Block' within Simulink, add this code to call the script. Make sure the .M script is on MATLAB path.

coder.extrinsic('compareExcelSheets');
compareExcelSheets();  

For more details on the solution, kindly refer to the documentation below:

https://www.mathworks.com/help/releases/R2019b/simulink/slref/coder.extrinsic.html

[Divyashree]

Hello Tejas,

I wrote a .m script and the matlab function as mentioned above.

compareExcelSheet.m

function compareExcelSheets()
% Read the Excel sheets
data1 = readtable('Maintainance_database.xlsx');
data2 = readtable('NVM_database.xlsx');

% Specify names of columns to consider for comparison
columnsToCompare = {'Serial_Number', 'Part_Number', 'Aircraft_Registration_Number', 'LRU_Position'}; 

matchedData = [];
mismatchedData = [];

% Compare the data row by row
for i = 1:min(height(data1), height(data2))
    isMatched = true;
    for col = columnsToCompare
        colName = col{1}; 
        if ~isequal(data1{i, colName}, data2{i, colName})
            isMatched = false;
            break;  
        end
    end
    
    % Add row to matched or mismatched data
    if isMatched
        matchedData = [matchedData; data1{i, :}];
    else
        mismatchedData = [mismatchedData; data1{i, :}];
    end
end

% Convert cell arrays to matrices
matchedMatrix = cell2mat(matchedData);
mismatchedMatrix = cell2mat(mismatchedData);

% Write the results to an Excel file
outputFileName = 'ComparisonResult.xlsx';
writematrix(matchedMatrix, outputFileName, 'Sheet', 'MatchedData');
writematrix(mismatchedMatrix, outputFileName, 'Sheet', 'MismatchedData');
end

I made few changes in the code as I got few errors in the code when I executed it. And later called the script in the matlab function block.

compareExcelSheets.slx

function callCompareExcelSheets()
coder.extrinsic('compareExcelSheets');  % Declare the external function
compareExcelSheets();  % Call the external function
end

Before executing the I ran the below command :

addpath('path of the .m script');

I got the output as an excel sheet. But the data in the excel sheet is not organised.

In one cell all the matched data is stored.

Column

data1data2data3data4

I need output as shown below:

col1 col2 col3 col4

data1 data2 data3 data4

[Tejas]

Hello Divyashree,

The reason the data is being added to a single column might be related to how it is stored in the Excel sheets. Could you please share the Excel sheets with me? This way, I can modify the code accordingly.

[Divyashree]

Hello Tejas,

I am sharing the sample excel sheets with you. As I cannot share the office data. It is similar as the excel sheet I've mentioned below.

test1.xlsx

The above mentioned is 1st excel sheet. Second excel sheet is same as the above and named as test2.xlsx.

[Walter Roberson]

matchedData = [];
mismatchedData = [];

Those are plain arrays

    if isMatched
        matchedData = [matchedData; data1{i, :}];
    else
        mismatchedData = [mismatchedData; data1{i, :}];
    end

The {} indexing indexes the table and returns a plain array. So you have [;] operations between a plain array and plain array, which will give a plain array in return.

matchedMatrix = cell2mat(matchedData);
mismatchedMatrix = cell2mat(mismatchedData);

cell2mat applied to a plain array would be an error.

.... possibly your arrays are stored as cell arrays inside the table. If so then you would be [;] together cell arrays, getting out a cell. cell2mat() of that would "flatten" the cell arrays, probably given a character array as a result. But writing a character array would give a block of text as a result, with no delimeter between the entries.

I suspect that you need to get rid of the cell2mat() calls.

[Tejas]

Hello Divyashree,

Based on the Excel file provided earlier, I have created two sample files to test the code. Below are screenshots of these files:

test1.xlsx

test2.xlsx

Here is the updated code for the 'compareExcelSheets' function.

function compareExcelSheets()

data1 = readtable('test1.xlsx');
data2 = readtable('test2.xlsx');

columnsToCompare = {'data1', 'data2', 'data3','data4'};

matchedData = table();
mismatchedData = table();

for i = 1:min(height(data1), height(data2))
    isMatched = true;
    for col = columnsToCompare
        colName = col{1}; 
        if ~isequal(data1{i, colName}, data2{i, colName})
            isMatched = false;
            break;  
        end
    end
    
    if isMatched
        matchedData = [matchedData; data1(i, :)];
    else
        mismatchedData = [mismatchedData; data1(i, :)];
        mismatchedData = [mismatchedData; data2(i, :)];
    end
end

outputFileName = 'ComparisonResult.xlsx';
writetable(matchedData, outputFileName, 'Sheet', 'MatchedData');
writetable(mismatchedData, outputFileName, 'Sheet', 'MismatchedData');
end

Additionally, here are the screenshots of the 'ComparisonResult.xlsx' file:

[Divyashree]

Hello Tejas,

Thank you so much. It worked.

I updated the code as mentioned above. But the output file is in different path.

Suppose if I give 'C:\Users\Divya\Documents\MATLAB\folder1', the excel sheet is not getting generated in this path. It is getting generated in the different folder, like 'C:\Users\Divya\Documents\MATLAB\folder2'.

After giving the command addpath 'C:\Users\Divya\Documents\MATLAB\folder1', the output file is getting generated in different path.

[Walter Roberson]

data1 = readtable('test1.xlsx');
data2 = readtable('test2.xlsx');

readtable will search along the MATLAB path looking for test1.xlsx and test2.xlsx . Those two are potentially in different folders. So you would need something like

filename1 = 'test1.xlsx';
filename2 = 'test2.xlsx';

w1 = which(filename1);
if isempty(w1)
    error('file not found: "%s"', filename1);
end

w2 = which(filename2);
if isempty(w2)
    error('file not found: "%s"', filename2);
end

d1 = fileparts(w1);
d2 = fileparts(w2);
if ~strcmp(d1, d2)
    error('test1 is in a different directory than test2, "%s" vs "%s". Cannot decide where to save results', d1, d2);
end
savefolder = d1;
%...
%...
outputFileName = fullfile(savefolder, 'ComparisonResult.xlsx');

This will have the effect of saving ComparisonResult.xlsx in whatever directory test1.xlsx happens to live in (provided that it is the same directory as test2.xlsx )

It sure would be easier if the files were in a consistent location relative to running the script, instead of having to fish around looking for the files.

[Divyashree]

Hello Walter,

Should I integrate the code which you shared with the updated code which Tejas had shared. Or should I create another .m script and call in the function model. How is it?

[Walter Roberson]

filename1 = 'test1.xlsx';
filename2 = 'test2.xlsx';

w1 = which(filename1);
if isempty(w1)
    error('file not found: "%s"', filename1);
end

w2 = which(filename2);
if isempty(w2)
    error('file not found: "%s"', filename2);
end

d1 = fileparts(w1);
d2 = fileparts(w2);
if ~strcmp(d1, d2)
    error('test1 is in a different directory than test2, "%s" vs "%s". Cannot decide where to save results', d1, d2);
end
savefolder = d1;

columnsToCompare = {'data1', 'data2', 'data3','data4'};
matchedData = table();
mismatchedData = table();
for i = 1:min(height(data1), height(data2))
    isMatched = true;
    for col = columnsToCompare
        colName = col{1}; 
        if ~isequal(data1{i, colName}, data2{i, colName})
            isMatched = false;
            break;  
        end
    end
    
    if isMatched
        matchedData = [matchedData; data1(i, :)];
    else
        mismatchedData = [mismatchedData; data1(i, :)];
        mismatchedData = [mismatchedData; data2(i, :)];
    end
end

outputFileName = fullfile(savefolder, 'ComparisonResult.xlsx');
writetable(matchedData, outputFileName, 'Sheet', 'MatchedData');
writetable(mismatchedData, outputFileName, 'Sheet', 'MismatchedData');

[Divyashree]

Thanks Walter, It worked:)