I want to write the result of a SQL query to a csv or Excel file and save it in a particular folder. I have following requests:
With use of openCSV API, you can export your data in csv file.
CSVWriter writer = new CSVWriter(new FileWriter("yourfile.csv"), '\t');
Boolean includeHeaders = true;
java.sql.ResultSet myResultSet = .... //your resultset logic here
writer.writeAll(myResultSet, includeHeaders);
writer.close();
Simplest solution.
Main Method
private List<String> resultSetArray=new ArrayList<>();
private String username =""; // Enter DB Username
private String password = ""; // Enter DB password
private String url = ""; // Enter DB URL
Connection connection=DriverManager.getConnection(url,user,pwd);
public static void main(String args[]) throws Exception{
fetchDataFromDatabase("SQL queries", connection);
printToCsv(resultArray);
}
fetchDataFromDatabase
The code below count the number of columns in a table, and store in a result array.
private void fetchDataFromDatabase(String selectQuery,Connection connection) throws Exception{
try {
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(selectQuery);
int numCols = rs.getMetaData().getColumnCount();
while(rs.next()) {
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= numCols; i++) {
sb.append(String.format(String.valueOf(rs.getString(i))) + " ");
}
resultSetArray.add(sb.toString());
}
} catch (SQLException e) {
LOGGER.error("Sql exception " + e.getMessage());
}
}
printToCsv
public static void printToCsv(List<String> resultArray) throws Exception{
File csvOutputFile = new File(file_name);
FileWriter fileWriter = new FileWriter(csvOutputFile, false);
for(String mapping : resultArray) {
fileWriter.write(mapping + "\n");
}
fileWriter.close();
}
It's difficult to export result set data from any tool.
ex: while exporting result set data to .csv file it does not export properly when data contains (,)
please refer below java code :
it works perfectly with any any query input and all type of data in result set
package com.demo.export;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class dataExportService {
public void getDefendants(Connection con , String db) throws Exception {
@SuppressWarnings("unused")
Workbook readWorkbook = WorkbookFactory.create(new FileInputStream(".xls file path(C:\\Users\\CEPL\\Desktop\\test.xls)") );
@SuppressWarnings("resource")
Workbook writeWorkbook = new HSSFWorkbook();
Sheet desSheet = writeWorkbook.createSheet("new sheet");
Statement stmt = null;
ResultSet rs = null;
try{
String query ="QUERY";
stmt = con.createStatement();
rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
Row desRow1 = desSheet.createRow(0);
for(int col=0 ;col < columnsNumber;col++) {
Cell newpath = desRow1.createCell(col);
newpath.setCellValue(rsmd.getColumnLabel(col+1));
}
while(rs.next()) {
System.out.println("Row number" + rs.getRow() );
Row desRow = desSheet.createRow(rs.getRow());
for(int col=0 ;col < columnsNumber;col++) {
Cell newpath = desRow.createCell(col);
newpath.setCellValue(rs.getString(col+1));
}
FileOutputStream fileOut = new FileOutputStream(".xls file path(C:\\Users\\CEPL\\Desktop\\test.xls)");
writeWorkbook.write(fileOut);
fileOut.close();
}
}
catch (SQLException e) {
System.out.println("Failed to get data from database");
}
}
}
Here is an example:
import java.io.*;
import java.sql.*;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
public class ExcelFile {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test", "root", "root");
PreparedStatement psmnt = null;
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("Select * from student");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Excel Sheet");
HSSFRow rowhead = sheet.createRow((short) 0);
rowhead.createCell((short) 0).setCellValue("Roll No");
rowhead.createCell((short) 1).setCellValue("Name");
rowhead.createCell((short) 2).setCellValue("Class");
rowhead.createCell((short) 3).setCellValue("Marks");
rowhead.createCell((short) 4).setCellValue("Grade");
int index = 1;
while (rs.next()) {
HSSFRow row = sheet.createRow((short) index);
row.createCell((short) 0).setCellValue(rs.getInt(1));
row.createCell((short) 1).setCellValue(rs.getString(2));
row.createCell((short) 2).setCellValue(rs.getString(3));
row.createCell((short) 3).setCellValue(rs.getInt(4));
row.createCell((short) 4).setCellValue(rs.getString(5));
index++;
}
FileOutputStream fileOut = new FileOutputStream("c:\\excelFile.xls");
wb.write(fileOut);
fileOut.close();
System.out.println("Data is saved in excel file.");
rs.close();
connection.close();
} catch (Exception e) {
}
}
}
This is my solution. Code to insert in the main class:
import java.io.*;
import java.sql.*;
import com.company.*;
/**
* Created by MAXNIGELNEGRO
*/
String[] filePath = new String[] {"C:\\Users\\Documents\\MyFile.csv"};
String[] driverDB = new String[] {"oracle.jdbc.driver.OracleDriver"};
String[] stringConnDB = new String[] {"jdbc:oracle:thin:@//127.0.0.1:1881/mydb"};
String[] userDB = new String[] {"pippo"};
String[] passDB = new String[] {"pluto"};
String[] charSep = new String[] {";"};
Boolean column= new Boolean (true);
String[] queryDB = new String[] {"select * FROM MYQUERY"};
try{
System.out.println("---------------File exist?------------" + filePath[0]);
File fileTemp = new File(filePath[0].toString());
if (fileTemp.exists()){
fileTemp.delete();
System.out.println("---------------DELETE FILE------------" + filePath[0] );
}
System.out.println("QUERY: ---->"+ queryDB[0].toString());
exportQueryToCsv exp = new exportQueryToCsv();
exp.exportQueryToCsv(filePath, driverDB, stringConnDB, userDB, passDB, queryDB, column, charSep);
if (fileTemp.exists()){
System.out.println("---File created---" + filePath[0]);
}
}
catch(Exception e){
e.printStackTrace();
}
The core class:
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Created by MAXNIGELNEGRO
*/
public class exportQueryToCsv {
public exportQueryToCsv(){}
public static void exportQueryToCsv (String[] filename, String[] driverDB, String[] connDB
, String[] userDB, String[] passDB, String[] queryDB, Boolean intestaFile
, String[] charSep) throws SQLException, IOException {
Statement stmt=null;
ResultSet rset=null;
Connection conn=null;
try { DBConn connessione = new DBConn();
conn=connessione.connect(driverDB[0],connDB[0],userDB[0],passDB[0]);
conn.setAutoCommit(false);
stmt = conn.createStatement();
rset = stmt.executeQuery(queryDB[0]);
ExportData2CSV csv = new ExportData2CSV();
csv.ExportData2CSV(rset,filename[0],intestaFile,charSep[0]);
csv.createFileCsv();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
finally {
if (stmt != null) {stmt.close();}
if (conn != null) {conn.close();}
if (rset != null) {rset.close();}
}
}
}
This is the class DBConn for the connection to the database:
import java.sql.*;
/**
* Created by MAXNIGELNEGRO
*/
public class DBConn {
public DBConn() {
}
public Connection connect(String driverDB, String db_connect_str, String db_userid, String db_password) {
Connection conn;
try {
Class.forName(driverDB).newInstance();
conn = DriverManager.getConnection(db_connect_str, db_userid, db_password);
} catch (Exception e) {
e.printStackTrace();
conn = null;
}
return conn;
}
}
This is the class for retrieves data from table to resultset and writes to csv file:
package com.company;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
/**
* Created by MAXNIGELNEGRO
*/
public class ExportData2CSV {
public ResultSet rset;
public String filename;
public Boolean columnName;
public String charSep;
public void ExportData2CSV(ResultSet rset, String filename, Boolean columnName, String charSep) {
this.rset = rset;
this.filename = filename;
this.columnName = columnName;
this.charSep = charSep;
}
public void createFileCsv() throws SQLException, IOException {
FileWriter cname = null;
try {
// WRITE COLUMN NAME
ResultSetMetaData rsmd = rset.getMetaData();
cname = new FileWriter(filename);
if (columnName) {
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
cname.append(rsmd.getColumnName(i));
cname.append(charSep);
cname.flush();
}
cname.append(System.getProperty("line.separator"));
}
// WRITE DATA
while (rset.next()) {
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
if (rset.getObject(i) != null) {
String data = rset.getObject(i).toString().replaceAll(charSep, "");
cname.append(data);
cname.append(charSep);
} else {
String data = "null";
cname.append(data);
cname.append(charSep);
}
}
//new line entered after each row
cname.append(System.getProperty("line.separator"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cname != null) {
cname.flush();
cname.close();
}
if (rset != null) {
rset.close();
}
}
}
}
This is the excel data sheet
input excel file containing records Hi this is the solution you need 3 files 1.input thread 2.output thread 3.data structure 4.main 1.input thread to read the excel and output thread to write the sql out put 2.data structure is to hold and transfer the data
(InputThread.java)
import java.io.*;
public class InputThread extends Thread{
String fp;
InputString is;
String tableName="emp";
String outFile;
InputThread(String FilePath,String nameOfTheTable,String outFileName){
fp=FilePath;
outFile=outFileName;
tableName=nameOfTheTable;
}
public void run(){
File file = new File(fp);
String line;
try{
BufferedReader br = new BufferedReader(new FileReader(file));
if( (line=br.readLine()) != null)
is = new InputString(line);
//transform(is);
InputString tmp = new InputString(createTable(line));
//tmp.next = is;
is = tmp;
//tmp = tmp.next;
for(; (line = br.readLine()) != null; ) {
tmp.next = new InputString(line);
tmp = tmp.next;
transform(tmp);
}
}catch(Exception e){ System.out.println("Error is :"+e); }
//traverse();
new OutputThread(is,outFile).start();
}
void transform(InputString x){
String[] arr = x.getLine().split(",");
String sql = "insert into "+tableName+" values(";
for(int i=0;i<arr.length;i++){
sql+="'"+arr[i]+"'";
if( (i+1) < arr.length) sql+=",";
}
sql+=");";
x.setLine(sql);
}
String createTable(String x){
String[] arr = x.split(",");
String sql = "create database vamsidb "+ "use vamsidb "+"create table "+tableName+"(";
for(int i=0;i<arr.length;i++){
sql+=arr[i]+" varchar(50)";
if( (i+1) < arr.length) sql+=",";
}
sql+=");";
return sql;
}
/*public void traverse(){
InputString tmp = is;
while(is != null){
System.out.println(is.getLine());
is=is.next;
}
}*/
}
(OutputThread.java)
import java.io.*;
public class OutputThread extends Thread{
InputString is;
String outFile;
OutputThread(InputString linkedList,String outFileName){
is=linkedList;
outFile = outFileName;
}
public void run(){
try{
FileOutputStream fos = new FileOutputStream(outFile);
while(is != null){
fos.write(is.getLine().getBytes());
is=is.next;
}
fos.close();
}catch(Exception e){
System.out.println("Error is :"+e);
}
}
}
(Main.java)
public class Main{
public static void main(String[] args){
InputThread it = new InputThread("sasken.csv","emp","output.sql");
it.start();
}
}
(DataStructure.java)
//This class represents the data structure to hold and transform input //data as a linked list of sql statements
class InputString{
String line;
InputString next;
InputString(String x){
line = x;
}
String getLine(){
return line;
}
void setLine(String x){
line = x;
}
}
You can use JDBC to fetch the records from DB in java and then use Apache POI for exporting the data to CSV/Excel.
Additionally, you can use the desktop API of java to send email using your default email client.
For this to work you need to work write a small code that can take up any query and any driver . The first input should be the driver name as the input to the software that you are writing. Then the software you are writing should be in a position to execute any SQL given to it and give out only rows and columns.
The next task comes to parse the ResultSet that comes from the JDBC of java application. Either you want to write the results into CSV file or EXCEL is based on how good you have the java api to do that.
Writing the output into the CVS is easy and not trival. I have not worked on exporting the data into Excel. I am sure you find jars for that.
The solution is based on a properties file.
Where this options are configured:
The process can start bis 4 threads to download 4 tables at the same time. If you want to run again, the generated files must be deleted. A text file (logs.txt) with the process data is also created.
---- PROP FILE : ExportProperties.prop --------- The solution will write a draft cinfiguration version in:
C:\tmp\test\ExportProperties.prop
## configuration properties
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@ldap://localhost
username=user
password=pass
fileExtension=_20200623.csv
columSeparator=;
charsetName=CP1252
## export only 10 rows change to false
only10Rows=true
##tables
tableName.1=USER
tableName.1.sqlWhere= user.name IS NOT NULL
tableName.2=ROLL
tableName.3=FIRMA
--------- The main file --------
public class ExportTable2CSVMain implements Runnable {
static SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss SSS|");
static final String folder = "C:\\tmp\\test\\";
static final Properties prop = getProperties();
public static void main(String[] args) {
for (int x = 1; x < 6; x++) {
try {
writieLog(0, "Start thread " + x);
new ExportTable2CSVMain();
Thread.sleep(1000);
} catch (final Exception e) {
e.printStackTrace();
}
}
}
public ExportTable2CSVMain() {
final Thread t = new Thread(this);
t.start(); // start the thread -> run
}
@Override
public void run() {
int pos = 1;
String tableName = prop.getProperty("tableName." + pos);
while (tableName != null) {
try {
export(tableName, pos++);
} catch (final Exception e) {
e.printStackTrace();
}
tableName = prop.getProperty("tableName." + pos);
}
}
private void export(String tableName, int filePos) throws Exception {
final boolean only10Rows = prop.getProperty("only10Rows", "false").equals("true");
String extraWhere = prop.getProperty("tableName."+filePos+".sqlWhere");
if(extraWhere ==null)
extraWhere = prop.getProperty("sqlWhere");
if(extraWhere ==null)
extraWhere = "";
final String sql = "select * from " + tableName + extraWhere
+ (only10Rows ? " FETCH NEXT 10 ROWS ONLY" : "");
final String fileName = folder + tableName + prop.getProperty("fileExtension");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ExportTable2CSV data2csv = null;
try {
data2csv = new ExportTable2CSV(fileName, tableName, filePos);
if (data2csv.toDo()) {
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
data2csv.createFileCsv(rs);
}
} catch (final Exception e) {
final int row = data2csv == null ? -1 : data2csv.row;
writieLog(filePos, "SQL", "Error", "rows:" + row, tableName, e.getMessage());
e.printStackTrace();
} finally {
try {
rs.close();
} catch (final Exception e) {
}
try {
stmt.close();
} catch (final Exception e) {
}
try {
conn.close();
} catch (final Exception e) {
}
}
}
public Connection getConnection() throws Exception {
Class.forName(prop.getProperty("driver"));
return DriverManager.getConnection(//
prop.getProperty("url"),
prop.getProperty("username"),
prop.getProperty("password"));
}
static private Properties getProperties() {
File file = new File(folder);
if (!file.exists()) { // if the folder do not exist create it
file.mkdirs();
}
file = new File(folder + "ExportProperties.prop");
if (!file.exists()) {
try {
final PrintWriter out = new PrintWriter(
new BufferedWriter(new FileWriter(folder + "ExportProperties.prop", true)));
out.println(//
"## configuration properties\n" +
"driver=oracle.jdbc.driver.OracleDriver\n" +
"url=jdbc:oracle:thin:@ldap://localhost\n"+
"username=USER\n" +
"password=PASSWORD\n" +
"sqlWhere=\n" +
"fileExtension=_20200619.csv\n" +
"columSeparator=;\n" +
"charsetName=CP1252\n" +
"##tables\n" +
"tableName.1=USER\n" + //
"tableName.2=ROLL\n"
);
out.close();
} catch (final Exception e) {
e.printStackTrace();
}
}
final Properties prop = new Properties();
try {
prop.load(new FileInputStream(folder + "ExportProperties.prop"));
} catch (final IOException e) {
e.printStackTrace();
}
return prop;
}
public static void writieLog(int filePos, String... txt) throws Exception {
final PrintWriter out = new PrintWriter(new BufferedWriter(new FileWriter(folder + "logs.txt", true)));
String sb = "";
sb += formatter.format(new Date()) + "\t";
sb += filePos == 0 ? "" : "F-" + filePos + "\t";
for (final String s : txt) {
sb += s + "\t";
}
System.out.println(sb);
out.println(sb);
out.close();
}
}
---------------- the ExportTable2CSV file -------
/**
* Created by Jose Manuel Davila (Mel) [email protected]
*/
public class ExportTable2CSV {
final String fileName;
final String table;
final String columSeparator;
final Boolean columnName = true;
final int filePos;
int row = 0;
int column = 0;
public ExportTable2CSV(String fileName, String table, int filePos) {
this.filePos = filePos;
this.fileName = fileName;
this.table = table;
columSeparator = ExportTable2CSVMain.prop.getProperty("columSeparator", ";");
}
public boolean toDo() throws Exception {
if (new File(fileName).exists()) {// the file exist jet return
return false;
}
writeLine("");
return true;
}
public void createFileCsv(ResultSet rs) throws Exception {
String sb = "";
try {
ExportTable2CSVMain.writieLog(filePos, "FILE", "INI ", table, fileName);
// WRITE COLUMN NAME
final ResultSetMetaData rsmd = rs.getMetaData();
sb = "";
final List<String> list = new ArrayList<String>();
if (columnName) {
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
sb += rsmd.getColumnName(i) + columSeparator;
list.add(rsmd.getColumnName(i));
}
writeLine(sb.toString());
}
// WRITE DATA
while (rs.next()) {
sb = "";
column = 0;
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
final Object obj = rs.getObject(i);
String data = "";
if (obj != null) {
if (obj instanceof String) {
data = obj.toString();
if (data.indexOf(columSeparator) != -1) {
if (data.indexOf("\"") != -1) {
data = data.replaceAll("\"", "'");
ExportTable2CSVMain.writieLog(filePos, "FILE", "WITH comm and ; ", "row:" + row,
"Column:" + list.get(column), table, fileName);
}
data = "\"" + data + "\"";
}
} else {
data = obj.toString();
}
}
sb += data + columSeparator;
column++;
}
writeLine(sb.toString());
row++;
}
ExportTable2CSVMain.writieLog(filePos, "FILE", "END ", "rows:" + row, table, fileName);
} catch (final Exception e) {
ExportTable2CSVMain.writieLog(filePos, "FILE", "Error ", "rows:" + row, table, fileName, e.getMessage());
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
}
}
}
void writeLine(String line) throws Exception {
if (row > 0 && row % 1000 == 0) {
System.out.println(filePos + " " + row + " working ...");
}
final PrintWriter cname = new PrintWriter(new BufferedWriter((new OutputStreamWriter(
new FileOutputStream(fileName, true), ExportTable2CSVMain.prop.getProperty("charsetName", "CP1252")))));
if (line.equals("")) {
cname.print(line);
} else {
cname.println(line);
}
cname.close();
}
}
--------- POM file pom.xml ----------------
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>ExportTable2CSV</groupId>
<artifactId>ExportTable2CSV</artifactId>
<version>1.1.0</version>
<name>ExportTable2CSV</name>
<properties>
<ojdbc8.version>18.3.0.0.0</ojdbc8.version>
</properties>
<dependencies>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc8</artifactId>
<version>${ojdbc8.version}</version>
<scope>runtime</scope>
</dependency>
</dependencies>
</project>
To export to a column separated with tab.
change this prop in properties file :
fileExtension=_mel.txt
columSeparator=\t
This works perfectly fine:
PreparedStatement statement = famuat.prepareStatement(sql query);
ResultSet result = statement.executeQuery();
ResultSetMetaData md = result.getMetaData();
FileWriter fw = new FileWriter(filepath);
int columnCount = md.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
columnheader = md.getColumnName(i);
// System.out.println(columnheader);
if (i > 1) System.out.print(" | ");
if (i > 1) System.out.print(", ");
if (i > 1) System.out.print("\t");
fw.append(columnheader.toUpperCase());
fw.append(",");
System.out.println(columnheader);
}
while (result.next()) {
fw.append("\r\n");
for (int i = 1; i <= columnCount; i++) {
if (i > 1) System.out.print(" | ");
if (i > 1) System.out.print(", ");
if (i > 1) System.out.print("\t");
fw.append(result.getString(i));
fw.append(",");
row = result.getString(i);
System.out.println(row);
}
}
this work for exort to csv and txt
public void ExportTxt(){
FileWriter texto = null;
ResultSet rs2 = null;
ResultSet rs = null;
try {
texto = new FileWriter("texto.txt");
PrintWriter pw = new PrintWriter(texto);
Statement consulta = conn.createStatement();
Statement consulta2 = conn.createStatement();
String query = "SELECT * FROM coches";
rs = consulta.executeQuery(query);
int i=1;
pw.println("----------------");
while (rs.next()) {
pw.println(i + ",Coche: Matricula:" + rs.getString("matricula") + ", Marca:" + rs.getString("marca") + ", Modelo:" + rs.getString("modelo"));
String query2 = "SELECT * FROM reparaciones WHERE coche = '" + rs.getString("matricula") + "'";
rs2 = consulta2.executeQuery(query2);
pw.println("----------------");
while(rs2.next()){
pw.println(rs2.getRow() + ",Reparacion: Fecha entrada:" + rs2.getString("fecha_entrada") + ", Fecha salida:" + rs2.getString("fecha_salida"));
}
i++;
}
rs.close();
rs2.close();
consulta.close();
} catch (IOException ex) {
Logger.getLogger(Conexion.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(Conexion.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
texto.close();
} catch (IOException ex) {
Logger.getLogger(Conexion.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
public void ExportCSV(){
PrintWriter pw = null;
try {
File archivo = new File("excel.csv");
pw = new PrintWriter((new FileWriter(archivo)));
Statement consulta = conn.createStatement();
String query = "SELECT * FROM clientes";
ResultSet rs = consulta.executeQuery(query);
pw.println("id;nombre;apellidos;direccion;nif;telefono");
while(rs.next()){
pw.println(rs.getInt("id") + ";" + rs.getString("nombre") + ";" + rs.getString("Apellidos") + ";" + rs.getString("direccion") + ";" + rs.getString("nif") + ";" + rs.getString("telefono"));
}
} catch (IOException ex) {
Logger.getLogger(Conexion.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(Conexion.class.getName()).log(Level.SEVERE, null, ex);
} finally {
pw.close();
}
}
Tried some of the other answers and they didn't really work and/or ask to add libraries, which I like to avoid, especially for a format as dead simple as CSV.
So here's my own take. No special library requirements (other than your SQL driver) so (mostly) vanilla Java 8 or later should work. Also works with beanshell (hence try/finally rather than try-with-resources.) All fields should be escaped correctly, assuming the junit test I created (posted) is correct, so any RFC 4180 compliant CSV reader should be able to parse it without errors and/or getting it wrong, and for the purpose of this code, that includes Microsoft Excel.
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;
import java.io.FileWriter;
// Replace this with your own SQLServerDriver class
import com.microsoft.sqlserver.jdbc.SQLServerDriver;
public class Sandbox {
/**
* Replaces null with an empty string, surrounds values that have commas or
* newlines with quotes, and if a value begins with a quote it escapes any
* quotes with another quote
*
* @param value
* @return
*/
public String csvEscape(String value) {
if (value == null) {
value = "";
} else if (value.contains(",") || value.contains("\n") || value.startsWith("\"")) {
value = "\"" + value.replace("\"", "\"\"") + "\"";
}
return value;
}
/**
* Run an SQL query and dump the output into a CSV file as we receive it
*
* @param url URL to your server in a format that the driver expects, i.e.
* "jdbc:sqlserver://server.com:1433;IntegratedSecurity=true;databaseName=foo"
* @param statement SQL statement, i.e. "SELECT foo FROM bar". Do NOT pass user
* generated strings!
* @param csvPath File path to the csv file, i.e. /foo/bar.csv
* @throws SQLException
* @throws IOException
*/
public void sqlToCsv(String url, String statement, String csvPath) throws SQLException, IOException {
Connection connection = DriverManager.getConnection(url);
Driver driverSelect = new SQLServerDriver();
DriverManager.registerDriver(driverSelect);
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(statement);
ResultSetMetaData md = rs.getMetaData();
int colCount = md.getColumnCount();
List<String> cols = new ArrayList<String>();
for (int i = 1; i <= colCount; i++) {
String value = csvEscape(md.getColumnName(i));
cols.add(value);
}
List<String> row = new ArrayList<String>();
FileWriter fileWriter = null;
try {
fileWriter = new FileWriter(csvPath, false);
fileWriter.write(String.join(",", cols) + "\n");
while (rs.next()) {
row.clear();
for (int i = 1; i <= colCount; i++) {
String value = csvEscape(rs.getString(i));
row.add(value);
}
fileWriter.write(String.join(",", row) + "\n");
}
} finally {
fileWriter.close();
}
}
@Test
public void testCsvEscape() {
assertEquals("say \"cheese!\"", csvEscape("say \"cheese!\""));
assertEquals("\"\"\"say cheese\"\"!\"", csvEscape("\"say cheese\"!"));
assertEquals("\"cheese\nplease\"", csvEscape("cheese\nplease"));
assertEquals("\"cheese, please\"", csvEscape("cheese, please"));
assertEquals("\"say \"\"cheese,\n please!\"\"\"", csvEscape("say \"cheese,\n please!\""));
assertEquals("\"\"\"say \"\"cheese,\n please!\"\"\"\"\"", csvEscape("\"say \"cheese,\n please!\"\""));
}
}
Yes!
You can connect to the different database types using jdbc and then create an Excel with the results (Seen here).
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestAccessExcel {
public static Connection getConnection() throws Exception {
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc:odbc:excelDB";
String username = "username";
String password = "pass";
Class.forName(driver);
return DriverManager.getConnection(url, username, password);
}
public static void main(String args[]) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.createStatement();
String excelQuery = "select * from [Sheet1$]";
rs = stmt.executeQuery(excelQuery);
while (rs.next()) {
System.out.println(rs.getString("BadgeNumber") + " " + rs.getString("FirstName") + " "
+ rs.getString("LastName"));
}
} catch (Exception e) {
System.err.println(e.getMessage());
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
