Export large data from DB to CSV

Question

I need to export a Table into a CSV File and serve it to download via my JSF/Icefaces Webapplication.

How can I do that? I have a Table with 20+ Columns and over 10 MIO rows.

At the moment, I use a Java Thread, loading all data into ram. Then I create a new File and iterate the Collection writing row for row into the file. If the Thread is done, the user can download the large file via Servlet.

But I dont want to write so many GB into ram. I cant secure, not to get a memory problem..

Is it possible that hibernate does it for me? Or does somebody has an other idea?

Im connected to a DB2 Datebase. The table I want to export is connected to a hibernate bean but it is also possible to write native sql.

Thank you for response!

Brian Agnew · Accepted Answer · 2012-10-12 11:40:02Z

3

Do you need the intermediate stage of a file ? Have you tried loading from the database and writing to your servlet output stream for each row ? That way you're acting simply as a pipe between the client and the db.

Simply set your content-disposition header appropriately and that will signal the client's browser to treat the incoming data as a CSV file itself.

answered Oct 12, 2012 at 11:40

Brian Agnew

273k38 gold badges342 silver badges443 bronze badges

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

3 Comments

Nils Over a year ago

That`s a nice Idea. Maybe reading with an interval of 50000 datasets directly writing to response? But then I need to lock the table to prevent manipulating data?

Brian Agnew Over a year ago

I would expect a simple select * to be transactional. ie.. you're not going to see changes under you.

Nils Over a year ago

Sounds good, I think loading data with more steps is what I need. I will serve a Servlet to download the data directly and I will serve the option to save the data to a file and download the file. Then I can serve both Methods for the User. Thank you!

Rohit Chemburkar · Accepted Answer · 2015-03-10 15:29:37Z

1

I have also gone through with similar kind of problem , the way I solve the problem is that I initially write a CSV file on disc and fetch 25K batch records from DB ans save to the file, and iteratively repeat the process until all the data required by the report is not written on the file. And then send the file URL to the client to download the file.

edited Mar 10, 2015 at 15:29

Rohit Chemburkar

1,0498 silver badges13 bronze badges

answered Oct 12, 2012 at 11:41

zaffargachal

8107 silver badges22 bronze badges

1 Comment

Nils Over a year ago

The Hibernate Batcher seems to be a rly nice option. I still use it for importing large data :) But Hibernate still has no build-in option for export to a file? With MySQL I easy could use native "LOAD FROM FILE"-Method. The DB2 Driver dont seem to support a solution like this. Thank you!

Collectives™ on Stack Overflow

Export large data from DB to CSV

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related