ASP.NET - Import Excel sheet into database

hi,

HI

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

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


	

		
	

refer the links also
http://www.davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx
http://www.4guysfromrolla.com/articles/022708-1.aspx#postadlink 

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

Easiest way: use SQL Server Import and Export Wizard in Management studio

To use the SQL Server Import and Export Wizard for importing and exporting data

1. Start the SQL Server Import and Export Wizard.

2. On the corresponding wizard pages, select a data source and a data destination.

   The available data sources include .NET Framework data providers, OLE DB providers, SQL Server Native Client providers, ADO.NET providers, Microsoft Office Excel, Microsoft Office Access, and the Flat File source. Depending on the source, you set options such as the authentication mode, server name, database name, and file format.

    3.   Set the options for the type of destination that you selected.

1. (Optional) Select one table and change the mappings between source and destination columns, or change the metadata of destination columns:

   • Map source columns to different destination columns.

   • Change the data type in the destination column.

   • Set the length of columns with character data types.

   • Set the precision and scale of columns with numeric data types.

   • Specify whether the column can contain null values.

2. (Optional) Select multiple tables, and update the metadata and options to apply to those tables:

   • Select an existing destination schema or provide a new schema to which to assign tables.

   • Specify whether to enable identity inserts in destination tables.

   • Specify whether to drop and re-create destination tables.

   • Specify whether to truncate existing destination tables.

3. Save and run a package.

   If the wizard is started from SQL Server Management Studio or the command prompt, the package can run immediately. You can optionally save the package to the SQL Server msdb database or to the file system. For more information about the msdb database, see http://msdn.microsoft.com/en-us/library/ms137916.aspx.

   When you save the package you can set the package protection level, and if the protection level uses a password, provide the password. For more information about package protection levels, see http://msdn.microsoft.com/en-us/library/ms141747.aspx.

   If the wizard is started from an Integration Services project in Business Intelligence Development Studio, you cannot run the package from the wizard. Instead, the package is added to the Integration Services project from which you started the wizard. You can then run the package in Business Intelligence Development Studio.

Refer: http://msdn.microsoft.com/en-us/library/ms140052.aspx

hi,

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:


SqlBulkCopy will import / export your Excel Spreadsheet information into a SQL Server Database Table at very high speeds.

hope this will help you

hello,

then just try this on click even of import

The OPENROWSET feature in SQL Server and MSDE provides a fast and easy way to open an OLE DB compatible data source, such as an Excel sheet, directly from your SQL script. Coupled with the "SELECT * INTO" command, the OPENROWSET feature can import data from an Excel sheet into a table in SQL Server or MSDE.

Run the following command from the SQL window in http://www.teratrax.com/tdm. This example uses the OLE DB Provider for Microsoft Excel to access sheet1 in the Excel file c:\book1.xls.

SELECT *
INTO db1.dbo.table1
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',
    'SELECT * FROM [sheet1$]')

table1 will be created in the db1 database. The content of this table will be imported from the sheet1 worksheet in your c:\book1.xls Excel file.

Try this code

private void button2_Click(object sender, EventArgs e)
{
  string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0";
  ExcelConstr += "Data Source=d:\book1.xls;Extended Properties=Excel 12.0";
  string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
  SqlConstr += "Integrated Security=True;User Instance=True";
  using (OleDbConnection con = new OleDbConnection(ExcelConstr))
  {
    con.Open();
    OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
    OleDbDataReader dr = com.ExecuteReader();
    using (SqlConnection sqlcon = new SqlConnection(Program.c))
    {
      sqlcon.Open();
      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
      {
        bulkCopy.DestinationTableName = "Table1";
        bulkCopy.ColumnMappings.Add("srno", "srno");
        bulkCopy.ColumnMappings.Add("amount", "amount");
        bulkCopy.WriteToServer(dr);
      }
    }
    dr.Close();
    dr.Dispose();
  }
  MessageBox.Show("successfully imported!");
  //display the imported data in the datagrid
  SqlDataAdapter da = new SqlDataAdapter("select * from Table1", Program.c);
  DataTable dt = new DataTable();
  da.Fill(dt);
  dataGridViewX1.DataSource = dt;
}