June 10, 2015

esProc Handles Duplicated Records in CSV files

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:

21458952,a1234,Doe,John,technology,support staff,work phone,555-555-5555
21458952,a1234,Doe,John,technology,support staff,work email,johndoe@whatever.net
21458952,a1234,Doe,John,technology,support staff,work pager,555-555-5555
99946133,b9854,Paul,Jane,technology,administration,work phone,444-444-4444
99946133,b9854,Paul,Jane,technology,administration,work email,janepaul@whatever.net
99946133,b9854,Paul,Jane,technology,administration,work pager,444-444-4444
99946133,b9854,Paul,Jane,technology,administration,cell phone,444-444-4444

We need to filter away the duplicated records, get the first 6 columns and rearrange the 7th and the 8th column, according to the rule that work phone will be made the 7th column and work email the 8th 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.

         // establish a connection via esProc JDBC
         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