opencsv - Aggregate CSV data with group by in Java -
i need aggregate csv data group by in java.
my csv file looks this:
numero, numerowsn, noeudadress, packetrece, noeudsrece, hello 1436136640477044,wsn430-8,na:b27b,packet recevied from,rx: b0b4, hello #33 1436136640477257,wsn430-8,na:b27b,packet recevied from,rx: b986, hello #33 1436136640477415,wsn430-8,na:b27b,packet recevied from,rx: bc2d, hello #33 1436136640477566,wsn430-8,na:b27b,packet recevied from,rx: b36b, hello #34 1436136640477716,wsn430-8,na:b27b,packet recevied from,rx: bcb6, hello #35 1436136640477995,wsn430-9,na:bc2d,packet recevied from,rx: 1f9e, hello #33 1436136640478162,wsn430-9,na:bc2d,packet recevied from,rx: be29, hello #33 1436136640478313,wsn430-9,na:bc2d,packet recevied from,rx: b61a, hello #32 1436136640478462,wsn430-9,na:bc2d,packet recevied from,rx: c735, hello #32 1436136640478612,wsn430-9,na:bc2d,packet recevied from,rx: bb0a, hello #32 1436136640478760,wsn430-9,na:bc2d,packet recevied from,rx: b6bc, hello #33 1436136640477044,wsn430-8,na:b27b,packet recevied from,rx: b0b1, hello #42 1436136640477257,wsn430-8,na:b27b,packet recevied from,rx: b984, hello #44 is there way aggregate these data grouping noeudadress , show noeudsrece counters column shown below using java?
noeudsadresse,numberofnoeudsrece b27b ,7 bc2d ,6 i thought of loading csv file list using opencsv, efficient csv file millions of rows?
use h2 instead of opencsv.
strip out header line , put in file called data.csv
1436136640477044,wsn430-8,na:b27b,packet recevied from,rx: b0b4, hello #33 1436136640477257,wsn430-8,na:b27b,packet recevied from,rx: b986, hello #33 1436136640477415,wsn430-8,na:b27b,packet recevied from,rx: bc2d, hello #33 1436136640477566,wsn430-8,na:b27b,packet recevied from,rx: b36b, hello #34 1436136640477716,wsn430-8,na:b27b,packet recevied from,rx: bcb6, hello #35 1436136640477995,wsn430-9,na:bc2d,packet recevied from,rx: 1f9e, hello #33 1436136640478162,wsn430-9,na:bc2d,packet recevied from,rx: be29, hello #33 1436136640478313,wsn430-9,na:bc2d,packet recevied from,rx: b61a, hello #32 1436136640478462,wsn430-9,na:bc2d,packet recevied from,rx: c735, hello #32 1436136640478612,wsn430-9,na:bc2d,packet recevied from,rx: bb0a, hello #32 1436136640478760,wsn430-9,na:bc2d,packet recevied from,rx: b6bc, hello #33 1436136640477044,wsn430-8,na:b27b,packet recevied from,rx: b0b1, hello #42 1436136640477257,wsn430-8,na:b27b,packet recevied from,rx: b984, hello #44 go download h2 jar file here: http://www.h2database.com/html/download.html
and run code ...
import java.io.file; import java.net.urisyntaxexception; import java.net.url; import java.sql.connection; import java.sql.drivermanager; import java.sql.resultset; import java.sql.sqlexception; import java.sql.statement; public class csvloader { public static final string getapplicationpath(class<?> mainclass) throws urisyntaxexception { return getapplicationdirectory(mainclass).getabsolutepath(); } public csvloader() { executestatement(getdroptablestatement(), false); executestatement(getcreatetablestatement(), false); executestatement(getinsertstatement(), false); executestatement(getselectstatement(), true); } public static final string getdroptablestatement() { string sqlstring = "drop table data if exists;\n"; return sqlstring; } public static final string getselectstatement() { string sqlstring = "select noeudadress, count(noeudsrece) data group noeudadress;\n"; return sqlstring; } public static final string getcreatetablestatement() { string sqlstring = "create table data(\n"; sqlstring += " numero varchar(100),\n"; sqlstring += " numerowsn varchar(100),\n"; sqlstring += " noeudadress varchar(100),\n"; sqlstring += " packetrece varchar(100),\n"; sqlstring += " noeudsrece varchar(100),\n"; sqlstring += " hello varchar(100))"; return sqlstring; } public static final string getinsertstatement() { return "insert data select * csvread('data.csv')"; } public void executestatement(string sql, boolean withresultset) { connection connection = null; statement statement = null; resultset resultset = null; try { file file = getapplicationdirectory(csvloader.class); class.forname("org.h2.driver"); connection = drivermanager.getconnection("jdbc:h2:" + file.getabsolutepath() + file.separator + "storage", "sa", "secret"); statement = connection.createstatement(); if(withresultset) { resultset = statement.executequery(sql); while(resultset.next()) { system.out.println("-->" + resultset.getstring(1) + "\t" + resultset.getstring(2)); } } else { statement.execute(sql); } } catch (urisyntaxexception e) { e.printstacktrace(); } catch (classnotfoundexception e) { e.printstacktrace(); } catch (sqlexception e) { e.printstacktrace(); } { try { if(resultset != null) { resultset.close(); } if(statement != null) { statement.close(); } if(connection != null) { connection.close(); } } catch (sqlexception e) { e.printstacktrace(); statement = null; connection = null; } } } private static final file getapplicationdirectory(class<?> mainclass) throws urisyntaxexception { url url = mainclass.getprotectiondomain().getcodesource().getlocation(); file file = new file(url.touri()); return file.getparentfile(); } public static void main(string[] args) { new csvloader(); } } output is
-->na:b27b 7 -->na:bc2d 6 you can run console: example above works when enter in console:
create table test( numero varchar(100), numerowsn varchar(100), noeudadress varchar(100), packetrece varchar(100), noeudsrece varchar(100), hello varchar(100) ) select * csvread('c:\\eclipse\\workspace\\h2\\data.csv') 
make sure use full path data file
Comments
Post a Comment