Sorting a CSV file by district
问题
https://stackoverflow.com/questions/67517258/sorting-a-csv-file-by-district
Hello I wanna create a program that sorts the following csv file I'm trying to sort the file by their district. So the following program reads the file and is able to sort it but it sorts it by their name and I wanna change it so it sorts by district any help is appreciated.
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
public class SortDistrict
{
private static final String COLUMN_SEPARATOR = ",";
public static void main(String[] args) throws Exception
{
InputStream inputStream = new FileInputStream("data.csv");
List<List<String>> lines = readCsv(inputStream);
// Create a comparator that compares the elements from column 0,
// in ascending order
Comparator<List<String>> c0 = createAscendingComparator(0);
// Create a comparator that compares the elements from column 2,
// in descending order
Comparator<List<String>> c1 = createDesendingComparator(2);
// Create a comparator that compares primarily by using c0,
// and secondarily by using c1
Comparator<List<String>> comparator = createComparator(c0, c1);
Collections.sort(lines, comparator);
OutputStream outputStream = new FileOutputStream("output.csv");
String header = "Last Name, First Name, Email, Address, Age, District, Gender";
writeCsv(header, lines, outputStream);
}
private static List<List<String>> readCsv(
InputStream inputStream) throws IOException
{
BufferedReader reader = new BufferedReader(
new InputStreamReader(inputStream));
List<List<String>> lines = new ArrayList<>();
String line = null;
// Skip header
line = reader.readLine();
while (true)
{
line = reader.readLine();
if (line == null)
{
break;
}
List<String> list = Arrays.asList(line.split(COLUMN_SEPARATOR));
lines.add(list);
}
return lines;
}
private static void writeCsv(
String header, List<List<String>> lines, OutputStream outputStream)
throws IOException
{
Writer writer = new OutputStreamWriter(outputStream);
writer.write(header+"\n");
for (List<String> list : lines)
{
for (int i = 0; i < list.size(); i++)
{
writer.write(list.get(i));
if (i < list.size() - 1)
{
writer.write(COLUMN_SEPARATOR);
}
}
writer.write("\n");
}
writer.close();
}
@SafeVarargs
private static <T> Comparator<T>
createComparator(Comparator<? super T>... districts)
{
return (t0, t1) ->
{
for (Comparator<? super T> district : districts)
{
int n = district.compare(t0, t1);
if (n != 0)
{
return n;
}
}
return 0;
};
}
private static <T extends Comparable<? super T>> Comparator<List<T>>
createAscendingComparator(int index)
{
return createListAtIndexComparator(Comparator.naturalOrder(), index);
}
private static <T extends Comparable<? super T>> Comparator<List<T>>
createDesendingComparator(int index)
{
return createListAtIndexComparator(Comparator.reverseOrder(), index);
}
private static <T> Comparator<List<T>>
createListAtIndexComparator(Comparator<? super T> delegate, int index)
{
return (list0, list1) ->
delegate.compare(list0.get(index), list1.get(index));
}
}
I also have a Person class if it'll be any use
public class Person implements Comparable<Person> {
private String name;
private String email;
private String address;
private String residency;
private String gender;
private int age;
private int district;
public Person(String name, String email, String address, String gender, String residency, int district, int age) {
this.name = name;
this.address = address;
this.age = age;
this.district = district;
this.residency = residency;
this.gender = gender;
}
public String getName() {
return name;
}
public String getAddress() {
return address;
}
public int getAge() {
return age;
}
public String getResidency() {
return residency;
}
public int getDistrict() {
return district;
}
public String getGender() {
return gender;
}
public String toString() {
return (name + "," + email + "," + address + "," + age + "," + residency + "," + district + "," + gender);
}
public int compareTo(Person another) {
if (district == another.getDistrict())
return 0;
else if (district < another.getDistrict())
return -1;
else
return 1;
} // end of compareTo
} // end of Person
The csv file is quite big but here is a few lines First Name, Last Name, Email, Address, Age, Residency, District, Gender
Colleen,Joyner,commodo.auctor@elementumat.net,Ap #697-1279 Nullam Road,30,Resident,4,Female
Fay,Parker,augue.ut.lacus@egetvarius.edu,"P.O. Box 234, 6576 Et, Ave",24,Resident,4,Female
TaShya,Atkinson,sem.egestas@urna.com,"6319 At, St.",45,Resident,15,Female
Curran,Shannon,massa@arcu.com,"980 In, Rd.",57,Resident,8,Male
Yolanda,Snyder,ipsum.ac@Sednullaante.org,"P.O. Box 769, 8207 Egestas Avenue",54,Non-Resident,4,Female
Candice,Weaver,ligula@Aenean.ca,"Ap #599-9287 Tellus, Rd.",35,Resident,9,Female
Yoshio,Silva,fames@Cumsociisnatoque.co.uk,Ap #327-6404 Dui St.,19,Resident,4,Male
Thanks in advance
解答
这个问题需要对一个非常大的csv 文件按某字段排序，并将结果导出到另一个 csv，Java 实现则代码较长。
用Java 下的开源包 SPL 很容易写，只要 1 句：
A |
|
1 |
>file("output.csv").export@ct(file("data.csv").cursor@cqt().sortx(District)) |
SPL提供了JDBC 供 Java 调用，把上面的脚本存为 sortx.splx，在 Java 中以存储过程的方式调用脚本文件：
…
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
st=con.prepareCall("call sortx()");
st.execute();
…
或在JAVA 中以 SQL 方式直接执行 SPL 串：
…
st = con.prepareStatement("=>file(\"output.csv\").export@ct(file(\"data.csv\").cursor@cqt().sortx(District))");
st.execute();
…
English version