Selecting the data from a csv file both row and column wise in java

Question

Consider a csv file(emp.csv).My idea is to output the data which is the intersection of rows and columns .I have provided checkbox for selecting rows. I am not getting a how to select columns and then output data from the selected rows and columns.Fields of emp.csv [id,first_name,last_name,email,gender]

//CSVReaderUtility

package com.query.generator;

import java.util.List;
import java.io.File;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.Scanner;

public class CSVReaderUtility {

    private static final char DEFAULT_SEPARATOR = ',';
    private static final char DEFAULT_QUOTE = '"';

    public List<Employee> getEmps() throws FileNotFoundException {
        List<Employee> emps = new ArrayList<>();
        String csvFile = "emp.csv";
        Scanner scanner = new Scanner(new File(csvFile));
        while (scanner.hasNext()) {
            List<String> line = parseLine(scanner.nextLine());
            Employee emp = new Employee();
            emp.setId(line.get(0));
            emp.setFirst_name(line.get(1));
            emp.setLast_name(line.get(2));
            emp.setEmail(line.get(3));
            emp.setGender(line.get(4));
            emp.setMobile(line.get(5));
            emp.setSalary(line.get(6));
            emps.add(emp);
        }

        System.out.println("Size of the employees : " + emps.size());
        scanner.close();
        return emps;
    }

    public static List<String> parseLine(String cvsLine) {
        return parseLine(cvsLine, DEFAULT_SEPARATOR, DEFAULT_QUOTE);
    }

    public static List<String> parseLine(String cvsLine, char separators) {
        return parseLine(cvsLine, separators, DEFAULT_QUOTE);
    }

    public static List<String> parseLine(String cvsLine, char separators, char customQuote) {

        List<String> result = new ArrayList<>();

        // if empty, return!
        if (cvsLine == null && cvsLine.isEmpty()) {
            return result;
        }

        if (customQuote == ' ') {
            customQuote = DEFAULT_QUOTE;
        }

        if (separators == ' ') {
            separators = DEFAULT_SEPARATOR;
        }

        StringBuffer curVal = new StringBuffer();
        boolean inQuotes = false;
        boolean startCollectChar = false;
        boolean doubleQuotesInColumn = false;

        char[] chars = cvsLine.toCharArray();

        for (char ch : chars) {

            if (inQuotes) {
                startCollectChar = true;
                if (ch == customQuote) {
                    inQuotes = false;
                    doubleQuotesInColumn = false;
                } else {

                    // Fixed : allow "" in custom quote enclosed
                    if (ch == '\"') {
                        if (!doubleQuotesInColumn) {
                            curVal.append(ch);
                            doubleQuotesInColumn = true;
                        }
                    } else {
                        curVal.append(ch);
                    }

                }
            } else {
                if (ch == customQuote) {

                    inQuotes = true;

                    // Fixed : allow "" in empty quote enclosed
                    if (chars[0] != '"' && customQuote == '\"') {
                        curVal.append('"');
                    }

                    // double quotes in column will hit this!
                    if (startCollectChar) {
                        curVal.append('"');
                    }

                } else if (ch == separators) {

                    result.add(curVal.toString());

                    curVal = new StringBuffer();
                    startCollectChar = false;

                } else if (ch == '\r') {
                    // ignore LF characters
                    continue;
                } else if (ch == '\n') {
                    // the end, break!
                    break;
                } else {
                    curVal.append(ch);
                }
            }

        }

        result.add(curVal.toString());

        return result;
    }

}

