Dump records from Database to file in CSV format

Question

I am reading from Database using Jooq and dumping the records in File. I want to dump the records in CSV format and new line between each records. I looked into Jooq Docs and researched on Google but didn't find any help. Jooq support CSV format but how do I incorporate the feature in my use case.

I am using the following code to dump the record in File:

SelectQuery<Record> query = getDslContext().selectQuery();       

        try {

            @Cleanup FileOutputStream fileOutputStream = new FileOutputStream(FILE);
            @Cleanup GZIPOutputStream gzipOutputStream = new GZIPOutputStream(fileOutputStream);
            @Cleanup OutputStreamWriter outputStreamWriter = new OutputStreamWriter(gzipOutputStream, charsetName);
            @Cleanup BufferedWriter writer = new BufferedWriter(outputStreamWriter);
            @Cleanup Cursor<Record> cursor = query.fetchSize(MAX_FETCH_SIZE).fetchLazy();
            while(cursor.hasNext()){
                Result<Record> results = cursor.fetchNext(MAX_FETCH_SIZE);
                for (Record record : results){
                    writer.write(record.formatJSON());
                    writer.newLine();
                }
            }

Lukas Eder · Accepted Answer · 2018-08-21 07:43:37Z

1

Use the CSVFormat.newline() property:

String csv = results.formatCSV(new CSVFormat().newline("\n\n"));

Alternatively, since you're iterating your cursor on a row subset basis, you might want to remove the headers, too:

String csv = results.formatCSV(new CSVFormat().newline("\n\n").header(false));

This will also produce two trailing newlines at the end, which you can easily remove if they're not desired:

csv = csv.trim();

answered Aug 21, 2018 at 7:43

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

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

Comments

wallek876 · Accepted Answer · 2018-08-20 15:06:49Z

You can format the records directly from Result<Record> results without needing to manually iterate over each record.

The only thing is that since you are fetching the records in batches of MAX_FETCH_SIZE, the formatted csv of each batch will contain the headers, you probably will need to only write the headers in the first batch.

@Cleanup Cursor<Record> cursor = query.fetchSize(2).fetchLazy();

boolean includeHeaders = true;
int dataStartIndex = 0;

while(cursor.hasNext()){

    Result<Record> results = cursor.fetchNext(2);
    String csv = results.formatCSV();

    if (includeHeaders) {
        writer.write(csv);
        dataStartIndex = csv.indexOf('\n') + 1;
        includeHeaders = false;
        continue;
    }

    writer.write(csv.substring(dataStartIndex));
}

Collectives™ on Stack Overflow

Dump records from Database to file in CSV format

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related