Reading CSV, XLS files using java

 A CSV is a comma separated values file, which allows data to be saved in a table structured format. If we get each row stored in csv file as array we can easily get the required values using index.


There is one open-source jar to parse csv file which will make our job so simple

opencsv

opencsv is a simple CSV Parser for Java under a commercial-friendly Apache 2.0 license. Download it from below location.

OpenCSV : Download

jxl

JExcelApi is a java library which provides the ability to read, write, and modify Microsoft Excel spreadsheets.
JXL ; Download

After downloading the jars, include it in your classpath.
In Eclipsr IDE,
  • Right click on project
  • Build Path => Configure build path..

  • Libraries => Add External jar => Select the above downloaded jar file
  • Click Ok. Now It is ready to use in your eclipse.

Here is the content of my csv file & xls file



Here is the Java Code to read csv, xls files.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
import java.io.File;
import java.io.FileReader;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
 
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
 
import com.opencsv.CSVReader;
 
public class FileReading {
  
    public static void main(String[] args){
   
        System.out.println("=====XLS=====");
        List<string> xls=getRowsFromXLSFile("/home/test/Contacts.xls");
        for(String eachRow[]:xls) {
            System.out.println(Arrays.toString(eachRow));
        }
         
       System.out.println("=====CSV=====");
       List<string> csv=getRowsFromCSVFile("/home/test/Contacts.csv");
       for(String eachRow[]:csv) {
           System.out.println(Arrays.toString(eachRow));
       }
    }
     
     /**
     * Method to read all rows from csv file using opencsv
     * @param path filePath
     * @return list of String arrays
     */
    public  static List<string> getRowsFromCSVFile(String path)
    {
         List<string> list=new LinkedList<string>();
        try
        {
            CSVReader csvReader = new CSVReader(new FileReader(new File(path)));
            list = csvReader.readAll();
        }
        catch(Exception e)
        {
           e.printStackTrace();
        }
        return list;
    }
     /**
     * Method to read all rows from xls file using jxl
     * @param path filePath
     * @return list of String arrays
     */
    public static List<string> getRowsFromXLSFile(String path) // Print and get
    {
     List<string> rows=new LinkedList<string>();
     try
     {
         File inputWorkbook = new File(path);
         Workbook workbook = Workbook.getWorkbook(inputWorkbook);
         Sheet sheet = workbook.getSheet(0);
         int maxCols=sheet.getColumns();
         int maxRows=sheet.getRows();
    
      for(int i=0;i<maxRows;i++) {
         String erows[] = new String[maxCols];
         int k=0;
         for(int j=0;j<maxCols;j++) {
             Cell cell1 = sheet.getCell(j,i);
             erows[k]=cell1.getContents();
             k++;
         }
         rows.add(erows);
     }
    } catch(Exception e){
         e.printStackTrace();
    }
    return rows;
  }
}
</string></string></string></string></string></string></string></string>

?
1
2
3
4
5
6
7
8
9
10
11
//Here is output for above file
=====XLS=====
[Name, email, phone]
[Jhon, Jhon@test.com, 234789234]
[Smith, Smith@test.com, 234728934]
[Jack, jack@test.com, 234234234]
=====CSV=====
[Name, email, phone]
[Jhon, Jhon@test.com, 234789234]
[Smith, Smith@test.com, 234728934]
[Jack, jack@test.com, 2394728394]
at