Question

Source: https://stackoverflow.com/questions/67935089/how-can-i-read-out-multiple-csv-files-and-combine-them-into-one-using-java

I am trying to read multiple csv files (for example, one with first names and gender and one with streetnames or whatever) and create a new csv file with all the information merged but I don't know how to do that in Java. At the moment I can read out a csv file and have hard coded a writer to create a new csv file but then I don't know how to continue.

My first csv file looks like this:

anzahl;vorname;geschlecht;position

207;Marie;w;2 ... n records

My second file looks like this:

STRASSE,STRANAM,HSZ

1,Aachener Str.,1 ... n records

Basically, if headers don't match, all new header titles(columns) should be added after original header and their values according to that order.

For example, the new csv file should look like this:

anzahl;vorname;geschlecht;position;STRASSE;STRANAM;HSZ

207;Marie;w;2;1;Aachener Str.;1

This is my code right now:

public void doCsvReading() throws IOException {

var fileName = "D:/IntelliJ/Offis_Project/src/main/resources/Vornamen_2020_Koeln.csv";

try {

var fr = new FileReader(fileName, StandardCharsets.UTF_8);

var reader = new CSVReader(fr);

String[] nextLine;

while((nextLine = reader.readNext()) != null) {

for (var e : nextLine) {

System.out.println(e);

}

}

} catch (Exception e) {

e.printStackTrace();

}

}

public void doCsvWriting() throws IOException {

CSVWriter writer = new CSVWriter(new FileWriter("Odin.csv"));

List<String[]> loki = new ArrayList<>();

String[] gkvHeader = new String[] {"id_intern",

"FirstName",

"LastName",

"FirstBirthName",

"LastBirthName",

"Gender",

"BirthDate",

"BirthYear",

"DeathDate",

"PlaceNow",

"StreetNow",

"HouseNumberNow",

"PostCodeNow",

"CityOfBirth",

"PostCodeBirth"};

loki.add(gkvHeader);

String[] row1 = new String[] {"1", "Bob", "Waters", "Bob", "Waters", "m", "09.06.2021", "2021", "","Oldenburg","Ammerländer Heerstaße","15","26129","Aurich","26721"};

loki.add(row1);

writer.writeAll(loki);

writer.close();

}

Answer

You want to combine two CSV files having same number of rows horizontally into one CSV. The code will be very long if you are trying to do this in Java.

It’s simple to get this done using SPL, an open-source Java package. Only one line of code is enough:

	A
1	>file("result.csv").export@cw(file("1.csv").import@w(;,";").(~|file("2.csv").import@wc()(#)))

 

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as combinecsv.splx and invoke it in Java as you call a stored procedure:

…

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

st=con.prepareCall("call combinecsv()");

st.execute();

…

Or execute the SPL string within a Java program as we execute a SQL statement:

…

st = con.prepareStatement("=>file(\"result.csv\").export@cw(file(\"1.csv\").import@w(;,\";\").(~|file(\"2.csv\").import@wc()(#)))");
st.execute();

…

View SPL source code.