I'm writing a little software that automatically exports a table called "Magazzino" of a .mdb file to a csv file. Everything works fine and I wrote this code using "UCANACCESS" library.
package it.simonesaleri.automation.software;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
public class AutomationMain {
public static void main(String[] args) throws IOException {
ArrayList<String> arr = new ArrayList<String>();
try {
//OPENING CONNECTION TO DATABASE
Connection conn = DriverManager.getConnection("jdbc:ucanaccess://eCommerce.mdb");
FileWriter writer = new FileWriter("eCommerce.csv");
System.out.println("Connection to Database");
Statement s = conn.createStatement();
//SQL INJECT CODE
String sqlString = "SELECT * FROM Magazzino";
ResultSet rs = s.executeQuery(sqlString);
ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
while (rs.next()) {
for (int i = 1; i < columnsNumber; i++) {
System.out.println(rs.getString(i));
arr.add(rs.getString(i));
}
}
//SAVING INTO THE FILE
for(String str: arr)
{
writer.append(str);
writer.append(";");
}
writer.flush();
writer.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
I'm having an issue...I don't really understand how to create a new line on the .csv file while writing on it. I tried to .append("\n") or .append("\r\n"), but the results is this:
1;244;300817; BA9S - 220R LAMPADA LED 220VAC ROSSA;;;;;;;;;;;;;;;;12.14;3.6420000000000003;5.6451;5.463000000000001;5.098800000000001;4.7346;5.88183;PCE;22.0;IVA 22%;0.0;0.0;0.0;0.0;;731;CHINT;CHT;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;CHTCHTAUTOM-A01;;;null;null;FALSE;FALSE;TRUE;null;0;;;;;;;;;;;;;null;null;null;null;null;null;null;null;null;FALSE;FALSE;0;0;2;9713;BSD450B/8A; CONVERTITORE BRUSHLESS FD BSD450B/8A;;;;;;;;;;;;;;;;680.0;680.0;1054.0;1020.0;952.0;884.0;1098.2;;22.0;IVA 22%;0.0;0.0;0.0;0.0;;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;;;;null;null;FALSE;FALSE;TRUE;null;0;;;;;;;;;;;;;null;null;null;null;null;null;null;null;null;FALSE;FALSE;0;0;
Instead of this (result that I need):
1;244;300817; BA9S - 220R LAMPADA LED 220VAC ROSSA;;;;;;;;;;;;;;;;12.14;3.6420000000000003;5.6451;5.463000000000001;5.098800000000001;4.7346;5.88183;PCE;22.0;IVA 22%;0.0;0.0;0.0;0.0;;731;CHINT;CHT;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;CHTCHTAUTOM-A01;;;null;null;FALSE;FALSE;TRUE;null;0;;;;;;;;;;;;;null;null;null;null;null;null;null;null;null;FALSE;FALSE;0;0;
2;9713;BSD450B/8A; CONVERTITORE BRUSHLESS FD BSD450B/8A;;;;;;;;;;;;;;;;680.0;680.0;1054.0;1020.0;952.0;884.0;1098.2;;22.0;IVA 22%;0.0;0.0;0.0;0.0;;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;0;null;null;;;;null;null;FALSE;FALSE;TRUE;null;0;;;;;;;;;;;;;null;null;null;null;null;null;null;null;null;FALSE;FALSE;0;0;
You could do it this way I suppose:
StringBuilder sb = new StringBuilder("");
while (rs.next()) {
// Clear StringBuilder object in prep for a new CSV line
sb.setLength(0);
// Create custom CSV data line using semicolon (;) as delimiter...
for (int i = 1; i < columnsNumber; i++) {
/* IF StringBuilder is not empty then add a semicolon
to the end of the last piece of columnar data that
was appended before appending this next piece of
columnar data. */
if (!sb.toString().isEmpty()) {
sb.append(";");
}
sb.append(rs.getString(i));
}
// Add the system newline character to custom CSV data line.
sb.append(System.lineSeparator());
// Add the newly created CSV line to the ArrayList.
arr.add(sb.toString());
}
// Write to File...
for(String str: arr) {
/* str already has the newline character appended
to it from the process above. */
writer.append(str);
// immediate write:
writer.flush();
}
writer.close(); // Close the writer.