Compare Excel.csv to Oracle Database in Java

Question

I have been tasked with comparing data in an excel file (in .csv format) to my companies oracle database in Java. Once the data has been compared, I need to write if the data is the same or different to a new .csv file.

I have written the read and write methods to access the excel file.

I do not know how to actually compare columns in excel to columns in the oracle database.

Here is the stringbuilder given to me to actually select the data from the table.

//***********************************************************************
   //SQL string builder
   //***********************************************************************
    StringBuilder sSQL = new StringBuilder("");
    sSQL.append("SELECT * FROM NSA_AL.POLVEHICLE PV");
    sSQL.append(" WHERE PV.VIN='?'");
    sSQL.append(" AND PV.POLICYID='?'");
    sSQL.append(" AND PV.SEGEFFDATE<=TO_DATE('06/10/2012 08:00','MM/DD/YYYY HH24:MI')");
    sSQL.append(" AND PV.SEGEXPDATE>=TO_DATE('06/10/2012 08:00','MM/DD/YYYY HH24:MI')");
    sSQL.append(" AND PV.SEGSTATUS=");

Any help would be appreciated.

Woot4Moo · Accepted Answer · 2013-05-22 16:22:03Z

1

Create an object with a function called toCSV.

public class MyCSVMapper  
{    
    String[] fields = {"First","Second","Third"};    

    public String toCSV()  
    {  
          String formatted = "";    
          for(int i = 0; i < formatted.length;i++)
          {
             if(i == formatted.length-1)  
             {  
                formatted+=formatted[i];
                break;  
             }  
              formatted+=formatted[i]+",";
          }  
          return formatted;  
    }  
}

Take this generated CSVfile and load it into Excel, you should be able to compare these files within the context of Excel.

answered May 22, 2013 at 16:22

Woot4Moo

24.4k19 gold badges103 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jayan · Accepted Answer · 2013-05-22 16:20:18Z

0

It is better to read Oracle data using jdbc and excel data using api(say Apache POI) and do comparison. You can write/update excel file using Apache POI.

answered May 22, 2013 at 16:20

Jayan

18.6k15 gold badges92 silver badges147 bronze badges

3 Comments

Olaf Over a year ago

Apache POI for reading a CSV file is an overkill, unless the application already uses it. There are a bunch of CSV-specific libraries out there. OpenCSV and SuperCSV are the first ones that come to my mind.

bucs10us Over a year ago

@Olaf - OpenCSV looks good for opening and writing csv files. How do I actually compare the data in the csv to the orcale database?

Olaf Over a year ago

@user2355037: Pretty much as Jayan said: load data from Oracle into a list or array of objects via JDBC; load data from CSV file into the same collection of the objects of the same type; compare collections. It would help if you order both collections by the primary key. If you are talking about more than, say, a 100K of objects in each data set, you might need some additional trickery.

Collectives™ on Stack Overflow

Compare Excel.csv to Oracle Database in Java

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related