Excel/CSV data to Oracle Tables and compare data

[vijaya hyd]

I have two excel files(say XL1, XL2) with same structure.
1) Part of XL1 data may exist in XL2 with few column values changed(modified field values)
2) Few records of XL1 may be delted in XL2(deleted)
3) New records may be added in XL2(newly added)

Task
-----
I need to load two excel into two different tables in the same (Oracle) database and compare the same and need to give output as
a)Modified records(Primary_Key_Field_Value, XL1 value(old value), XL2 value(New value)
b)New(Inserted) records which are present in XL@ but not in XL1
c)Old(Deleted) records which are present in XL! but not in XL2.

[Rabbit]

What do you have so far?

[vijaya hyd]

@Rabbit
I did not get your quextion.
Let me know what things need to make it more clear to you to understand the problem. should I give sample data, which is of 50+ columns and above 300,000 records

[Rabbit]

I mean what have you done so far towards solving your problem. What code do you have? What steps have you taken? Have you at least imported the data from Excel to the database?

[vijaya hyd]

I could able to save excels into two tables.
I need to compare col1 of TableA to col1 of TableB, similarly 30+ columns I have to compare and if any change found, I need to Insert those changes in TableC
TableC structre is like
ID ColumnName TableA_Value TableB_Value
1 Emp_Dept Accounts Admin
1 Address XYZ ABC
2 Sal 10000 12000

[Rabbit]

You already told us what your end goal is. You still haven't told me what you've done so far.

[vijaya hyd]

Sincere apologies for not giving you clear picture of what I have done.
Right now I am taking the inner join of the two tables and looping each column and checking each column value in both the tables and comparing the values, which is taking lot of time.

loop for rows starts
loop for columns starts
...
val1=select '||col1' from tableA where key_value='||_ID||'
val2=select '||col1' from tableB where key_value='||_ID||'
 
if(val1<>val2)
    Insert into tableC with ChangedCoulmn Name and TableA_
Value, TableB_value.
..
loop for rows ends here
loop for columns ends here

Hope I made it. clear. As pl/sql code is in prod server I could not give the same.

[Rabbit]

Please use code tags when posting code.

It sounds like you're only talking about #1. That is a bit more involved than #2 and #3. Those are a lot simpler so let me get that out of the way quickly. #2 and #3 can be accomplished using outer joins and looking for the nulls.

As for #1, if you're using Oracle 11g and above, you'll want to unpivot your 30+ columns. If you're using a prior version, you can use a series of union all queries to achieve the same effect.

Once you've unpivoted each table, you can join the two on the id and column name and compare the two values.

[vijaya hyd]

Thank you very much for the quick response. As you have mentioned , #2 and #3 I have done using joins and I will try #1 with your suggestion and if possible can you provide a sample.

[Rabbit]

That depends on which version you have.

[vijaya hyd]

It is oracle 11g R2.
I feel happy if you can provide a sample as I am a newbie to oracle pl/sql

Thanks in advance

[Rabbit]

Here is oracle's documentation on the pivot and unpivot syntax: http://www.oracle-base.com/articles/...tors-11gr1.php.

You will want to use the examples in there to unpivot your two tables. That's the first step.

[vijaya hyd]

Thank you friend I will look into this and get back to you if required .
Thanks once again for the detailed info.