Sort the column in a CSV File in Java

Question

I have a CSV file which reads this.

City,Job,Salary
Delhi,Doctors,500
Delhi,Lawyers,400
Delhi,Plumbers,100
London,Doctors,800
London,Lawyers,700
London,Plumbers,300
Tokyo,Doctors,900
Tokyo,Lawyers,800
Tokyo,Plumbers,400
Lawyers,Doctors,300
Lawyers,Lawyers,400
Lawyers,Plumbers,500
Hong Kong,Doctors,1800
Hong Kong,Lawyers,1100
Hong Kong,Plumbers,1000
Moscow,Doctors,300
Moscow,Lawyers,200
Moscow,Plumbers,100
Berlin,Doctors,800
Berlin,Plumbers,900
Paris,Doctors,900
Paris,Lawyers,800
Paris,Plumbers,500
Paris,Dog catchers,400`

Now, I want to sort the column of Salary and write it to a txt file.

I can access the column, but not able to sort it. I am new to this CSV Reading part of Java. Can someone help! How should I store each salary value to a variable.

import java.io.*;

public class Main {

    public static void main(String args[]) throws FileNotFoundException
    {
        String csv="C:\\Users\\Dipayan\\Desktop\\salaries.csv";
        BufferedReader br= new BufferedReader(new FileReader(csv));
        String line="";

        try {
            br.readLine();

            while((line = br.readLine())!=null)
            {
                String[] f=line.split(",");
                System.out.println(" Salary ="+f[2]);
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Answer 1

You can try something like -

String csv="/home/user/Desktop/salaries.csv";
BufferedReader br= new BufferedReader(new FileReader(csv));
String line;
Map<Integer, String> salaryMap = new TreeMap<Integer, String>();

try {
    br.readLine();
    while((line = br.readLine()) != null) {
        salaryMap.put(Integer.parseInt(line.split(",")[2]), line);
    }
} catch (IOException e) {
    e.printStackTrace();
}


try {
    FileWriter fw = new FileWriter("/home/user/Desktop/salaries.txt");
    BufferedWriter bw = new BufferedWriter(fw);
    List<String> list = new ArrayList<String>(salaryMap.values());
    for (String str : list) {
        bw.write(str);
        bw.newLine();
    }

    bw.flush();
    bw.close();

} catch (IOException e) {
    e.printStackTrace();
}

Answer 2

I think you should use map here or may be

List<Integer, String> 

In case of map

 Map<Integer,String>

where key would be the salary and the string would be the whole complete row. Let me write some code to help you.

 Map<Integer,String> dataMap = new HashMap<Integer,String>();
 String row = br.readLine();
 String columns[]=row.split(","); // columns[2] has the salary now
 dataMap.add(Integer.valueOf(columns[2]),row);

Use bubble sort to saw the content or your customer compare class to sort the map. Check what are the possible sort functions of Collection Api.

You can use even List<Integer,String>

Sort with respect to integer, keep placing the elements on their actual positions and re-generate the file.

EDIT: You can use tree map since it sorts the data. Just use Treemap of integer as key and string as the value. Put all the values in it and iterate over it, that's all!

Answer 3

Be careful with the map, put will override your previous values.

You can use Arrays.sort that will sort a native array or use guavas Iterables.sortedCopy

Here´s a codebank for Arrays.sort: http://codebunk.com/b/40731608/

Answer 4

Java NIO

• Java has provided a second I/O system called NIO (New I/O).

• NIO was developed to allow Java programmers to implement high-speed I/O without using the custom native code. NIO moves the time-taking I/O activities like filling, namely and draining buffers, etc back into the operating system, thus allows for a great increase in operational speed.

  import java.io.File;
  
  import java.nio.charset.Charset;
  import java.nio.file.Files;
  import java.nio.file.Path;
  
  import java.util.List;
  

  // ...

  Path filePath = new File("csv_file_path").toPath();
  Charset charset = Charset.defaultCharset();        
  List<String> stringList = Files.readAllLines(filePath, charset);
  String[] stringArray = stringList.toArray(new String[]{});
  
  
  Arrays.sort(stringArray, new Comparator<String>(){
      public int compare(String first, String second) {
          return Integer.valueOf(first.split(",")[2]).compareTo(Integer.valueOf(second.split(",")[2]));
      }
  });
  
  for (String newstr: stringArray){
      System.out.println(newstr);
  }
  

Packages Used in Above Code: -

• java.nio.charset :- It encapsulates the character sets and also supports encoders and decoders operation that convert characters to bytes and bytes to characters, respectively.

• java.nio.file :- It provides the support for files.

Methods Used: -

• toPath() :- (No parameters)

  This method returns a java.nio.file.Path object constructed from the this abstract path. Exception:- InvalidPathException – if a Path object cannot be constructed from the abstract path (see FileSystem.getPath)

  Example: -

  File file = new File("C:\\sandbox\\test.txt");
  System.out.println(file.toPath());
  
  Output:- C:\sandbox\test.txt 
  

  Note:- It is mandatory to add an exception for above code

• defaultCharset() : - (No parameters)

  This method returns the default charset of this Java virtual machine.

• public static List <String> readAllLines(Path path, Charset cs) :-

  Parameters (path - the path to the file, cs - the charset to use for decoding)

  This method read all the lines from the file as a List.

Now, I have converted the List into an Array using toArray() method and implemented a Comparator for sorting an array. I have overridden the compare method and comparing two strings which are the third string in each sentence after comma(,) and converting them to a number and arranging them in a sorted order.