Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Export 1 million records from MS SQL to Excel

Posted on 2009-05-02
7
Medium Priority
?
2,258 Views
Last Modified: 2012-05-06
Hi there

Wonder if you can help me?

I have a .NET 3 tier/ASP.NET application, it has over 1 million records and the user MUST be able to export all 1 million records to excel. I know excel has a limit, in which case I guess I have to export it to a .csv file. My question is, what would be the best way to export all 1 million records? Bearning in mind this is a 3 tier application

thanks
0
Comment
Question by:mousemat24
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 6

Accepted Solution

by:
RPCIT earned 2000 total points
ID: 24288073
Here is a function I use regularly to export datagridview rows.. you should be able to modify it to also export a datareader.. or whatever you are using.

fyi.. this returns a memorystream.. so you would need to create a streamwriter.. and have it write out the memorystream..
        public static MemoryStream GetCSVStream(DataGridViewRowCollection rows)
        {
            MemoryStream ms = new MemoryStream();
            StreamWriter sw = new StreamWriter(ms);
            bool firstRow = true;
            int lineNumber = 1;
            foreach (DataGridViewRow row in rows)
            {
                if (firstRow)
                {
                    string headerLine = "\"Line\"";
                    //writeheader
                    foreach (DataGridViewCell cell in row.Cells)
                    {
                        if (cell.Visible)
                        {                        
                            headerLine += ",\"" + cell.OwningColumn.Name + "\"";
                        }
                    }
                    sw.WriteLine(headerLine);
                    firstRow = false;
                }
                string dataLine = lineNumber.ToString();
                foreach (DataGridViewCell cell in row.Cells)
                {
                    if (cell.Visible)
                    {
                        double tryHandle;
                        string field = cell.Value.ToString();
                        if (double.TryParse(field, out tryHandle))
                        {
                            dataLine += "," + field;
                        }
                        else
                        {
                            dataLine += ",\"" + field + "\"";
                        }
                    }
                }
                sw.WriteLine(dataLine);
                lineNumber++;
            }
            sw.Flush();
            return ms;
        }

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24289052
you should consider exporting to a excel file with multiple worksheets... knowing that 1M records, with 50K per sheet, will still result in 20 sheets, and excel go slow :)

or, ensure you use excel 2007, which allows such size..
0
 
LVL 4

Expert Comment

by:r0bertdenir0
ID: 24290549
Even if Excel 2007 allows that many rows - what data are you putting into that spreadsheet?
If each rows contains a decent amount of data, your file could be several hundred megs in size.
From my own experience, once Excel opens a  file above 30MB it just slows to a crawl.
I've got a hp centrino dual core with 2gb of ram - that may not be the fastest available, but most business pc's are an even lower spec.
Even with a CSV, it's not a matter of how many records, but the total amount of data that needs to be managed.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mousemat24
ID: 24299560
true, so what do you think is the best way to export the data? How can I send the file to the user?

Thanks
0
 
LVL 6

Expert Comment

by:RPCIT
ID: 24299682
a flat file csv should be something they can deal with.  that will allow them to import it easily into access, excel(2007), SQL Server.. whatever.  They can even view it in a (fairly robust, notepad will choke) text viewer.

The code I pasted above will try to format numbers as numbers, and everything else as a string. It also includes column headers and a line number.  These are things that will make it easier for them to import.
0
 
LVL 4

Expert Comment

by:r0bertdenir0
ID: 24301977
Yup, I'd agree with RPCIT - it gives yr users the most options.
Send in CSV & suggest they open it with Access or something similar that can handle that amount of data.
0
 

Author Comment

by:mousemat24
ID: 24304169
Thanks RPCIT, I'll use your code. :-)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question