Hello,

I am using SQL Server 2000 and ASP.NET1.1. My problem is about the uploading the Excel data into the SQL Server Database table. I could read the data from the excel data in ASP.NET page with Datagrid but the problem is with inserting into the database table.

Is there anyone, helping me in giving the ASP.NET code for the above problem? Help with Code..

Thanks.

There's a few different ways of doing this depending on what your circumstances are.

Is there a folder where Excel files saved and then must be uploaded? Do your users select the file to be uploaded? You could use SQL server and a DTS package to upload the data from the Excel file to the table. What's your scenario?

Please look here:

http://support.microsoft.com/kb/319951/EN-US/

or here

first you have to store excel data in datatable,
you can store data in datatable as

string constring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
datatable dt=new datatable();
Oledbconnection con=new oledbconnection(constring);
oledbdataadapter da=new oledbdataadapter("select * from sheet1",con)
da.fill(dt);

now you can store datatable data into sql using for/while loop.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69818


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


or this

Bind/Export Gridview data to EXCEL file---- ON button click:

protected void btnsubmit_Click(object sender, EventArgs e)
{

grdsqldata.PageSize = Convert.ToInt32(ViewState["rows"]);
grdsqldata.DataBind();
GridViewExportUtil.Export("Book1.xls",this.grdsqld ata);

}


public static void Export(string fileName, GridView grd)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";

using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid

Table table = new Table();
table.GridLines = grd.GridLines;

// add the header row to the table

if (grd.HeaderRow != null)
{
//GridViewExportUtil.PrepareControlForExport(grd.Hea derRow);
table.Rows.Add(grd.HeaderRow);
table.GridLines = grd.GridLines;
table.BackColor = System.Drawing.Color.Beige;
}

// add each of the data rows to the table
foreach (GridViewRow row in grd.Rows)
{
//GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}

// add the footer row to the table
if (grd.FooterRow != null)
{
table.GridLines = grd.GridLines;
//GridViewExportUtil.PrepareControlForExport(grd.Foo terRow);
table.Rows.Add(grd.FooterRow);
}

// render the table into the htmlwriter

table.RenderControl(htw);

// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}

or this

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
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 = "Sqltablename";
bulkCopy.WriteToServer(dr);
}
}
}

Hello rstelma,

Thank you for immediate reply.. I appreciated you for giving such a long list of coding..

Actually, I tried to fetch the data into DataGrid from Excel sheet.. But now my problem is that from there I want to save to the SQL database table.

or You could specify the code, that directly fetch the data from excel sheet to database table.

Yes, the user should select the excel file location and that I have done to fetch the location, but how to insert into database table.
I have the table created in SQL server database, now want only data to be inserted from Excel sheet. If u specify the ASP.NET code,it would be very helpful, though ur code helps me to get idea.

Thanks

hi there...m using asp.net1.1(c#.net)...I want to import data from an excel sheet and save it in a database...can anybody tell me how to do this...in this method bulkcopy is used but i am not able to use it...
can anybody help me out.thanks

Hi,

You can use sqlserver dts package for to upload the excel sheet data. for this first you need to save the uploaded excel sheet, then map that path as sourcefile in your dts package.

connect excel file through OLEDB connection and insert.

http://vb.net-informations.com/excel...data_oledb.htm

also you can modify data through OLEDB

http://vb.net-informations.com/excel...data_oledb.htm

huyang