Compare Two Excel Files and send report as E-mail in Oracle Apex

1. Overview

This document talks about Compare Two Excel Files with same column name and send report to E-mail in Oracle Apex. This has been achieved using Apex Collection.

2. Technologies and Tools Used

The following technologies has been used to achieve the expected output.

SQL/PLSQL
Oracle Apex

3. Use Case

An organization manages customer data in Oracle APEX and receives regular updates in Excel files from different sources. The goal is to compare the new Excel file with the existing data in the Oracle APEX application, identify differences, and synchronize the data accordingly.

This document explains how to achieve this requirement.

4. Architecture

Following steps explains in detail,

Step 1:

We needs to create region two Page Item and button to Upload files as below Screenshot.

Step 2:

Create Process to store data in two Apex Collection on Upload button Click as below Code.

BEGIN

IF APEX_COLLECTION.COLLECTION_EXISTS(‘CSVDATALOAD’) THEN

APEX_COLLECTION.TRUNCATE_COLLECTION(‘CSVDATALOAD’);

END IF;

IF NOT APEX_COLLECTION.COLLECTION_EXISTS(‘CSVDATALOAD’) THEN

APEX_COLLECTION.CREATE_COLLECTION(‘CSVDATALOAD’);

END IF;

IF APEX_COLLECTION.COLLECTION_EXISTS(‘CSVDATA’) THEN

APEX_COLLECTION.TRUNCATE_COLLECTION(‘CSVDATA’);

END IF;

IF NOT APEX_COLLECTION.COLLECTION_EXISTS(‘CSVDATA’) THEN

APEX_COLLECTION.CREATE_COLLECTION(‘CSVDATA’);

END IF;

for r1 in (select * from

apex_application_temp_files f, table( apex_data_parser.parse(

p_content => f.blob_content,

p_add_headers_row => ‘Y’,

— p_store_profile_to_collection => ‘FILE_PROV_CASH’,

p_file_name => f.filename,

p_skip_rows => 1 ) ) p

where f.name = :P11_UPLOAD_EXCEL

)

loop

APEX_COLLECTION.ADD_MEMBER(P_COLLECTION_NAME => ‘CSVDATALOAD’,

p_c001 => nvl(REPLACE(r1.col001,’-‘,”),0),

p_c002 => nvl(REPLACE(r1.col002,’-‘,”),0),

P_C003 => nvl(REPLACE(r1.col003,’-‘,”),0),

p_c004 => nvl(REPLACE(r1.col004,’-‘,”),0),

p_c005 => nvl(REPLACE(r1.col005,’-‘,”),0),

P_C006 => nvl(REPLACE(r1.col006,’-‘,”),0),

p_c007 => nvl(REPLACE(r1.col007,’-‘,”),0),

p_c008 => nvl(REPLACE(r1.col008,’-‘,”),0),

P_C009 => nvl(REPLACE(r1.col009,’-‘,”),0),

P_C010 => nvl(REPLACE(r1.col010,’-‘,”),0),

P_C011 => nvl(REPLACE(r1.col011,’-‘,”),0),

P_C012 => nvl(REPLACE(r1.col012,’-‘,”),0),

P_C013 => nvl(REPLACE(r1.col013,’-‘,”),0)

);

END LOOP;

for r1 in (select * from

apex_application_temp_files f, table( apex_data_parser.parse(

p_content => f.blob_content,

p_add_headers_row => ‘Y’,

p_file_name => f.filename,

p_skip_rows => 1 ) ) p

where f.name = :P11_UPLOAD_EXCEL2

)

loop

APEX_COLLECTION.ADD_MEMBER(P_COLLECTION_NAME => ‘CSVDATA’,

p_c001 => nvl(REPLACE(r1.col001,’-‘,”),0),

p_c002 => nvl(REPLACE(r1.col002,’-‘,”),0),

P_C003 => nvl(REPLACE(r1.col003,’-‘,”),0),

p_c004 => nvl(REPLACE(r1.col004,’-‘,”),0),

p_c005 => nvl(REPLACE(r1.col005,’-‘,”),0),

P_C006 => nvl(REPLACE(r1.col006,’-‘,”),0),

p_c007 => nvl(REPLACE(r1.col007,’-‘,”),0),

p_c008 => nvl(REPLACE(r1.col008,’-‘,”),0),

P_C009 => nvl(REPLACE(r1.col009,’-‘,”),0),

P_C010 => nvl(REPLACE(r1.col010,’-‘,”),0),

P_C011 => nvl(REPLACE(r1.col011,’-‘,”),0),

P_C012 => nvl(REPLACE(r1.col012,’-‘,”),0),

P_C013 => nvl(REPLACE(r1.col013,’-‘,”),0)

);

END LOOP;

end;

Step 3: Use the below Insert Apex Collection Data into Table.

DECLARE

CURSOR C2 IS

SELECT C001, C002, C003, C004 FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = ‘CSVDATALOAD’;

CURSOR C1 IS

SELECT C001, C002, C003, C004 FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = ‘CSVDATA’;

BEGIN

FOR I IN C2

LOOP

INSERT INTO upload_cs1(id, name, disgnation, salary)VALUES(I.C001, I.C002, I.C003, I.C004);

