Exporting large data into CSV from sqlserver using java

Question

I have 9 million records in sqlserver. I am trying to import it into csv files so that I can put that data into mongo db. I have written Java code for sql2csv import. But I have two issue

If I read all the data in list and then try to insert into CSV, I got outofmemorry exception.
If I read line by line and try to insert every line in CSV, it took very long time to export data.

My code is some thing like

 List list = new ArrayList();  
    try {
        Class.forName(driver).newInstance();
        conn = DriverManager.getConnection(url, databaseUserName, databasePassword);
        stmt =  conn.prepareStatement("select  OptimisationId  from SubReports");
        result = null;

        result =   stmt.executeQuery(); 
        //  stmt.executeQuery("select * from Subscription_OptimisationReports");
        result.setFetchSize(1000);

        while (result.next()) {
            //System.out.println("Inside while");
            SubReportsBean bean = new SubReportsBean();
            bean.setOptimisationId(result.getLong(("OptimisationId")));

            list.add(bean);
             generateExcel(list);

        }
        //generateExcel(list);  
        conn.close();
    }

Can there be a faster approach to export all data quickly? Or even better if it can directly be exported to mongo instead of csv.

You can combine your two ways by adding counter, which will write your data foreach (for example 1000) beans. Also, you can directly export your data to CSV from SQL Server Management Studio — MGorgon
– MGorgon, Commented Feb 27, 2015 at 14:12
I know I can do that directly from SQL Server Management Studio but I don't have full access to studio. So I only one option to write some code. — sangita
– sangita, Commented Feb 27, 2015 at 14:16

thelok · Accepted Answer · 2015-02-27 14:27:55Z

1

Maybe you should paginate your data by only reading a little at a time by using LIMIT and OFFSET.

select  OptimisationId  from SubReports OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;
select  OptimisationId  from SubReports OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;
select  OptimisationId  from SubReports OFFSET 2000 ROWS FETCH NEXT 1000 ROWS ONLY;
...

Just keep a counter of the offset.

Another Example

If you use this solution then you'd need to modify your code to append to the end of the Excel file -- don't keep all your results in memory otherwise you'll still run into the OutOfMemoryException.

answered Feb 27, 2015 at 14:27

thelok

836 bronze badges

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

Comments

NJ73 · Accepted Answer · 2015-02-27 16:16:20Z

1

Definitely when dealing with so much records, collecting all date in a list before dumping in to CSV is bound to fail.

So your solution 2 is the way to go.

Your code seems to correspond to this solution but I think you 've just forgotten to move your list declaration or to empty your list in the loop. You could do :

try {
    Class.forName(driver).newInstance();
    conn = DriverManager.getConnection(url, databaseUserName, databasePassword);
    stmt =  conn.prepareStatement("select  OptimisationId  from SubReports");
    result = null;

    result =   stmt.executeQuery(); 
    //  stmt.executeQuery("select * from Subscription_OptimisationReports");
    result.setFetchSize(1000);

    while (result.next()) {
        //System.out.println("Inside while");
        SubReportsBean bean = new SubReportsBean();
        bean.setOptimisationId(result.getLong(("OptimisationId")));
        List list = new ArrayList();  
        list.add(bean);
         generateExcel(list);

    }
    //generateExcel(list);  
    conn.close();
}

answered Feb 27, 2015 at 16:16

NJ73

861 bronze badge

2 Comments

NJ73 Over a year ago

Also be sure to use a BufferedWriter in your csv writing code

Muneeb Mirza Over a year ago

is it going to be only 1 record in the list, then what is the purpose of this list?

Collectives™ on Stack Overflow

Exporting large data into CSV from sqlserver using java

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related