How To Read CSV File Column By Column Using Java?

Why we need CSV Parser?

How to read csv file column by column using Java! Although this may be true, being a Developer/Automation tester CSV(Comma-Separated Values ) Parsing is the one of frequent task which we need in day to day work. Another key point, most of the complex application reports generates in CSV format and further processed by downstream. Hence, during testing/development we need to validate CSV data in Databases, APIs or web portal. Also, these type of testing is quite repetitive and time consuming. So reading CSV column by column by using java code make the work more simple and interesting.

At this point, this step by step tutorial will help you by in writing CSV Parser.

Which CSV Parser API can be used?

Jackson data format is one of the faster csv reader and writer either as raw data or via data binding ot/from Objects (POJO classes). With this purpose in mind, this tutorial as based on jackson-dataformat-csv api.

CSV Sample File

Overview of CSV data structure:

How to read a CSV File as Map for Key Value pair?

Map<Key, Value>

[1={EmpId=1, Name=Jack, Age=45, Salary=50000, City=New York}, 2={EmpId=2, Name=Mack, Age=35, Salary=60000, City=London}, 3={EmpId=3, Name=Andrew, Age=45, Salary=754100, City=Berlin}, 4={EmpId=4, Name=John, Age=25, Salary=95000, City=NewDelhi}, 5={EmpId=5, Name=Sonia, Age=66, Salary=74455, City=GGN}]

All things considered, now, let’s move on to the coding part

Pre-Requisites For CSV Parser:

1. Java should be installed (if not then refer this link)
2. Maven should be installed(if not then refer this link)
3. Maven Project should be created.
4. In addition, basic idea of CSV, Map and HashMap

• Open IDE (Intellij or Eclipse)
• Open/Create Maven Project and click on POM.xml file
• Add Maven jackson-dataformat-csv dependency in with dependencies section.

<dependency>
<groupId>com.fasterxml.jackson.dataformat</groupId>
    <artifactId>jackson-dataformat-csv</artifactId>
    <version>2.9.9</version>
</dependency>

• Step 4: Add UTF-8 properties in Maven Project

<properties>  <project.java.version>1.8</project.java.version> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
</properties>

• Step 5: Run Maven Update, Clean & Install

• Step 6: Go to src/test/Java and create Util.java class and write CSV Parser Code.

Lastly, in this code, we will create a method which will return all the CSV Data in Map. The map is used to store Key and value. Here, we will use the unique column as Key to identify each row and for each key, we will store corresponding column values. So, in like manner, return type of this method is Map which has values in HashMap. Generally, as an input parameter, we will pass filename (which is saved at a specific location), one character( for separating), and column index( which will be treated as the primary key).

How to read CSV file column by column using Java – CSV Parser Algorithm Java

• At first, load CSV file in Reader class to parse CSV Data

Reader reader = new FileReader("C:\Users\user\OneDrive\Documents\"+FileName+".csv");

• Secondly, identify CSV separator (comma delimitor, pipe separated etc.)
• Create Iterator to iterate each CSV row over column and row.

Iterator<Map<String, String>> iterator = new CsvMapper().readerFor(Map.class)        .with(CsvSchema.emptySchema().withHeader()                  .withColumnSeparator(seperator).withoutQuoteChar()).readValues(reader);

• Create one object of Map type which hold another String (Key-Column as Key) and Map as as value.( for each row corresponding key and values).

Map<String, Map<String, String>> CSVData = new TreeMap<String, Map<String, String>>();

• Iterate bullet 3 iterator upto each row and put values in bullet 4 Map i.e CSVData.

 while(iterator.hasNext()){
 keyVals = iterator.next();
 Object[] keys = keyVals.keySet().toArray();
                CSVData.put(keyVals.get(keys[primaryKeyIndex]), keyVals);
}

• Return the CSVData Map as parse Data.

import com.fasterxml.jackson.dataformat.csv.CsvMapper;
import com.fasterxml.jackson.dataformat.csv.CsvSchema;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;
public class Util {
    public static Map<String, Map<String, String>> getCSVData(char seperator, String FileName, int primaryKeyIndex) throws FileNotFoundException {
        Map<String, Map<String, String>> CSVData = new TreeMap<String, Map<String, String>>();
        Map<String, String> keyVals = null;
        Reader reader = new FileReader("C:\\Users\\user\\OneDrive\\Documents\\"+FileName+".csv");
        try {
            Iterator<Map<String, String>> iterator = new CsvMapper().readerFor(Map.class)
                    .with(CsvSchema.emptySchema().withHeader()
                            .withColumnSeparator(seperator).withoutQuoteChar()).readValues(reader);
            while(iterator.hasNext()){
                keyVals = iterator.next();
                Object[] keys = keyVals.keySet().toArray();
                CSVData.put(keyVals.get(keys[primaryKeyIndex]), keyVals);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return CSVData;
    }
}

• Finally, write Code to test above code.

1. Create one map which holds data in Key (String) and values(Map- Column Name as Key and Value).
2. Iterate above collections over each Key
3. Get value using primary key and column Name

Given these points, refer below code for more details:

import jdk.nashorn.internal.parser.JSONParser;
import java.io.FileNotFoundException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;
public class parseCSVCode {
public static void main(String[] args) throws FileNotFoundException {
    System.out.println("+++++++++++++++++++++Code For CSV Parser++++++++++++");
    Map<String, Map<String, String> CSV= Util.getCSVData(',',"ParseCsv",0);
  Iterator<String> keys = CSV.keySet().iterator();
  while (keys.hasNext()){
      String primaryKey= keys.next();
      System.out.print("Emp Id: "+primaryKey);
      System.out.print(CSV.get(primaryKey).get("Name")+" ");
      System.out.print(CSV.get(primaryKey).get("Age")+" ");
      System.out.print(CSV.get(primaryKey).get("City")+" ");
      System.out.print(CSV.get(primaryKey).get("Salary")+" \n");
  }
    System.out.println(CSV.entrySet());
 }
}

[1={EmpId=1, Name=Jack, Age=45, Salary=50000, City=New York}, 2={EmpId=2, Name=Mack, Age=35, Salary=60000, City=London}, 3={EmpId=3, Name=Andrew, Age=45, Salary=754100, City=Berlin}, 4={EmpId=4, Name=John, Age=25, Salary=95000, City=NewDelhi}, 5={EmpId=5, Name=Sonia, Age=66, Salary=74455, City=GGN}]

To conclude, above step by step tutorial written to help you to parse CSV and use data and further process. For that reason, hope this article helps you. Still if you face any issue feel free contact us or reach us on query@thoughtcoders.com

Reference: https://github.com/FasterXML/jackson-dataformats-text/tree/master/csv
https://docs.oracle.com/javase/7/docs/api/java/io/Reader.html

To this end, for more updates, stay connected to us via Facebook and LinkedIn!