locked
How to import the data from excel sheet to sql server database in asp net RRS feed

  • Question

  • User-1499091019 posted

    How to import the data from excel sheet to sql server database in asp net using c#

    Monday, February 10, 2014 12:07 AM

Answers

  • User2103319870 posted

    Hi,

    To Export Excel sheet data to Sql Server you can use any one of the following solutions

    ASP.Net

    SQL Server

    You can use the SQL Sever Export Import Wizard to retreive and save data 

    Apart from the above option you can also use the SQL function OPENROWSET, suppose you have an Excel file in the C:\ drive of the SQL Server database, and the data is on a Worksheet called Sheet1 and your two columns are called Column1 and Column2.

    INSERT INTO [dbo].[Table1]  ( [Column1 ], [Column2] )
    
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    
    'Excel 8.0;Database=C:\MyTable.xls',
    
    'SELECT * FROM [Sheet1$]')

    Another suggestion is to use SSIS to import data from Excel to sql server

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 10, 2014 8:59 PM

All replies

  • User578059180 posted

    Try this

    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); } } }

    Please check this link

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

    Please mark as answer if this helps you

    Monday, February 10, 2014 12:15 AM
  • User2103319870 posted

    Hi,

    To Export Excel sheet data to Sql Server you can use any one of the following solutions

    ASP.Net

    SQL Server

    You can use the SQL Sever Export Import Wizard to retreive and save data 

    Apart from the above option you can also use the SQL function OPENROWSET, suppose you have an Excel file in the C:\ drive of the SQL Server database, and the data is on a Worksheet called Sheet1 and your two columns are called Column1 and Column2.

    INSERT INTO [dbo].[Table1]  ( [Column1 ], [Column2] )
    
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    
    'Excel 8.0;Database=C:\MyTable.xls',
    
    'SELECT * FROM [Sheet1$]')

    Another suggestion is to use SSIS to import data from Excel to sql server

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 10, 2014 8:59 PM
  • User-389597101 posted

    Check this tutorial

    http://gyansangrah.com/ArticleContent.aspx?ID=bulk_upload_in_asp_net

    Monday, February 10, 2014 9:26 PM