Java: How to compare columns of a CSV file and print specific lines

Question

I'm new to Java programming and I've been tasked to parse a CSV file and print out the correct row of the CSV file, by comparing columns e.g. the lines printed need to have a unique 1st and 2nd column, that the 3rd column must contain "no" and that the 4th column has a value greater or equal to 12.

There are 5 columns in total.

Also I'm not allowed to use any libraries that parse the CSV for you.

Here is my code so far:

private static String[] routerData;

public static void main(String [] args)  {

    // Input of file which needs to be parsed
    String csvFile = "./sample.csv";
    BufferedReader csvReader = null;

    // Data split by ',' in CSV file
    String line = null;
    String csvSplitBy = ",";

    try {

        // Create the CSV file reader
        csvReader = new BufferedReader(new FileReader(csvFile));
        while ((line = csvReader.readLine()) !=null) {

            routerData = line.split(csvSplitBy, -1);
            System.out.println(routerData[0] + ", " + routerData[1] + ", " + routerData[2] + ", " + routerData[3] + ", " + routerData[4]);

        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (csvReader != null) {
            try {
                csvReader.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

}

Edit Sample CSV:

For example, a sample CSV file would look like this:

Name,IP,Working?,Version,Comments
name,0.0.0.0,No,11.2,Some GUI Bugs
name2,0.1.2.0,Yes,12.3,
name,0.0.1.0,Yes,12.2,Case broken,
name4,0.0.0.0,no,10.3

So the code has to take a CSV file and then print out the rows that have a Unique name (1st column), Unique IP (2nd column), "No" for 'Working?' (3rd column) and a version value above 12.

I'm not sure what Data structure to use that will allow me to compare columns.

I'm very thankful for any advice!

Must the first and second columns be unique towards each other or towards the 1st and 2nd columns of the entire CSV data file? — DevilsHnd - 退した
– DevilsHnd - 退した, Commented Dec 28, 2016 at 1:08
@DevilsHnd Unique for the Entire CSV file. So I have to print out the correct row from the CSV file only if it matches all the requirements. — Adil Khan
– Adil Khan, Commented Dec 28, 2016 at 19:13
In that case @Adil Khan, check out my answer to your question. — DevilsHnd - 退した
– DevilsHnd - 退した, Commented Dec 29, 2016 at 4:40

DevilsHnd - 退した · Accepted Answer · 2016-12-30 02:25:38Z

The requirements within your question are rather confusing and things don't get any easier if there is no example CSV data. The confusing portion is in relation to Column 1 and Column 2 data regarding uniqueness. What do you consider to be UNIQUE?

A) Is it to be Unique in the sense that Column 1 and or Column 2 contains a string or numerical value which is not duplicated in any other CSV file line (no duplicate within its respective column)?

B) Or is it to be Unique in the sense that Column 1 and or Column 2 contains a string or numerical value which is unique upon itself and can be found in other CSV file lines (duplicates allowed within its respective column)?

The example code below assumes unique condition (A). This means then, if a CSV file contained the following comma delimited lines then only two of those lines would fulfill the specified data conditions:

Jack,Flash,yes,14,Unknown Value
Bob,Stick,no,11,Unknown Value
Jack,Flash,no,22,Unknown Value
Fred,Frog,yes,6,Unknown Value
Bob,Stick,no,32,Unknown Value
Tracey,Jones,no,17,Unknown Value
Fred,Frog,no,23,Unknown Value
John,Brown,no,12,Unknown Value
Bob,Stick,yes,88,Unknown Value

since only those two lines have columns 1 and 2 which are truly unique to the entire CSV file. Can you see which lines they are?

Here is the example code:

ArrayList<String> resultList = new ArrayList<>();
ArrayList<String> linesList = new ArrayList<>();
// Input of file which needs to be parsed
String csvFile = "sample.csv";
BufferedReader csvReader;

// Data split by ',' in CSV file
String csvSplitBy = ",";
try {
    // Read the CSV file into an ArrayList array for easy processing.
    String line;
    csvReader = new BufferedReader(new FileReader(csvFile));
    while ((line = csvReader.readLine()) !=null) {
        linesList.add(line);
    }
    csvReader.close();
} 
catch (IOException e) { e.printStackTrace(); } 

// Process each CSV file line which is now contained within
// the linesList list Array
for (int i = 0; i < linesList.size(); i++) {
    String[] data = linesList.get(i).split(csvSplitBy);
    String col1 = data[0];
    String col2 = data[1];
    String col3YesNo = data[2];
    //int col4Value = Integer.parseInt(data[3]);  //WAS THIS
    double col4Value = Double.parseDouble(data[3]); // *** SHOULD BE ***
    String col5Unknown = data[4];

    // Determine if Column 1 and Column 2 data for the 
    // current line is unique to the entire CSV file.
    boolean columns1And2AreUnique = true;
    for (int j = 0; j < linesList.size(); j++) {
        String[] tmp = linesList.get(j).split(csvSplitBy);
        // Make sure we don't process the same line we are on...
        if (j != i) {
            if (col1.equals(tmp[0]) || col2.equals(tmp[1])) { 
                columns1And2AreUnique = false; 
                break;
            }
        }
    }
    if (columns1And2AreUnique && col3YesNo.equalsIgnoreCase("no") && col4Value >= 12.0) {
        resultList.add(linesList.get(i));
    }
}

// Display the determined results from the CSV file.
if (resultList.isEmpty()) {
    System.out.println("There could be no data results gathered from the supplied\n"
                     + "CSV file which meets the required criteria.");
}
else {
    System.out.println("Column 1\tColumn 2\tColumn 3\tColumn 4\tColumn 5");
    System.out.println("================================================"
                     + "========================\n");
    String padString = "        "; //Used for simple space padding in display
    for (int i = 0; i < resultList.size(); i++) {
        String[] tmp = resultList.get(i).split(csvSplitBy);
        System.out.println(tmp[0] + padString.substring(tmp[0].length()) + "\t" 
                         + tmp[1] + padString.substring(tmp[1].length()) + "\t" 
                         + tmp[2] + padString.substring(tmp[2].length()) + "\t"
                         + tmp[3] + padString.substring(tmp[3].length()) + "\t"
                         + tmp[4]);
    }
}

EDIT: Now that you have posted some sample data from your CSV file...

Well, I was very close with my columnar CSV Data Type assumptions but there does need to be a code change since I now know that the 4th data column contains Double data type values.

You will need to change the code line that states:

int col4Value = Integer.parseInt(data[3]);

to this code line which handles the Double data type values:

double col4Value = Double.parseDouble(data[3]);

This should help you a little while you are modifying the code to suit your needs.

Sorry about that, I should've added an example CSV! But yes it would be Unique Condition (A). Check my post again, please! I've added an edit that should help! Hope this makes it clear. I'm also going to edit your code and see if i can make it work! Thank you so much for all your help!
@Adil Khan, Thank you for posting your CSV data sample. There has been a minor one line code modification to accommodate the fact that column 4 of a CSV data line contains a Double data type. See my Edited post above.
@DevilsHnd your answer solved my problem. Thanks for the help.

Mcmil · Accepted Answer · 2016-12-28 09:27:04Z

0

I will do it like that. First create a simple class for representing one row of data, let it be A named. Next create wrapper class B for this data set containing list of A objects as field. Make public method for this class returning that rows which fulfills these predicates passed as parameters. To find unique values create static public utility method in another class. This method takes as parameter for exemple list of strings (1 column) and will return unique value which you can pass next to method of B class. Look at Java 8 stream api and map method to get i.e. list of first column values to pass to that utility method you can do like this:

b.getList().stream().map(e -> e.getFirstValue()).collect(Collectors.asList());

edited Dec 28, 2016 at 9:27

answered Dec 28, 2016 at 0:54

Mcmil

91711 silver badges26 bronze badges

1 Comment

Adil Khan Over a year ago

Thank you So much! I'm going to try your method now and will report back!

Collectives™ on Stack Overflow

Java: How to compare columns of a CSV file and print specific lines

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related