END LOOP;

FOR I IN C1

LOOP

INSERT INTO upload_cs2(id, name, disgnation, salary) VALUES(I.C001, I.C002, I.C003, I.C004);

END LOOP;

END;

Step 4: Create two reports for showing Apex Collection Data with below Code.

Report 1:

SELECT C001,C002,C003,C004 FROM APEX_COLLECTIONS WHERE COLLECTION_NAME=’CSVDATALOAD’

Report 2

SELECT C001,C002,C003,C004 FROM APEX_COLLECTIONS WHERE COLLECTION_NAME =’CSVDATA’

Step 5:

Report Screenshot

Step 6:

Create Report for Show Compare data. Below is Screenshot of report and Code.

select uc1.id, uc1.name, uc1.disgnation, uc1.salary,

case

when uc1.name !=uc2.name and uc1.disgnation !=uc2.disgnation

then ‘’ || ‘Name and Designation is not Matched’ || ‘’

when uc1.salary !=uc2.salary and uc1.disgnation !=uc2.disgnation

then ‘’ ||’Salary and Designation is not Matched’ || ‘’

when uc1.name !=uc2.name and uc1.disgnation !=uc2.disgnation

then ‘’ || ‘’ || uc2.name || ‘ and ‘|| uc2.disgnation || ‘’ || ‘Name and Designatin is not Matched in 2nd Excel’ || ‘’

when uc1.salary !=uc2.salary

then ‘’ || ‘’ || uc2.salary || ‘’ || ‘ Salary is not Matched in 2nd Excel’ || ‘’

when uc1.disgnation !=uc2.disgnation

then ‘’ || ‘’ || uc2.disgnation || ‘’ || ‘Designation is not Matched in 2nd table’ || ‘’

when uc1.name !=uc2.name

then ‘’ || ‘’ || uc2.name || ‘’ || ‘ Name is not Matched in 2nd table’ || ‘’

else ‘’ || ‘Matched’ || ‘’ end Diff

from upload_cs1 uc1, upload_cs2 uc2

where uc1.id=uc2.id;

Step 5:

Create Region with Email Page item and button to send the Compared report on mail as below screenshot.

Step 6:

Create Process to send mail with below code.

DECLARE

l_email VARCHAR2(100):= :P11_EMAIL;

l_body CLOB := NULL;

CURSOR c1 IS

SELECT uc1.id as id, uc1.name as name, uc1.DISGNATION as DISGNATION, uc1.salary as salary,

CASE

WHEN uc1.name != uc2.name AND uc1.DISGNATION != uc2.DISGNATION THEN

‘’ || ‘Name and Designation is not Matched’ || ‘’

WHEN uc1.salary != uc2.salary AND uc1.DISGNATION != uc2.DISGNATION THEN

‘’ || ‘Salary and Designation is not Matched’ || ‘’

WHEN uc1.name != uc2.name AND uc1.DISGNATION != uc2.DISGNATION THEN

‘’ || ‘’ || uc2.name || ‘ and ‘ || uc2.DISGNATION ||

‘’ || ‘Name and Designation is not Matched in 2nd Excel’ || ‘’

WHEN uc1.salary != uc2.salary THEN

‘’ || ‘’ || uc2.salary || ‘’ ||

‘ Salary is not Matched in 2nd Excel’ || ‘’

WHEN uc1.DISGNATION != uc2.DISGNATION THEN

‘’ || ‘Designation is not Matched’ || ‘’

WHEN uc1.name != uc2.name THEN

‘’ || ‘Name is not Matched’ || ‘’

ELSE

‘’ || ‘Matched’ || ‘’

END diff

FROM upload_cs1 uc1, upload_cs2 uc2 WHERE uc1.id = uc2.id;

BEGIN

l_body := l_body ||

‘<html>

<head>

<style>

table, th, td {

border: 1px solid black;

border-collapse: collapse;

padding: 15px;

}

</style>

</head>

<center>Customer Solutions</center>

<center>Issues below are either “Mismatch” or “At Risk”</center>

<center>Please direct questions to the consolidated operations issues mailbox (ankur.rai@doyensys.com)</center>

<tr>

<th style=”background-color:black; color:white;”>Designation</th>

<th style=”background-color:black; color:white;”>Salary</th>

<th style=”background-color:black; color:white;”>Difference</th>

</tr>’;

FOR r IN c1

LOOP

l_body := l_body ||

‘<tr>

<td bgcolor=”white”>’ || r.DISGNATION || ‘</td>

<td bgcolor=”white”>’ || r.salary || ‘</td>

</tr>’;

END LOOP;

l_body := l_body ||

‘</table>’ ||

‘ ’ ||

‘</body>’ ||

‘</html>’;

APEX_MAIL.SEND(

p_to => l_email,

p_from => ‘rai.ankur@hotmail.com’,

p_subj => ‘Mismatch in CSV File’,

p_body => l_body,

p_body_html => l_body

);

APEX_MAIL.PUSH_QUEUE;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLERRM);

END;

5. Screen Shot

Output:

Using above steps we can Compare two Excel File. –

Ankur Rai

I am Oracle Apex Developer.