//index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
    <%@ page import="java.util.List"%>
    <%@ page import="com.query.generator.*"%>
    <%

        List<Employee> eList = null;
        CSVReaderUtility utility = new CSVReaderUtility();
        eList = utility.getEmps();

    %>

    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>Employee Details</title>
    </head>
    <body>
    <form action="/generate" method="post">
    <table>

    <tr>
        <td>
        <td><input type="checkbox" name="col" value="id"></td>
        <td><input type="checkbox" name="col" value="first_name"></td>
        <td><input type="checkbox" name="col" value="last_name"></td>
        <td><input type="checkbox" name="col" value="email"></td>
        <td><input type="checkbox" name="col" value="gender"></td>
        <td><input type="checkbox" name="col" value="mobile"></td>
        <td><input type="checkbox" name="col" value="salary"></td>

    </tr>
            <%
                for (Employee ee : eList) {

            %>
            <tr>
                <%
                    String data = "";
                    // 123,john,123123,eny
                    // 2354 ,krishna,44444,msn
                    StringBuffer buffer = new StringBuffer();
                    buffer.append(ee.getId()).append(",");
                    buffer.append(ee.getFirst_name()).append(",");
                    buffer.append(ee.getLast_name()).append(",");
                    buffer.append(ee.getEmail()).append(",");
                    buffer.append(ee.getGender()).append(",");
                    buffer.append(ee.getMobile()).append(",");
                    buffer.append(ee.getSalary());
                    data = buffer.toString();       
                %>
                <td><input type="checkbox" name="row" value=<%=data%>></td>
                            <td><%=ee.getId()%></td>
                            <td><%=ee.getFirst_name()%></td> 
                            <td><%=ee.getLast_name()%></td>
                            <td><%=ee.getEmail()%></td>
                            <td><%=ee.getGender()%></td>
                            <td><%=ee.getMobile()%></td>
                            <td><%=ee.getSalary()%></td>     
            </tr>     
            <%
                }
            %>
    </table>
    <input type="submit" value="generate"/>
    </form>
    </body>
    </html>

Answer 1

I am not exactly sure, what you actually want. The end goal. But i will try to help.

I see that the CSV file you have an id field. Then each line could be identified/selected by its id.

The CSVReaderUtility

Instead of creating a List in your CSVReaderUtility getEmps() method in the line:

List<Employee> emps = new ArrayList<>();
//...
emps.add(emp);

you could create a map with key the id. Like this:

Map<String,Employee> emps = new HashMap<String,Employee>();
//...
emps.put(emp.getId(), emp);

This will make the line easier to access later.

The JSP

You JSP form action go to the Servlet mapped to "/generate". In this Servlet you could read the same CSV file again in a map and access the whole line only with a submitted id. There is no need to submit the full line data.
I mean concretely this:

            <%
                String data = "";
                // 123,john,123123,eny
                // 2354 ,krishna,44444,msn
                StringBuffer buffer = new StringBuffer();
                buffer.append(ee.getId()).append(",");
                buffer.append(ee.getFirst_name()).append(",");
                buffer.append(ee.getLast_name()).append(",");
                buffer.append(ee.getEmail()).append(",");
                buffer.append(ee.getGender()).append(",");
                buffer.append(ee.getMobile()).append(",");
                buffer.append(ee.getSalary());
                data = buffer.toString();       
            %>
            <td><input type="checkbox" name="row" value=<%=data%>></td>

could be replaced with:

            <td><input type="checkbox" name="employeeId" value=<%=ee.getId()%>></td>

Instead of having input tags in the first table tow

    <td><input type="checkbox" name="col" value="id"></td>
    <td><input type="checkbox" name="col" value="first_name"></td>
    <td><input type="checkbox" name="col" value="last_name"></td>
    <td><input type="checkbox" name="col" value="email"></td>
    <td><input type="checkbox" name="col" value="gender"></td>
    <td><input type="checkbox" name="col" value="mobile"></td>
    <td><input type="checkbox" name="col" value="salary"></td>

you could have just normal text like

    <td>id</td>
    ...        
    <td>salary</td>

And the important part in order to get the value of the column

The code

                        <td><%=ee.getId()%></td>
                        <td><%=ee.getFirst_name()%></td> 
                        <td><%=ee.getLast_name()%></td>
                        <td><%=ee.getEmail()%></td>
                        <td><%=ee.getGender()%></td>
                        <td><%=ee.getMobile()%></td>
                        <td><%=ee.getSalary()%></td>

could be replaced with

         ...
         <td><input type="text" name="salary" value="<%=ee.getSalary()%>"></td>

But like already said. The id should be enough for further processing in the servlet.

Additionally the form could be splitted to many forms. One form each csv line.

Instead of scriptlets <% %> you could use Expression Language and jstl iteration.
It make the JSP easier to maintain.
Here you can read how to generate JSP Content from map with EL and JSTL
The answer is about loading a data from property file, but with CSV file is the same.