Compare each excel sheets with a table in oracle

0 votes

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.

Feb 5, 2023 in Others by Kithuzzz
• 38,010 points
250 views

1 answer to this question.

0 votes

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.

answered Feb 5, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,400 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,720 points
859 views
0 votes
1 answer

Convert table in a jpg image to excel using python

I believe you must execute OCR (optical ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,720 points
2,048 views
0 votes
1 answer

Excel - Make a graph that shows number of occurrences of each value in a column

There is probably a better way to ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 63,720 points
8,685 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,216 views
0 votes
1 answer
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,720 points
1,329 views
0 votes
1 answer

How do I get text in a pivot table in excel or google sheets?

Try: =QUERY(QUERY({Data!A:A, Data!Q:S}, "select Col4,max(Col1) ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,720 points
1,034 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP