c# Memory exception at merge

Question

Updated by new case:

Hi, I am having a huge excel file want to import to SQL DB.

for each (DataRow drSheet in dtsheet.Rows)
{
  sheetname = drsheet ["TABLE_NAME"].ToString();

  OleDBCommand ocon = new OleDBCommand("select * from [" + sheetname  + "]", cnn);
  OleDataAdapter adp = new OleDataAdapter (ocon);
  DataSet ds = new DataSet();
  adp.Fill(ds); //Getting an error

---

Hi, I'm getting out of memory exception at merge. I have excel file having 150 columns with 1 million records. Here is my code. Please suggest me what to do?

Datatable dt = new DataTable();
dt.Columns.Add("Empid");
dt.Columns.Add("Empfname");
dt.Columns.Add("Emplname");
dt.Columns.Add("Empid").DefaultValue = 1;
dt.Columns.Add("Empfname") DefaultValue = "test";
dt.Columns.Add("Emplname").DefaultValue = "test2";
for(int = 1 < i < xlcolcnt; i++)
{
	xlData.Columns[i].Columname = "Column" + i;
}
dt.Merge(xlData, true, MissingSchemaAction, Add); //---getting error out of memory here 
int xlcocnt = dt.columns.count;
DataTable dt1 = new DataTable();
int xlcocnt = dt.columns.count;
int diffcnt = dbcont - xlcocnt;
for(int i = xlcocnt; i++)
{
	dt1.columns.Add("Column" + i + 1)
}
dt.Merge(dt1, true, MissingSchemaAction, Add);
using(SqlBulkCopy dtbc = new SqlBulkCopy(adoconn)
		{
			dtbc.BulkCopyTimeout = 180;
			dtbc.DestinationTableName = "Emp";
			dtbc.WriteToServer(dt);
		}

Answer 1

Probably the best approach would be to store your very large excel table in a Sql database, creating a SQL TABLE and adding the three datatable dt columns also. A Sql database table can easily manage 153 columns and one (or more) millions of records...

Or even easier, You can use ADO.NET with ODBC Excel Driver to access directly to your Excel file (.xls) and You can use a SQL Command to ALTER TABLE and add another three columns to your one-million of records, in one shot.

Answer 2

Hi, I found a very simple way to insert three columns in your excel sheet:

using Microsoft.Office.Interop.Excel;
using System.Reflection;


namespace Excel_Add_Cells
{
    internal class Program
    {
        static void Main(string[] args)
        {            
            Application xlApp = new Application();
            Workbook xlWorkBook = xlApp.Workbooks.Open(@"yourfilename");
            Worksheet sheet = xlWorkBook.Sheets[1];            

            Range rng = sheet.get_Range("A1", Missing.Value);
            rng.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight,XlInsertFormatOrigin.xlFormatFromRightOrBelow);
            sheet.Cells[1, 1] = "Emplname";

            rng = sheet.get_Range("A1", Missing.Value);
            rng.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromRightOrBelow);            
            sheet.Cells[1, 1] = "Empfname";

            rng = sheet.get_Range("A1", Missing.Value);
            rng.EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
            sheet.Cells[1, 1] = "Empid";
            
            xlWorkBook.SaveAs(@"D:\Test.xlsx");
            xlWorkBook.Close();
        }
    }
}