Search code examples
javaopencsv

Problem whit trying to access to a column Java CSV


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();

enter image description here


Solution

  • 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;
                    }