Upload Excel Sheet Data to the SQL Server

Question

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.

Answer 1

Hi,

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

---

Mitja

Answer 2

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.

Answer 3

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