esProc Handles Duplicated Records in CSV files

Problem source：http://stackoverflow.com/questions/28976554/removing-duplicates-from-csv-and-rearranging-the-data-into-columns-with-java

JAVA doesn’t have the class library for grouping data from a text file or getting distinct values, which results rather complicated coding. In dealing with the duplicated records in a CSV file, esProc can work with JAVA to make it easier. The following example will tell you how esProc works.

dup.csv contains 8 columns, in which there are some duplicated values, as shown below: 

We need to filter away the duplicated records, get the first 6 columns and rearrange the 7^th and the 8^th column, according to the rule that work phone will be made the 7^th column and work email the 8^th column for the new file, and if there is more than one work phone or work email for the same person, the first one will be used.

esProc approach：

A1: Import the file separated by commas.

A2: Filter records to remove the duplicated ones and rearrange them. group function is used to group them. ~ represents each group of records; _1,_2…_8 are default column names; @1 indicates getting the first record from the query result. The result is as follows:

A3: Export the result to a new CSV file. Or we can use exportxls function to export data as the Excel format.

Having done all data processing, the esProc script will then be integrated with JAVA program via JDBC using the following JAVA code:

         // establish a connection via esProc JDBC

         Class.forName("com.esproc.jdbc.InternalDriver");

         con= DriverManager.getConnection("jdbc:esproc:local://");

         // call esProc script, whose name is test and which receives parameters

         st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test()");

         st.execute();//execute esProc stored procedure

         ResultSet set = st.getResultSet();//get the result