472,352 Members | 1,626 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,352 software developers and data experts.

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)

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 5118
12,516 Expert Mod 8TB
What do you have so far?
May 2 '13 #2
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
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
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||'
  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
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
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
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

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

Similar topics

by: narasingarao | last post by:
Hi to group, I'm a student of M.C.A. from B.I.T. Ranchi...I'm in my project period here i have to migrate the MS-Access database table to Oracle...
by: Hi5 | last post by:
Hi, I am new to access I usedto work in Oracle and Mysql. I am after a way that enables me to populate a database I designed in access with...
by: Mark | last post by:
by m.r.davies I have 2 tables on seperate Db's (and servers) I want to use a datareader on the first table to pick the booking ref, and then use...
by: Mark D Powell | last post by:
We are running SQL Server 2000 SP3. We have linked servers in use that we use to access Oracle tables. Recently the claim has been made that you...
by: khushbubhalla | last post by:
how to compare data between tables and views row by row , column by column in the test and production environment
by: =?Utf-8?B?R2Vvcmdl?= | last post by:
Hello everyone, I am learning how to use Excel to connect to other external data source (like database). What I want to do is to develop a...
by: =?Utf-8?B?UGV0ZXI=?= | last post by:
I'm confused about Data Access Layer and Data Object Layer. How are they related? Which layer will be affected when the underlying database...
by: Poonam G | last post by:
I need to call oracle meta data tables using MS DOS, and then proceed with the execution of the MS DOS batch files as per the out put from oracle...
by: manijee | last post by:
I am trying to link oracle table in MS Access. Under ODBC, I have defined the system DSN. Through access, when I try to link to a table (File, Get...
by: mc223 | last post by:
I have an excel sheet which has connection to Oracle table.I want VBA code which will enable the chaNges the user made on the excel sheet to refelct...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.