locked
Upload Excel Sheet Data to the SQL Server

    Frage

  • Hi All,
     
    I have a requirement to upload excel data to SQL Server table in 32bit and 64bit machines environment using asp.net with c#. In the production server we need to install MS Office to perform those operations. Please do the need full. Thanks in an advance....
     
    Regards,
    Jeevan.
    Dienstag, 6. März 2012 16:01

Antworten

  • Hi Jeevan,

    Welcome to the MSDN forum!

    You may consider about using SqlBulkCopy class.

        string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";
        
        using (OleDbConnection connection = new OleDbConnection(xConnStr))
        {
    
          OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
          connection.Open();
    
          // Create DbDataReader to Data Worksheet
    
          using (DbDataReader dr = command.ExecuteReader())
          {
    
            // SQL Server Connection String
    
            string sqlConnectionString =DataAccess_Perf.GetConnectionString() ;
           // Bulk Copy to SQL Server
    
           using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
           {
    
             bulkCopy.DestinationTableName = "LIB_SQUIRREL_user.ExcelTest";
             bulkCopy.WriteToServer(dr);
    
           }
         }
        }

    Reference from: 

    Import Excel File into SQL server 2005 in ASP.NET using c# ??
    http://forums.asp.net/t/1336761.aspx/1 

    You may refer to the following links as well:

    Import Data from Excel to SQL Server 
    http://www.codeproject.com/Articles/32581/Import-Data-from-Excel-to-SQL-Server 

    "This article mainly describes how to import data from an Excel sheet to the SQL Server, viewing the data from SQL Server using the Gridview control, and deleting data. In case the Excel sheet does not contain data (null values), those values are replaced by zero" 

    How to import data from Excel to SQL Server
    http://support.microsoft.com/kb/321686 

    "This step-by-step article demonstrates how to import data from Microsoft Excel worksheets into Microsoft SQL Server databases by using a variety of methods." 

    Meanwhile, for ASP.NET issue, you may try the dedicated asp.net forum where you can contact more experts on asp.net:

    http://forums.asp.net 

    Thanks for your understanding and have a nice day!

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us



    Freitag, 9. März 2012 04:59
    Moderator

Alle Antworten

  • Hi,

    does this table alredy have columns? Which excel file it is (what is the file`s extension, xls, or csv)?


    Mitja

    Dienstag, 6. März 2012 17:28
  • Hi,

    The table already has columns, when user upload excel file those records will be inserted into the db table.  Extension of upload file .xls or .xlsx.

    Regards,

    Jeevan.

    Mittwoch, 7. März 2012 04:23
  • Hi Jeevan,

    Welcome to the MSDN forum!

    You may consider about using SqlBulkCopy class.

        string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";
        
        using (OleDbConnection connection = new OleDbConnection(xConnStr))
        {
    
          OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
          connection.Open();
    
          // Create DbDataReader to Data Worksheet
    
          using (DbDataReader dr = command.ExecuteReader())
          {
    
            // SQL Server Connection String
    
            string sqlConnectionString =DataAccess_Perf.GetConnectionString() ;
           // Bulk Copy to SQL Server
    
           using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
           {
    
             bulkCopy.DestinationTableName = "LIB_SQUIRREL_user.ExcelTest";
             bulkCopy.WriteToServer(dr);
    
           }
         }
        }

    Reference from: 

    Import Excel File into SQL server 2005 in ASP.NET using c# ??
    http://forums.asp.net/t/1336761.aspx/1 

    You may refer to the following links as well:

    Import Data from Excel to SQL Server 
    http://www.codeproject.com/Articles/32581/Import-Data-from-Excel-to-SQL-Server 

    "This article mainly describes how to import data from an Excel sheet to the SQL Server, viewing the data from SQL Server using the Gridview control, and deleting data. In case the Excel sheet does not contain data (null values), those values are replaced by zero" 

    How to import data from Excel to SQL Server
    http://support.microsoft.com/kb/321686 

    "This step-by-step article demonstrates how to import data from Microsoft Excel worksheets into Microsoft SQL Server databases by using a variety of methods." 

    Meanwhile, for ASP.NET issue, you may try the dedicated asp.net forum where you can contact more experts on asp.net:

    http://forums.asp.net 

    Thanks for your understanding and have a nice day!

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us



    Freitag, 9. März 2012 04:59
    Moderator