none
Import Excel Spreadsheet Data into SQL Server Database Table

    Question

  • Hi All,

    I am trying to import data from an Excel spreadsheet into a SQL server db table though having some issues. This is the code I'm using:

    private void Button1_Click(object sender, System.EventArgs e)
    {
    // Connection String to Excel Workbook
    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

    // Create Connection to Excel Workbook
    using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
    {
    //OleDbCommand command = new OleDbCommand ("Select ID,Data FROM [Data$]", connection);
    connection.Open();
    OleDbCommand command =
    new OleDbCommand ("Select ID,Data FROM [Data$]", connection);
    // Create DbDataReader to Data Worksheet
    using (DbDataReader dr = command.ExecuteReader())
    {
    // SQL Server Connection String
    string sqlConnectionString = "Data Source=.; Initial Catalog=Test;Integrated Security=True";

    // Bulk Copy to SQL Server
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
    {
    bulkCopy.DestinationTableName = "ExcelData";
    bulkCopy.WriteToServer(dr);
    }
    }
    connection.Close();
    }
    }

    With this code I get several errors, the main two being DbDataReader & SqlBulkCopy. The error mentions that I may be missing an assembly reference though I am using the correct references according to my search on msdn. These are my assembly refs:

    using System.Data.OleDb;
    using System.Data.SqlClient;
    using System.Data.Common;

    Anyone any idea whats wrong here or if there is a better way to do this taks please let me know.

    Thanks.

    Monday, February 05, 2007 9:43 AM

All replies

  •  

    What are the errors you are getting while trying executing your code?

    Monday, February 05, 2007 10:03 AM
  • Ok fixed the error with DbDataReader, though still getting the error with SQLBulkCopy.

    This is the error i am getting:

    Cannot implicitly convert type '<error>' to 'System.IDisposable'

    Monday, February 05, 2007 10:06 AM
  •  

    try adding one more assembly ref

    using System.Data;

    Monday, February 05, 2007 10:14 AM
  • Added System.Data but still no joy.

    The SqlBulkCopy section appears to be the only issue now.

    Monday, February 05, 2007 10:25 AM
  •  

    have you tried it executing without using block?

    Monday, February 05, 2007 11:33 AM
  • I'm not too sure what you mean by 'Block'
    Monday, February 05, 2007 11:42 AM
  •  

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
    {
    bulkCopy.DestinationTableName = "ExcelData";
    bulkCopy.WriteToServer(dr);
    }

    I mean, writing above code without using "using".

    Following is using block.

    using(....)
    {

    }

     

     

     


     

    Monday, February 05, 2007 11:57 AM
  • I ran into this also.  Check your version of Visual Studio.  SqlBulkInsert seems to only be available in 2005.
    • Proposed as answer by Cat2000 Tuesday, July 08, 2008 6:41 PM
    Thursday, June 26, 2008 4:25 PM
  • connection.Open();
    OleDbCommand command =
    new OleDbCommand ("Select ID,Data FROM [Data$]", connection);

    ==> Could not find installable ISAM.
    Wednesday, August 19, 2009 5:11 PM
  • Hi,

    you can do this with Excel C# application that would be used for importing Excel to database.

    While developing Visual Basic application you would use Visual Studio designer to automatically (with drag & drop) generate typed DataSet and DataAdapters from your database.

    Then you could use this Excel C# / VB.NET library to import Excel to DataSet.

    Here is a sample Excel C# code:

    var ef = new ExcelFile();
    ef.LoadXls("DataSet.xls");
    
    foreach (ExcelWorksheet ws in ef.Worksheets)
    {
      var dataTable = dataSet.Tables.Add(ws.Name);
    
      // Create columns from first row cells.
    
      // If excel cell value needs conversion, do it here.
      ws.ExtractDataEvent += (sender, e) =>
      {
        if (e.ErrorID == ExtractDataError.WrongType)
        {
          // e.DataTableValue = parse e.ExcelValue
          e.Action = ExtractDataEventAction.Continue;
        }
      };
    
      ws.ExtractToDataTable(dataTable, ws.GetUsedCellRange().Height, ExtractDataOptions.None, ws.Rows[0], ws.Columns[0]);
    }
    

    After that you only need to update your database from DataSet using DataAdapters.

    Wednesday, May 04, 2011 10:15 AM