SuperCSV

Java

This page is about leveraging a Java Bean called SuperCSV to get data from CSV files onto database tables. This example is for loading the 1999 US Census zip codes.

Super CSV can be found at

http://supercsv.sourceforge.net/

You can get a zip code csv file at

http://federalgovernmentzipcodes.us/

Or you can use your search engine of choice to locate a csv of whatever data that interests you for loading onto a database table. Of course, your field names and types for the bean and loader may need attention…

We need a place to put the data on the database, so we will create a table for it using SQL. The CSV file has a header line with the names of the fields. The field names are pretty sloppy, so I renamed them to speed along the process. However, I will write some code to automatically parse and appropriately rename the fields. The data itself shows us what it is and how big. A few fields are very long sloppy strings of characters which are lists of city names and they contain multiple apostrophes! You probably know apostrophes are a bane to SQL. For now, I just manually removed the apostrophes. There were more than I cared to count, but I got through it.

Here is the SQL that I settled on for defining the zip code table. -- drop table reference_zipcode_1999uscensus ; create table reference_zipcode_1999uscensus (     zip                  varchar(7),      type                 varchar(15),      primary_city         varchar(255),      acceptable_cities    text,      unacceptable_cities  text,      state                varchar(7),      county               varchar(255),      timezone             varchar(255),      area_codes           varchar(255),      latitude             numeric(15,7),      longitude            numeric(15,7),      world_region         varchar(255),      country              varchar(255),      decommissioned       varchar(255),      estimated_population varchar(255),      notes                varchar(255)  ) ;

