469,282 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Excel/CSV data to Oracle Tables and compare data

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.
May 2 '13 #1
12 4878
Rabbit
12,516 Expert Mod 8TB
What do you have so far?
May 2 '13 #2
@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
May 2 '13 #3
Rabbit
12,516 Expert Mod 8TB
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?
May 2 '13 #4
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
May 2 '13 #5
Rabbit
12,516 Expert Mod 8TB
You already told us what your end goal is. You still haven't told me what you've done so far.
May 2 '13 #6
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.

Expand|Select|Wrap|Line Numbers
  1. loop for rows starts
  2. loop for columns starts
  3. ...
  4. val1=select '||col1' from tableA where key_value='||_ID||'
  5. val2=select '||col1' from tableB where key_value='||_ID||'
  6.  
  7. if(val1<>val2)
  8.     Insert into tableC with ChangedCoulmn Name and TableA_
  9. Value, TableB_value.
  10. ..
  11. loop for rows ends here
  12. loop for columns ends here
Hope I made it. clear. As pl/sql code is in prod server I could not give the same.
May 2 '13 #7
Rabbit
12,516 Expert Mod 8TB
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.
May 2 '13 #8
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.
May 2 '13 #9
Rabbit
12,516 Expert Mod 8TB
That depends on which version you have.
May 2 '13 #10
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
May 3 '13 #11
Rabbit
12,516 Expert Mod 8TB
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.
May 3 '13 #12
Thank you friend I will look into this and get back to you if required .
Thanks once again for the detailed info.
May 3 '13 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Mark | last post: by
reply views Thread by =?Utf-8?B?R2Vvcmdl?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.