JAVA Code to print SQL Server to CSV returns with incomplete data

Question

I have a JAVA code to out put SQL Server Table to CSV. The printing is stopping in the middle and I am getting only partial data as CSV output. Below my code.

public class SQLServerConnection {

    public static void main(String[] args) throws IOException, SQLException {
        // TODO Auto-generated method stub

        Connection conn = null;
        SQLConnection cnn = new SQLConnection();
        FileWriter fw = new FileWriter(cnn.fileName);

        try {conn = DriverManager.getConnection(cnn.dbURL);

        if (conn != null) {
            DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
        }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } 

        String sql = String.format(cnn.Query);
        assert conn != null;

        PreparedStatement preStatement;
        preStatement = conn.prepareStatement(sql);
        ResultSet result = preStatement.executeQuery();
        ResultSetMetaData rmsd = result.getMetaData();
        int Columncount = rmsd.getColumnCount();

      //Get the column name and print the column name
        for (int iterator=1; iterator<= Columncount; iterator++) {
            fw.append(rmsd.getColumnName(iterator)+",");
        }
        fw.append('\n'); 

        while(result.next()){
            try {
                for (int jterator=1; jterator<=Columncount; jterator++){
                    fw.append(result.getString(jterator));
                    fw.append(',');
                }
                fw.append('\n');
             } catch (SQLException e)
            {
                e.printStackTrace();
            }}
        conn.close();
    }}

Class for DB and Excel Parameters

public class SQLConnection {
    public String ServerName = "Server1";
    public String DBName = "DB1";
    public String FileLoc = "Location of the file";
    public String dbURL = "jdbc:sqlserver://"+ServerName+";databaseName="+DBName+";integratedSecurity=true";
    public String Query = "SELECT * from [scdHSBCHK]";
    public String fileName = (FileLoc + DBName +"_QueryResult.csv"); 
}

The actual db returns 57 records but the csv returns only 29. I tried with different db names as well same issue though. When I output the results in the program window, the data is displaying correctly.

Yes. Did a debug and found that the issue is with the file writer as the data coming correct when I did a print within eclipse. After that I couldn't progress on anything. — Rakesh133
– Rakesh133, Commented Aug 15, 2018 at 16:16
Google for "try-with-resources", learn what it's about, and use it for all the resources that must be closed: Connection, PreparedStatement, and of course, FileWriter. Also, use a BufferedWriter as explained in the Java IO tutorial. Writing with a FileWriter like this is slooow. — JB Nizet
– JB Nizet, Commented Aug 15, 2018 at 16:23
Note that Windows users would expect a CSV file to have "\r\n" line endings. To make it platform-independent, you should use System.lineSeparator() instead of '\n' or use BufferedWriter.newLine(). — DodgyCodeException
– DodgyCodeException, Commented Aug 15, 2018 at 16:43

gil.fernandes · Accepted Answer · 2018-08-15 16:49:28Z

2

I would make sure that you close the FileWriter. This will ensure that all buffers are flushed to the file. Make sure that all Closeable resources are closed (Connection, PreparedStatement, ResultSet, FileWriter), by using try with resources.

My suggested implementation would be something like:

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.*;

public class SQLServerConnection {

    private static final String LINE_SEP = System.lineSeparator();

    public static void main(String[] args) throws IOException {
        SQLConnection cnn = new SQLConnection();
        try (Connection conn = DriverManager.getConnection(cnn.dbURL, cnn.user, cnn.pass);
             PreparedStatement preStatement = conn.prepareStatement(cnn.Query);
             ResultSet result = preStatement.executeQuery();
             Writer fw = new BufferedWriter(new FileWriter(cnn.fileName))) {
            ResultSetMetaData rmsd = result.getMetaData();
            int columnCount = rmsd.getColumnCount();
            //Get the column name and print the column name
            for (int iterator = 1; iterator <= columnCount; iterator++) {
                fw.append(rmsd.getColumnName(iterator)).append(",");
            }
            fw.append(LINE_SEP);
            while (result.next()) {
                try {
                    for (int jterator = 1; jterator <= columnCount; jterator++) {
                        fw.append(result.getString(jterator)).append(',');
                    }
                    fw.append(LINE_SEP);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

}

edited Aug 15, 2018 at 16:49

answered Aug 15, 2018 at 16:28

gil.fernandes

14.8k7 gold badges78 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DodgyCodeException Over a year ago

Closing the FileWriter also implicitly flushes it. Also the Class.forName call is unnecessary. It was only needed in versions of JDBC drivers that were written 10-20 years ago.

Rakesh133 Over a year ago

Excellent, I am getting the correct data after closing the File Writer. Thanks @DodgyCodeException

Collectives™ on Stack Overflow

JAVA Code to print SQL Server to CSV returns with incomplete data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related