How To Read CSV File Column By Column Using Java?

Why we need CSV Parser?

Being a Developer/Automation tester CSV(Comma-Separated Values ) Parsing is the one of frequent task which we need in day to day work. Most of the complex application reports generates in CSV format and further processed by downstream. So, 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.

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). This tutorial as based on jackson-dataformat-csv api.

CSV Sample File

Overview of CSV data structure:

CSV Sample File
CSV Sample File

Here is data in Text form:

Emp Id: 1Jack 45 New York 50000
Emp Id: 2Mack 35 London 60000
Emp Id: 3Andrew 45 Berlin 754100
Emp Id: 4John 25 NewDelhi 95000
Emp Id: 5Sonia 66 GGN 74455

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}]

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. Basic idea of CSV, Map and HashMap

  • Step 1: Open IDE (Intellij or Eclipse)
  • Step 2: Open/Create Maven Project and click on POM.xml file
  • Step 3: 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>
Maven jackson-dataformat-csv Dependency
Maven jackson-dataformat-csv 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>
UTF-8 properties
UTF-8 properties in Maven Project
  • Step 5: Run Maven Update, Clean & Install
Maven
Maven
Install Maven
Maven install
  • Step 6: Go to src/test/Java and create Util.java class and write CSV Parser Code.

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 return type of this method is Map which has values in HashMap. 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).

CSV Parser Algorithm Java

  • Step 1: Load CSV file in Reader class to parse CSV Data
Reader reader = new FileReader("C:\Users\user\OneDrive\Documents\"+FileName+".csv");
  • Step 2: Identify CSV separator (comma delimitor, pipe separated etc.)
  • Step 3: 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);
  • Step 4: 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>>();
  • Step 5: Iterate step 3 iterator upto each row and put values in step 4 Map i.e CSVData.
 while(iterator.hasNext()){
 keyVals = iterator.next();
 Object[] keys = keyVals.keySet().toArray();
                CSVData.put(keyVals.get(keys[primaryKeyIndex]), keyVals);
}
  • Step 6: 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;
    }
}
Util.java
  • Step 7: 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

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

Output:

Emp Id: 1Jack 45 New York 50000
Emp Id: 2Mack 35 London 60000
Emp Id: 3Andrew 45 Berlin 754100
Emp Id: 4John 25 NewDelhi 95000
Emp Id: 5Sonia 66 GGN 74455

[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}]

Above step by step tutorial written to help you to parse CSV and use data and further process. Hope this article helps you. Still if you face any issue feel free to reach out 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

0 0 vote
Article Rating
close

Subscribe to our Newsletter:

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x
Open chat
Feel free to contact