title: How to read large excel files containing more that 200,000 rows and load that data in datatable in C#
body: <p>I am working on reading data from excel and loading it in datatable. My problem is that it is giving SystemOutOfMemoryException while loading large excel files.
  The colomns in excel are not fixed so I can't load that data in sql table.
  I need to do some manipulation on data so I am loading it in datatable.
  Can anyone suggest me how to resolve this issue?</p>
  
  <p>I am doing it like this </p>
  
  <pre><code> OleDbConnection conn = new OleDbConnection();
                  OleDbCommand cmd = new OleDbCommand();
                  OleDbDataAdapter da = new OleDbDataAdapter();
                  conn = new OleDbConnection(GetOleDbConnectionString(strFileType, strNewPath));
                  if (conn.State == ConnectionState.Closed) conn.Open();    
                  string query = null;
                  DataTable dt = new DataTable();              
  
                  query = "SELECT  * FROM [" + SpreadSheetName + "]";               
  
                  cmd.Connection = conn;
                  cmd.CommandText = query;
                  da.SelectCommand = cmd;
                  da.Fill(dt);
                  da.Dispose();
                  conn.Close();
                  conn.Dispose();
  </code></pre>