Memory problem while fetching 25 lakhs record from Mysql and importing to CSV file

Question

0.00/5 (No votes)

See more:

Hi
Iam fetching 25 lakh (250,000) records from my sql and the records have 5 columns in the table..
the fetched records are imported to CSV file...
for 6 or 7 lakhs records its fetching and exported to csv file..

but for more than 20 lakhs records its throwing the system out of memory exception..

im using this code to export the records to CSV...

C#

public static void ExportDataTableToCSV(DataTable dt, string FileName, string Heading, HttpResponse objResponse)
      {
          System.Text.StringBuilder sb = new System.Text.StringBuilder();
          sb.AppendLine(Heading);
          string[] columnNames = dt.Columns.Cast<DataColumn>().
                                            Select(column => column.ColumnName).
                                            ToArray();
          sb.AppendLine(string.Join(",", columnNames));

          foreach (DataRow row in dt.Rows)
          {
              string[] fields = row.ItemArray.Select(field => field.ToString().Replace(',',' ')).
                                              ToArray();
              sb.AppendLine(string.Join(",", fields));
          }
          objResponse.Write(sb.ToString());
          objResponse.ContentType = "application/CSV";
          objResponse.AddHeader("content-disposition", "attachment; filename="+FileName+".CSV");
          objResponse.Flush();
          objResponse.End();
      }

My issue is that im not able to fetch huge amount of records like 25 lakhs from mysql and

pls give me any other techniques to export the fetched records...

Is there any option to fill the data using json techniques or any other techniques..pls suggest me some answers...

Im importing 90 MB data from database...

Posted 23-Jan-14 20:12pm

Member 9700867

Updated 7-Dec-16 1:10am

v4

Add a Solution

Comments

BillWoodruff 24-Jan-14 2:25am

How much memory have you got ?

Member 9700867 24-Jan-14 2:27am

90 MB...

Member 9700867 24-Jan-14 2:37am

90 MB and 25 lakhs record...

Mohibur Rashid 24-Jan-14 20:58pm

In English there is no such word as Lakhs. You meant to say 2500,000

Hrishikesh 7-Dec-16 7:18am

JUST FYI:
https://en.oxforddictionaries.com/definition/lakh

[no name] 7-Dec-16 7:21am

Just FYI yourself. A translation of an Indian word to English doesn't make it an English word. Lakh is Indian not English.

Hrishikesh 7-Dec-16 7:40am

The word has been added to the dictionary using Sanskrit language as the translation reference, whose presence in the oxford dictionary makes it an English word. English language has most of its language derived from greek and latin. So my friend English is an evolving language. Apart from that, the guidelines by the website are pretty clear that not everybody's primary language is English, so the gentleman who is trying to correct his language must try and help him with the issue than correct others language.

Mohibur Rashid 8-Dec-16 0:54am

I am aware of these information, but in practice, very few people knows this particular word. You will have to understand, I am not neither insult him nor try to teach English language, I am not good at this either. He needed help, he might use the proper word that would be easily decipherable by others. No insult intended.
Also please note, this is an old thread and an old comment.

Richard MacCutchan 7-Dec-16 7:28am

Have you ever heard of the laws of physics? You cannot just read any amount of data into memory and expect your application to run without problems. Break it up into manageable pieces and process small amounts at a time.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BillWoodruff · Answer 1 · 2014-01-24T01:04:00

Solution 2

Use a StreamWriter in a 'using block, and write each row to the CSV file [^] rather than trying to fill the StringBuilder with all the rows data.

Posted 24-Jan-14 1:04am

BillWoodruff

E.F. Nijboer · Answer 2 · 2014-01-23T22:47:00

Solution 1

Why use a StringBuilder?

Instead of

sb.AppendLine(string.Join(",", fields));

Write to the response directly:

objResponse.Write(string.Join(",", fields));

Good luck!

Posted 23-Jan-14 22:47pm

E.F. Nijboer

Comments

Member 9700867 24-Jan-14 6:59am

hi E.F. Nijboer...im exporting 25 lakhs records..but when i use this code only last record is exporting in the excel..thats y i used string builder..

E.F. Nijboer 24-Jan-14 11:35am

Maybe you need to call AddHeader and ContentType first. You also don't need to use End explicitly because it is only implemented for classic asp compatibility.

E.F. Nijboer 24-Jan-14 11:40am

You might need to use OutputStream.Write. Check this link for more info:

http://msdn.microsoft.com/en-us/library/system.web.httpresponse.outputstream%28v=vs.110%29.aspx