Compare each excel sheets with a table in oracle

Question

I have an xlsx file and I have loaded most of the sheets in transposed manner in oracle.

Ex:- Xlsx :

Roll no	Name.	Age	Job
1	Harshita	25	IT

Oracle :

Roll No	Parame	Param_Value
1	Name	Harshita
1	Age	25
1	Job	IT

Now, roll number 1 in the Oracle table has three rows, but only one in the XLSX file. How can the count and value be confirmed to be the same when comparing table data with sheets?

Is it possible to develop a script to compare the sheets with the tables once the data has been loaded?

I attempted using macros, but I was unable to do so because the sheets would need to be changed every six months. I also tried a couple of queries like groupBy.

Answer 1

One way to verify the count and value of the data is to use SQL queries to compare the data between the Oracle table and the xlsx sheet.

1. First, you need to create a new table in Oracle that has the same structure as the xlsx sheet.

2. Import the xlsx data into this new table.

3. Use a SQL query to compare the count of rows in both the tables, and to verify that the values in both the tables are the same. For example:

SELECT COUNT(*) FROM xlsx_table
UNION 
SELECT COUNT(*) FROM oracle_table; 

1. If the count is not the same, then use another query to find out which rows are missing:

SELECT * FROM xlsx_table
MINUS 
SELECT * FROM oracle_table; 

1. If the values are not the same, then use another query to find out which values are different:

SELECT xlsx_table.*, oracle_table.* FROM xlsx_table, oracle_table 
WHERE xlsx_table.roll_no = oracle_table.roll_no 
AND (xlsx_table.name != oracle_table.name 
OR xlsx_table.age != oracle_table.age 
OR xlsx_table.job != oracle_table.job)

By using these queries, you can verify the count and values of the data and make sure that the data has been loaded correctly.