Question

Source: https://stackoverflow.com/questions/68777361/java-parsing-a-2nd-csv-file-to-override-a-value

I have two csv files. One shows all crime data including City, State, Population etc. The other shows State and Abbreviation. I want to have the state set as the abbreviation, currently I have some very long code and I'm thinking there is definitely a better way at setting it based on the abbreviation csv file.

My main class:

public class StartApp {

public static ArrayList<CityCrime> crimes = new ArrayList<CityCrime>();

public static ArrayList<String> cities = new ArrayList<String>();

/**

* Start point for app. Directs the reads from file and shows the menu

* @param args

*/

public static void main(String[] args) {

try {

readCrimeData("crimeUSA.csv");

System.out.println("Total cities read:" + getTotalCities());

showMenu();

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* Reads the crime data for each city from entered file

* Adds the CityCrime objects to the crimes ArrayList

*/

public static void readCrimeData(String fromFile) {

File file = new File(fromFile);

FileReader fileReader;

BufferedReader bufferedReader;

String crimeInfo;

String[] stats;

try {

fileReader = new FileReader(file);

bufferedReader = new BufferedReader(fileReader);

crimeInfo = bufferedReader.readLine();

crimeInfo = bufferedReader.readLine();

do {

CityCrime crime = new CityCrime(); // Default constructor

stats = crimeInfo.split(",");

{

if (stats[0] != null) {

crime.setCity(stats[0]);

}

if (stats[1] != null) {

crime.setState(stats[1]);

}

if (stats[2] != null) {

if (Integer.parseInt(stats[2]) >= 0) {

crime.setPopulation(Integer.parseInt(stats[2]));

}

}

if (stats[3] != null) {

if (Integer.parseInt(stats[3]) >= 0) {

crime.setMurder(Integer.parseInt(stats[3]));

}

}

if (stats[4] != null) {

if (Integer.parseInt(stats[4]) >= 0) {

crime.setRobbery(Integer.parseInt(stats[4]));

}

}

if (stats[5] != null) {

if (Integer.parseInt(stats[5]) >= 0) {

crime.setAssault(Integer.parseInt(stats[5]));

}

}

if (stats[6] != null) {

if (Integer.parseInt(stats[6]) >= 0) {

crime.setBurglary(Integer.parseInt(stats[6]));

}

}

if (stats[7] != null) {

if (Integer.parseInt(stats[7]) >= 0) {

crime.setLarceny(Integer.parseInt(stats[7]));

}

}

if (stats[8] != null) {

if (Integer.parseInt(stats[8]) >= 0) {

crime.setMotorTheft(Integer.parseInt(stats[8]));

}

}

crime.setTotalCrimes(Integer.parseInt(stats[3]), Integer.parseInt(stats[4]), Integer.parseInt(stats[5]), Integer.parseInt(stats[6]), Integer.parseInt(stats[7]), Integer.parseInt(stats[8]));

}

crimes.add(crime);

System.out.println(crime);

crimeInfo = bufferedReader.readLine();

} while (crimeInfo != null);

fileReader.close();

bufferedReader.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

} catch (NumberFormatException e) {

e.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* For each crime, add the city to the cities ArrayList and return the count

*/

public static int getTotalCities() {

for(CityCrime crime : crimes) {

cities.add(crime.getCity());

}

int cityCount = cities.size();

return cityCount;

}

/**

* Displays the menu

* User can select which function they want to run

* @throws IOException

*/

@SuppressWarnings("unlikely-arg-type")

public static void showMenu() throws IOException {

@SuppressWarnings("resource")

Scanner menuSelect = new java.util.Scanner(System.in);

System.out.println("1. Display all crime stats by city");

System.out.println("2. Display all crime stats by selected city");

System.out.println("3. Display the murder stats by selected state");

System.out.println("4. Display highest crime city - all crimes");

System.out.println("5. Display each state (in alphabetical order with the number of car thefts");

System.out.println("6. Write / export all cities in descending order of Robbery rate");

System.out.println("7. Quit");

System.out.println("Enter option 1-7");

@SuppressWarnings("resource")

Scanner scanner = new Scanner(System.in);

int option = Integer.parseInt(menuSelect.next());

if(option<1 || option>7) {

System.out.println("Invalid input.");

return;

}

switch (option) {

case 1:

displayAllCityCrimeStats();

break;

case 2:

System.out.println("Enter city");

String cityOption = menuSelect.next();

displayCrimeStatsByCity(cityOption);

break;

case 3:

System.out.println("Enter state");

String stateOption = menuSelect.next();

displayMurdersByState(stateOption);

break;

case 4:

displayHighestCrimeStats();

break;

case 5:

displayStateCarThefts();

break;

case 6:

writeToFile("Robbery.csv");

break;

case 7:

return;

default:

option = Integer.parseInt(scanner.next());

}

}

My CityCrime file. It is a mess right now as I don't know what the right direction to go in is. I have cut down the setStates, there are 52 in reality so it's pretty long:

public class CityCrime {

//Instance variables

private String city;

private String state;

private int population;

private int murder;

private int robbery;

private int assault;

private int burglary;

private int larceny;

private int motorTheft;

public int totalCrimes;

public static ArrayList<CityState> abbreviations = new ArrayList<CityState>();

public String fromFile = ("C:/Users/ebeck/Downloads/StateAbbreviations.csv");

public static void main(String[] args) {

}

public static void readAbbrevData(String fromFile) {

File file = new File(fromFile);

FileReader fileReader;

BufferedReader bufferedReader;

String abbrevInfo;

String[] stats;

try {

fileReader = new FileReader(file);

bufferedReader = new BufferedReader(fileReader);

abbrevInfo = bufferedReader.readLine();

abbrevInfo = bufferedReader.readLine();

do {

CityState abbrev = new CityState(); // Default constructor

stats = abbrevInfo.split(",");

{

if (stats[0] != null) {

abbrev.setState(stats[0]);

}

if (stats[1] != null) {

abbrev.setAbbreviation(stats[1]);

}

}

abbreviations.add(abbrev);

System.out.println(abbrev);

abbrevInfo = bufferedReader.readLine();

} while (abbrevInfo != null);

fileReader.close();

bufferedReader.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

} catch (NumberFormatException e) {

e.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

}

}

public String getCity() {

return city;

}

public void setCity(String city) {

this.city = city;

}

public String getState() {

return state;

}

public void setState(String state) {

if(state.equalsIgnoreCase("ALABAMA")) {

this.state = "AL";

}

else if(state.equalsIgnoreCase("ALASKA")) {

this.state = "AK";

}

else if(state.equalsIgnoreCase("ARIZONA")) {

this.state = "AR";

}

else if(state.equalsIgnoreCase("ARKANSAS")) {

this.state = "AR";

}

else if(state.equalsIgnoreCase("CALIFORNIA")) {

this.state = "CA";

}

else if(state.equalsIgnoreCase("COLORADO")) {

this.state = "CO";

}

else if(state.equalsIgnoreCase("CONNECTICUT")) {

this.state = "CT";

}

//etc

}

public int getPopulation() {

return population;

}

public void setPopulation(int population) {

this.population = population;

}

public int getMurder() {

return murder;

}

//etc

}

I created a CityState file, however may be unnecessary: public class CityState {

private String state;

private String abbreviation;

public static void main(String[] args) {

}

public String getState() {

return state;

}

public String getAbbreviation() {

return abbreviation;

}

public void setAbbreviation(String abbreviation) {

this.abbreviation = abbreviation;

}

public void setState(String state) {

this.state = state;

}

}

A couple reasons I want to change how I've set up the get state is 1. for my Junit test for state:

@Test

public void testValidState() {

CityCrime crimes = new CityCrime();

crimes.setState(state);

assertEquals(crimes.getState(), state);

}

I was getting the error:

expected: <A[K] but was: <A[LASKA]> if I set the state to "Alaska".

Then if I set it to "AK" I got the error:

expected: but was:

and 2. It doesn't look great either and I want to learn a better way.

Thank you for your help in advance.

Edit: For each crime, if the state in CityCrimes csv file is equal to the state in the StatesAbbreviations file, then set the state as the abbreviation in the StatesAbbreviations file and return.

Answer

You want to replace certain values in the first CSV file with corresponding values in the second CSV file. The code will be extremely long if you use Java to perform the association.

But it is easy to do the coding job using Java’s open-source package SPL. One line of code is enough:

	A
1	=file("crimeUSA.csv").import@ct().switch(State,file("StateAbbreviations.csv").import@ct():State).new(City,State.Abbreviation:   StateAbbreviation,Population,Murder,Robbery,Assault,Burglary,Larceny,MotorTheft,TotalCrimes)

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as abbr.splx and invoke it in Java as you call a stored procedure:

…

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

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

st=con.prepareCall("call abbr()");

st.execute();

…

Or execute the SPL string within a Java program as we execute a SQL statement:

…

st = con.prepareStatement("==file(\"crimeUSA.csv\").import@ct().switch(State,file(\"StateAbbreviations.csv\").import@ct():State).new(City,State.Abbreviation,Population,Murder,Robbery,Assault,Burglary,Larceny,MotorTheft,TotalCrimes)");

st.execute();

…

View SPL source code.