Here is the java bean code for the zip code records. Basically, it defines a record for the loader. (Preformatting truncates the lines of code. I’ll figure out how to make the presentation easier to read for you, eventually…) /* * ZipcodeBean.java * * Copyright (c) 2013 Alfred J Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred J Wheeler * */ /** * The purpose of this program is to help add records to a database table. * * COMPILE EXAMPLE *  javac -classpath $CLASSPATH:/opt/local/share/java/super-csv-2.1.0.jar ZipcodeBean.java * * @author     8/13/2013 created by Alfred J Wheeler. * * @version    0.1 * * @see        ExtractRecords, TransformRecords * */ /* * Database : development, Table : reference_zipcode_1999uscensus * * field name                       data type * ——————————-  ——————————- * zip                               character varying(7) * type                             character varying(15) * primary_city                     character varying(255) * acceptable_cities                text * unacceptable_cities              text * state                            character varying(7) * county                           character varying(255) * timezone                         character varying(255) * area_codes                       character varying(255) * latitude                         numeric * longitude                        numeric * world_region                     character varying(255) * country                          character varying(255) * decommissioned                   character varying(255) * estimated_population             character varying(255) * notes                            character varying(255) */ public class ZipcodeBean { private String zip; private String type; private String primaryCity; private String acceptableCities; private String unAcceptableCities; private String state; private String county; private String timezone; private String areaCodes; private Double latitude; private Double longitude; private String worldRegion; private String country; private String decommissioned; private Integer estimatedPopulation; private String notes; /** * Default constructor. */ public ZipcodeBean { } /** * Constructs a ZipcodeBean * * @param zip * @param type * @param primaryCity * @param acceptableCities * @param unAcceptableCities * @param state * @param county * @param timezone * @param areaCodes * @param latitude * @param longitude * @param worldRegion * @param country * @param decommissioned * @param estimatedPopulation * @param notes */ public ZipcodeBean( final String zip, final String type, final String primaryCity, final String acceptableCities, final String unAcceptableCities, final String state, final String county, final String timezone, final String areaCodes, final Double latitude, final Double longitude, final String worldRegion, final String country, final String decommissioned, final Integer estimatedPopulation, final String notes ) { this.zip = zip; this.type = type; this.primaryCity = primaryCity; this.acceptableCities = acceptableCities; this.unAcceptableCities = unAcceptableCities; this.state = state; this.county = county; this.timezone = timezone; this.areaCodes = areaCodes; this.latitude = latitude; this.longitude = longitude; this.worldRegion = worldRegion; this.country = country; this.decommissioned = decommissioned; this.estimatedPopulation = estimatedPopulation; this.notes = notes; } /** * @return zip */ public String getZip { return zip; } /** * @param zip *           zip to set */ public void setZip(String zip) { this.zip = zip; } /** * @return type */ public String getType { return type; } /** * @param type *           type to set */ public void setType(String type) { this.type = type; } /** * @return primaryCity */ public String getPrimaryCity { return primaryCity; } /** * @param primaryCity *           primaryCity to set */ public void setPrimaryCity(String primaryCity) { this.primaryCity = primaryCity; } /** * @return acceptableCities */ public String getAcceptableCities { return acceptableCities; } /** * @param acceptableCities *           acceptableCities to set */ public void setAcceptableCities(String acceptableCities) { this.acceptableCities = acceptableCities; } /** * @return unAcceptableCities */ public String getUnAcceptableCities { return unAcceptableCities; } /** * @param unAcceptableCities *           unAcceptableCities to set */ public void setUnAcceptableCities(String unAcceptableCities) { this.unAcceptableCities = unAcceptableCities; } /** * @return state */ public String getState { return state; } /** * @param state *           state to set */ public void setState(String state) { this.state = state; } /** * @return county */ public String getCounty { return county; } /** * @param county *           county to set */ public void setCounty(String county) { this.county = county; } /** * @return timezone */ public String getTimezone { return timezone; } /** * @param timezone *           timezone to set */ public void setTimezone(String timezone) { this.timezone = timezone; } /** * @return areaCodes */ public String getAreaCodes { return areaCodes; } /** * @param areaCodes *           areaCodes to set */ public void setAreaCodes(String areaCodes) { this.areaCodes = areaCodes; } /** * @return latitude */ public Double getLatitude { return latitude; } /** * @param latitude *           latitude to set */ public void setLatitude(Double latitude) { this.latitude = latitude; } /** * @return longitude */ public Double getLongitude { return longitude; } /** * @param longitude *           longitude to set */ public void setLongitude(Double longitude) { this.longitude = longitude; } /** * @return worldRegion */ public String getWorldRegion { return worldRegion; } /** * @param worldRegion *           worldRegion to set */ public void setWorldRegion(String worldRegion) { this.worldRegion = worldRegion; } /** * @return country */ public String getCountry { return country; } /** * @param country *           country to set */ public void setCountry(String country) { this.country = country; } /** * @return decommissioned */ public String getDecommissioned { return decommissioned; } /** * @param decommissioned *           decommissioned to set */ public void setDecommissioned(String decommissioned) { this.decommissioned = decommissioned; } /** * @return estimatedPopulation */ public Integer getEstimatedPopulation { return estimatedPopulation; } /** * @param estimatedPopulation *           estimatedPopulation to set */ public void setEstimatedPopulation(Integer estimatedPopulation) { this.estimatedPopulation = estimatedPopulation; } /** * @return notes */ public String getNotes { return notes; } /** * @param notes *           notes to set */ public void setNotes(String notes) { this.notes = notes; } @Override public int hashCode { final int prime = 367; int result = 1; result = prime * result + ((zip == null) ? 0 : zip.hashCode); result = prime * result + ((type == null) ? 0 : type.hashCode); result = prime * result + ((primaryCity == null) ? 0 : primaryCity.hashCode); result = prime * result + ((acceptableCities == null) ? 0 : acceptableCities.hashCode); result = prime * result + ((unAcceptableCities == null) ? 0 : unAcceptableCities.hashCode); result = prime * result + ((state == null) ? 0 : state.hashCode); result = prime * result + ((county == null) ? 0 : county.hashCode); result = prime * result + ((timezone == null) ? 0 : timezone.hashCode); result = prime * result + ((areaCodes == null) ? 0 : areaCodes.hashCode); result = prime * result + ((latitude == null) ? 0 : latitude.hashCode); result = prime * result + ((longitude == null) ? 0 : longitude.hashCode); result = prime * result + ((worldRegion == null) ? 0 : worldRegion.hashCode); result = prime * result + ((country == null) ? 0 : country.hashCode); result = prime * result + ((decommissioned == null) ? 0 : decommissioned.hashCode); result = prime * result + ((estimatedPopulation == null) ? 0 : estimatedPopulation.hashCode); result = prime * result + ((notes == null) ? 0 : notes.hashCode); return result; } @Override public boolean equals( Object obj ) { if ( this == obj ) { return true; } if ( obj == null ) { return false; } if ( !(obj instanceof ZipcodeBean) ) { return false; } ZipcodeBean other = (ZipcodeBean) obj; if ( zip == null ) { if ( other.zip != null ) { return false; } } else if ( !zip.equals(other.zip) ) { return false; } return true; } @Override public String toString { return String.format( “ZipcodeBean [zip=%s, type=%s, primaryCity=%s, acceptableCities=%s, unAcceptableCities=%s, state=%s, county=%s, timezone=%s, areaCodes=%s, latitude=%s, longitude=%s, worldRegion=%s, country=%s, decommissioned=%s, estimatedPopulation=%s, notes=%s]“, zip, type, primaryCity, acceptableCities, unAcceptableCities, state, county, timezone, areaCodes, latitude, longitude, worldRegion, country, decommissioned, estimatedPopulation, notes ); } } // end ZipcodeBean

