I have a problem, I'm trying to access to a column from a CSV archive in JAVA. I programmed a function thats return all data from the csv archive. But I want to return 1 column depending of a condition. The condition is, the column region.
If the region number is 1, the result is, for example in SQL.
SELECT region, sum(nviv) AS 'HOUSES' FROM census WHERE region = 1
The clausure returns 1 value for region 1, a sum of all columns nviv. I trying to do same that but in JAVA and OpenCSV.
This is my code, I'm new in java programming whit CSV archives.
package hogares;
import java.util.*;
import com.opencsv.*;
import java.io.*;
public class Hogares {
public static final String SEPARADOR = ";";
public static void main(String[] args){
int opc;
Scanner sc = new Scanner(System.in);
System.out.println("MENU HOMES");
System.out.println("");
System.out.println("1. Count Regions by region number");
System.out.println("2. Average of homes of blocks by region number");
System.out.println("3. Blocks number by region number");
System.out.println("4. Exit");
System.out.println("");
System.out.println("Select an option: ");
opc = sc.nextInt();
switch(opc){
case 1:
CountHome();
break;
case 2:
AverageHome();
break;
case 3:
BlocksNumber();
break;
case 4:
System.out.println("EXITING...");
System.out.println("");
break;
default:
System.out.println("Invalid option...");
break;
}
}
private static void CountHome(){
int region;
Scanner lee = new Scanner(System.in);
BufferedReader csvr = null;
try {
csvr = new BufferedReader(new FileReader("C:\\Users\\ADMIN\\Desktop\\census2010.csv"));
String[] headers = new String[]{"REGION","PROVINCE","COMMUNE","DC","AREA","ZC_LOC","ID_ZONE_LOC","NVIV","NHOME","TIPE_HOME","TIPE_OPERATIVE"};
System.out.println("Tell me a number: ");
region = lee.nextInt();
String line = csvr.readLine();
while (null!=line) {
headers = line.split(SEPARADOR);
System.out.println(Arrays.toString(headers));
System.out.println(Arrays.toString(headers));
line = csvr.readLine();
}
}
catch (IOException e) {
e.printStackTrace();
}
finally{
if (csvr != null) {
try {
csvr.close();
}
catch (IOException e) {
e.printStackTrace();
}
}
}
}
private static void AverageHome() {
}
private static void BlocksNumber() {
}
}
This is the output. Remember I'm trying to return 1 result depending de number from this line:
System.out.println("Tell me a number: ");
region = lee.nextInt();
Code comment added for explanation
private static void CountHome() {
int requestedRegion;
Scanner scanner = new Scanner(System.in);
System.out.println("Tell me a number: ");
requestedRegion = scanner.nextInt();
try {
FileReader filereader = new FileReader("C:\\\\Users\\\\ADMIN\\\\Desktop\\\\census2010.csv");
// Use OpenCsv to read CSV file
CSVReader csvReader = new CSVReader(filereader);
System.out.println("REGION\tHOUSES");
//Assign it to skip header row from records
String[] nextLine = csvReader.readNext();
//Take variable to for sum of nviv
int houses = 0;
// Read CSV record one by one
while ((nextLine = csvReader.readNext()) != null) {
// Get first column value which is region
String region = nextLine[0];
// Get nviv column value
String nviv = nextLine[7];
// Check region and nviv is not null and empty
if (region != null && !region.isEmpty() && nviv != null && !region.isEmpty()) {
// Parse region string to integer
int currentRegion = Integer.parseInt(region);
// Check row region is equals to region entered by user
if (currentRegion == requestedRegion) {
// Parse nviv sting to integer
int currentNviv = Integer.parseInt(nviv);
// Sum nviv And get total houses
houses = houses + currentNviv;
// System.out.println(region + "\t" + nviv);
}
} else {
System.out.println("Invalid data to proceed");
}
}
System.out.println(requestedRegion + "\t" + houses);
} catch (Exception e) {
e.printStackTrace();
}
}
Sample CSV Data:
REGION,PROVINCE,COMMUNE,DC,AREA,ZC_LOC,ID_ZONE_LOC,NVIV,NHOME,TIPE_HOME,TIPE_OPERATIVE
1,2,33,4,5,66,7,8,9,10,11
2,2,33,4,5,66,7,8,9,10,11
1,2,33,4,5,66,7,8,9,10,11
2,2,33,4,5,66,7,8,9,10,11
1,2,33,4,5,66,7,8,9,10,11
1,2,33,4,5,66,7,8,9,10,11
7,2,33,4,5,66,7,8,9,10,11
7,2,33,4,5,66,7,8,9,10,11
7,2,33,4,5,66,7,8,9,10,11
7,2,33,4,5,66,7,8,9,10,11
7,2,33,4,5,66,7,8,9,10,11
8,2,33,4,5,66,7,8,9,10,11
8,2,33,4,5,66,7,8,9,10,11
8,2,33,4,5,66,7,8,9,10,11
8,2,33,4,5,66,7,8,9,10,11
8,2,33,4,5,66,7,8,9,10,11
Output:
MENU HOMES
1. Count Regions by region number
2. Average of homes of blocks by region number
3. Blocks number by region number
4. Exit
Select an option:
1
Tell me a number:
7
REGION HOUSES
7 40
Can Result with error if CSV have empty row,
To check if any empty row exsist and skip if found
// Read CSV record one by one
while ((nextLine = csvReader.readNext()) != null) {
// Check row empty, if found skip iteration
if (nextLine.length != 0 && nextLine[0].trim().isEmpty()) {
continue;
}