The problem of uploading to memory at once

[patrick]

Hello

In C#.NET VS2019, I Write a query using oled.
Here, The problem of uploading to memory at once.
Problem code ( The problem of uploading to memory at once. )

---

string connectionString = "";

        OleDbCommand cmd = null;

        OleDbConnection objCon = null;

        OleDbDataAdapter objDA = null;





private string collect_Con(string szFileExtension, string szPath)
        {
                    connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=

                                        " + szPath + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";

               return connectionString;

        }



        private void cmdReadExcel_Click(object sender, EventArgs e)

        {

            DataSet objDS = new DataSet();



            string szConStr = collect_Con(System.IO.Path.GetExtension(txtFilePath.Text), txtFilePath.Text);





            try

            {

                objCon = new OleDbConnection(szConStr);

                objCon.Open();

                objDA = new System.Data.OleDb.OleDbDataAdapter("select *from [Sheet1$]", objCon);

                DataTable objDTExcel = new DataTable();

                objDA.Fill(objDTExcel);

                dataGridView1.DataSource = objDTExcel;

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);            }

            finally

            {

                if (objDA != null)

                {

                    objDA.Dispose();

                    objDA = null;

                }



                if (objCon != null)

                {

                    objCon.Close();

                    objCon.Dispose();

                    objCon = null;

                }

            }

        }

 

[Skydiver]

If you read the data grid view documentation, you can put it into virtual mode where it will query you for rows when it needs it.

That means you need to abandon using the data table class because that is what actually loads everything into memory. You will now have to figure out how to read just the rows needed from the Excel file.

But that also means you may need to stop using that ACE driver because what it does is open the .XLSX file, decompresses it into memory, and then parses the XML data that was decompressed in the previous step, and ends up allocating even more memory to hold that parsed data. You need to judiciously access the parsed data so that you only ask for what you need. Unfortunately, given the way that ACE driver works, you'll need to load the data into memory to parse it. Fortunately, you can get a row at at time and just discard it, until you get to the rows that you are interested in, and keep those in a list, and then stop reading when you don't need the rest.

 

[Skydiver]

Can your data come in .CSV instead of .XLSX? If yes, then you have much simpler options to implement the virtual rows for the data grid view. If no, can you take the time and disk space hit to dump the .XLSX data into a temporary .CSV at runtime?

 

[Skydiver]

You do not need to use OLEDB to read .CSV files. You can use the TextFieldParser.

The TextFieldParser let's you read one row at a time. You can decide to keep that row in memory by copying the fields elsewhere, or you can move on to the next row.