And here is the loader class. The loader class is the work horse. This one just reads each record and puts it on the table. /* * LoadZipCodeRecordsFromFile.jsp * * Copyright (c) 2013 Alfred J Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred J Wheeler * */ /**  * The purpose of this program is to add records to a database table. Requires * SuperCSV bean. Just place the bean in some sensible place and add the * path of the jar to the classpath argument at compile and run times. SuperCSV * can be found for download at http://supercsv.sourceforge.net/ * * COMPILE EXAMPLE *  javac -classpath $CLASSPATH:/opt/local/share/java/super-csv-2.1.0.jar:. LoadZipCodeRecordsFromFile.java * * USAGE EXAMPLE *  java -classpath $CLASSPATH:/opt/local/share/java/super-csv-2.1.0.jar:. LoadZipCodeRecordsFromFile -input /path/zipcodes_filename.csv * * @author      8/13/2013 created by Alfred J Wheeler. * * @version     0.1 * * @see         ExtractRecords, TransformRecords * */ /*  * Problems:  (acceptableCities and unAcceptableCities lists too long), (apostrophe embedded in city names) * * Note: Search for and use an "sqlEncode" type of tool to escape the apostrophes (or write it...) */ /* * Database : development, Table : reference_zipcode_1999uscensus *  * field name                        data type * ---  ---  * zip                               character varying(7) * type                             character varying(15) * primary_city                     character varying(255) * acceptable_cities                text * unacceptable_cities              text * state                            character varying(7) * county                           character varying(255) * timezone                         character varying(255) * area_codes                       character varying(255) * latitude                         numeric * longitude                        numeric * world_region                     character varying(255) * country                          character varying(255) * decommissioned                   character varying(255) * estimated_population             character varying(255) * notes                            character varying(255) */ import java.io.*; import java.io.BufferedReader; import java.sql.*; import java.util.*; import org.supercsv.cellprocessor.*; import org.supercsv.cellprocessor.constraint.*; import org.supercsv.cellprocessor.ift.CellProcessor; import org.supercsv.io.CsvBeanReader; import org.supercsv.io.CsvBeanReader.*; import org.supercsv.io.ICsvBeanReader; import org.supercsv.io.ICsvBeanReader.*; import org.supercsv.prefs.CsvPreference; public class LoadZipCodeRecordsFromFile { /* 	 * Initialize some variables */ 	public static String className = "LoadZipCodeRecordsFromFile"; //	public static boolean argHelp        = false; public static boolean argInFile      = false; public static void main(String args[]) { String usage = "Usage: java [-cp ] " + className + " <-i|input input file name>"; int errorCount = 0; String msg = "";              // user feedback message long dt = 0;                  // date-time stamp String url;                   // database url Connection conn = null;       // database handle DatabaseMetaData dbmd = null; // database meta data handle Statement st = null;          // statement handle //		ResultSet rs = null;          // result set handle ResultSetMetaData rsmd = null; // result set meta data handle String sql = "";              // sql statement String inName = "";           // File path name File inFile = null; BufferedReader in = null; String lineIn = ""; long linesRead = 0; String foundData = "false"; int i = 0;                    // utility variable /* 		 * Get a date time stamp. */ 		dt = System.currentTimeMillis; //		System.out.println; //		for (i=0;i ( i + 1 ) ) inName = args[i+1]; System.out.println; System.out.println( "inName == " + inName ); } 			} else { if ( args[i].equals ( "-i" ) || args[i].equals ( "-input" ) ) { msg = "** " + className + ": ERROR - Duplicate input option specified."; System.out.println; System.out.println( msg ); errorCount++; } 			} 		} 		/* 		 * Exit point */ 		if ( errorCount > 0 ) { System.exit(1); } 		/* 		 * There is no point in going on, if it is a help request. */ 		if ( argHelp ) { System.out.println; System.out.println( usage ); System.exit(1); } 		/* 		 * Final check for presence of required value(s) */ 		if ( !argInFile ) { msg = "** " + className + ": ERROR - Required value, input file path-name not present."; System.out.println; System.out.println( msg ); System.out.println; System.out.println( usage ); errorCount++; } 		/* 		 * Exit point */ 		if ( errorCount > 0 ) { System.exit(1); } 		/* 		 * Get the database connection values, etc... from the properties file * ( This eventually will be externalized to reduce code clutter... ) */ 		String p = null; InputStream propsFile; Properties appProp = new Properties; try { p = ( new File(".") ).getCanonicalPath + "/" + "properties/Demo.properties"; propsFile = new FileInputStream(p); appProp.load(propsFile); propsFile.close; } 		catch (IOException ioe) { System.out.println; System.out.println("I/O Exception."); ioe.printStackTrace; System.out.println; } 		String dbDriver = "" + appProp.getProperty("dbDriver"); String dbUrl   = "" + appProp.getProperty("dbUrl"); String dbUid   = "" + appProp.getProperty("dbUid"); String dbUpw   = "" + appProp.getProperty("dbUpw"); String css     = "" + appProp.getProperty("css"); //System.out.println; //System.out.println(dbDriver); //System.out.println(dbUrl); //System.out.println(dbUid); //System.out.println(dbUpw); //System.out.println; /* 		 * Open the database */ 		try { Class.forName(dbDriver); conn = DriverManager.getConnection(dbUrl, dbUid, dbUpw); dbmd = conn.getMetaData; st = conn.createStatement; ICsvBeanReader beanReader = null; try { beanReader = new CsvBeanReader(new FileReader( inName ), CsvPreference.STANDARD_PREFERENCE); // the header elements are used to map the values to the bean (names must match) final String[] header = beanReader.getHeader(true); final CellProcessor[] processors = getProcessors; ZipcodeBean zipcodeRecord; while( (zipcodeRecord = beanReader.read(ZipcodeBean.class, header, processors)) != null ) { sql = "INSERT INTO reference_zipcode_1999uscensus " + " VALUES ( " + 							"'" + zipcodeRecord.getZip + "', " + 							"'" + zipcodeRecord.getType + "', " + 							"'" + zipcodeRecord.getPrimaryCity + "', " + 							"'" + zipcodeRecord.getAcceptableCities + "', " + 							"'" + zipcodeRecord.getUnAcceptableCities + "', " + 							"'" + zipcodeRecord.getState + "', " + 							"'" + zipcodeRecord.getCounty + "', " + 							"'" + zipcodeRecord.getTimezone + "', " + 							"'" + zipcodeRecord.getAreaCodes + "', " + 							"" + zipcodeRecord.getLatitude + ", " + 							"" + zipcodeRecord.getLongitude + ", " + 							"'" + zipcodeRecord.getWorldRegion + "', " + 							"'" + zipcodeRecord.getCountry + "', " + 							"'" + zipcodeRecord.getDecommissioned + "', " + 							"" + zipcodeRecord.getEstimatedPopulation + ", " + 							"'" + zipcodeRecord.getNotes + "' " + 						")"; System.out.println( String.format( "%5s. %s", beanReader.getRowNumber, sql ) ); st.executeUpdate(sql); } 			} 			finally { if( beanReader != null ) { beanReader.close; } 			} 			st.close; conn.close; } // end try catch ( SQLException ex ) { System.out.println ; System.out.println ( "---" ); System.out.println ( "-- FATAL ERROR IN SQL" ); System.out.println ( "---" ); System.out.println ( "-- " ); while ( ex != null ) { System.out.println ("-- SQLState: " + ex.getSQLState ); System.out.println ("-- Message: " + ex.getMessage ); System.out.println ("-- Vendor:  " + ex.getErrorCode ); ex = ex.getNextException ; System.out.println ( "-- " ); } 			System.out.println ( "---" ); } // end catch catch ( java.lang.Exception ex ) { System.out.println ; System.out.println ( "---" ); ex.printStackTrace ; System.out.println("exception: " + ex.getMessage ); } // end catch finally { // No action for now } // end finally System.exit(0); } // main /* 	 * Sets up the processors used for the examples. There are 16 CSV columns, so 16 processors are defined. Empty * columns are read as null (hence the NotNull for mandatory columns). * 	 * @return the cell processors */ 	 private static CellProcessor[] getProcessors { final CellProcessor[] processors = new CellProcessor[] { new NotNull, // zip new Optional, // type new NotNull, // primary_city new Optional, // acceptable_cities new Optional, // unacceptable_cities new NotNull, // state new Optional, // county new Optional, // timezone new Optional, // area_codes new Optional(new ParseDouble), // latitude new Optional(new ParseDouble), // longitude new Optional, // world_region new Optional, // country new Optional, // decommissioned new Optional(new ParseInt), // estimated_population new Optional // notes }; 		return processors; } // getProcessors } // class

To put the application to work, you can use a terminal window. You need to compile both the bean and the loader classes, and then you can run the loader class, e.g.

javac -classpath $CLASSPATH:/opt/local/share/java/super-csv-2.1.0.jar ZipcodeBean.java

javac -classpath $CLASSPATH:/opt/local/share/java/super-csv-2.1.0.jar:. LoadZipCodeRecordsFromFile.java

java -classpath $CLASSPATH:/opt/local/share/java/super-csv-2.1.0.jar:. LoadZipCodeRecordsFromFile -input /path/zipcodes_filename.csv

That’s it for this example. HTH