0

I am tryig to export my database result set to csv file , here I am trying it as something like this , I am getting the data but the format is not correct can someone help me to resolve this

import com.opencsv.CSVWriter;

public class DbToCSV {

    @SuppressWarnings("deprecation")
    public static String RetrieveData(String filename) throws Exception {

        String readFile = readFile(filename);

        String sql = readFile;
        System.out.println(sql);
        PreparedStatement statement;

        DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        String mysqlUrl = "jdbc:mysql://localhost:3306/employee";
        Connection con = DriverManager.getConnection(mysqlUrl, "root", "root");
        statement = con.prepareStatement(sql);
        ResultSet rs = statement.executeQuery();
        System.out.println("Process Started");
        String file = filename + "/elastic1.csv";
        System.out.println(file);
        CSVWriter writer = new CSVWriter(new FileWriter(file));

        ResultSetMetaData Mdata = rs.getMetaData();
        String line1[] = { "id", "id_name", "name", "attribute_name", "values" };
        writer.writeNext(line1);
        String data[] = new String[5];
        while (rs.next()) {
            data[0] = new Integer(rs.getInt("id")).toString();
            data[1] = new Integer(rs.getInt("id_name")).toString();
            data[2] = rs.getString("name");
            data[3] = rs.getString("values");
            data[4] = rs.getString("data_part");
            writer.writeNext(data);
        }
        writer.flush();
        System.out.println("Data entered");
        return file;
    }

    public static void main(String[] args) throws Exception {
        RetrieveData("/home/Pictures");
    }
}
Improve this question
1

2 Answers 2

Reset to default
1

There is a version of writeNext() that takes a boolean which controls whether the quotes should be applied to all values, or just those that need to be quoted. The one you are using quotes all values.

instead of:

writer.writeNext(data);

use:

writer.writeNext(data, false);

See: http://opencsv.sourceforge.net/apidocs/com/opencsv/ICSVWriter.html#writeNext-java.lang.String:A-boolean-

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

1 Comment

getting same result @vmallet
0

You can use the overloaded method for writeNext which accepts boolean as a parameter.

void writeNext(String[] nextLine, boolean applyQuotesToAll)

In this pass the boolean as false to ignore the quotes.

[EDIT]

You can also use the constructor to disable the quotes.

CSVWriter writer = new CSVWriter(outputfile, ',',
                                 CSVWriter.NO_QUOTE_CHARACTER,
                                 CSVWriter.DEFAULT_ESCAPE_CHARACTER,
                                 CSVWriter.DEFAULT_LINE_END);

http://opencsv.sourceforge.net/apidocs/com/opencsv/ICSVWriter.html#writeNext-java.lang.String:A-boolean-

Improve this answer

2 Comments

Can you update the code snippet in the question?
And if that doesn't work try with this constructor CSVWriter writer = new CSVWriter(outputfile, ',', CSVWriter.NO_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER, CSVWriter.DEFAULT_LINE_END);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.