Click here to Skip to main content
15,881,866 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
i have 2,000,000 record in my table and excel don't accept more than 1,040,000 record what should i do ?????????
i use this code

C#
<pre>public void DumpTableToFile(SqlConnection connection, string tableName, string destinationFile)
        {
            using (var command = new SqlCommand("select * from tabl", connection))
            {
                using (var reader = command.ExecuteReader())
                {
                    using (StreamWriter sw = new StreamWriter(File.Open(destinationFile, FileMode.Create), Encoding.UTF8))
                    {
                        string[] columnNames = GetColumnNames(reader).ToArray();
                        int numFields = columnNames.Length;
                        sw.WriteLine(string.Join(",", columnNames)); // writing headers  

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                string[] columnValues = Enumerable.Range(0, numFields)
                                      .Select(i => reader.GetValue(i).ToString())
                                      .Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
                                      .ToArray();
                                sw.WriteLine(string.Join(",", columnValues));
                            }
                        }
                    }
                }
            }
        }

        private IEnumerable<string> GetColumnNames(IDataReader reader)
        {
            foreach (DataRow row in reader.GetSchemaTable().Rows)
            {
                yield return (string)row["ColumnName"];
            }
        }

        private void Button5_Click(object sender, EventArgs e)
        {
            SqlConnection _connection = new SqlConnection();
            SqlDataAdapter _dataAdapter = new SqlDataAdapter();
            SqlCommand _command = new SqlCommand();
            DataTable _dataTable = new DataTable();

            _connection = new SqlConnection();
            _dataAdapter = new SqlDataAdapter();
            _command = new SqlCommand();
            _dataTable = new DataTable();

            //dbk is my database name that you can change it to your database name
            _connection.ConnectionString = "connaction";
            _connection.Open();

            SaveFileDialog saveFileDialogCSV = new SaveFileDialog();
            saveFileDialogCSV.InitialDirectory = Application.ExecutablePath.ToString();

            saveFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*";
            saveFileDialogCSV.FilterIndex = 1;
            saveFileDialogCSV.RestoreDirectory = true;

            string path_csv = "";
            if (saveFileDialogCSV.ShowDialog() == DialogResult.OK)
            {
               
                // Runs the export operation if the given filenam is valid.
                path_csv = saveFileDialogCSV.FileName.ToString();
            }

           
            DumpTableToFile(_connection, "tbl_trmc", path_csv);

        }
    }


What I have tried:

i search about it bout didn't find any thing to help me
Posted
Updated 16-Jan-19 10:58am
Comments
Richard MacCutchan 20-Dec-18 8:29am    
The obvious answer is: do not export so many records. Do not assume that one application will have the same capacity as another.
[no name] 20-Dec-18 15:49pm    
And what is Excel going to do for you with those "2,000,000" records?

"Business Intelligence" (BI) requires intelligence.

Look at (MS) BI for the desktop.

The bigger question is why are you putting all of this in an Excel workbook? It'll be practically unmanageable and unusable. If you're going to be manipulating data in Excel, the proper way to deal with such large data sets would be to have Excel connect to the database and grab the data it needs instead of dumping all the data into Excel.

As for your question, nobody can answer that. It's entirely up to your business rules what you do with such a massive quantity of data stored in Excel workbooks.
 
Share this answer
 
CSV is a text file format it is not an Excel file (although Excel can read it).

CSV files have no limitations and can be as large as you need.

Excel has a limit of 1 million rows per sheet.

1) Seriously reconsider importing large data into Excel by grouping and filtering your queries in SQL beforehand.
2) If you really need to use Excel consider using Power Pivot which can handle large number of rows and read CSV files.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900