Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > ASP.NET 1.0 and 1.1 Professional
|
ASP.NET 1.0 and 1.1 Professional For advanced ASP.NET 1.x coders. Beginning-level questions will be redirected to other forums. NOT for "classic" ASP 3 or the newer ASP.NET 2.0 and 3.5
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 16th, 2008, 07:53 AM
Authorized User
 
Join Date: Apr 2008
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to insert Data from Excel file to SQL server

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.


 
Old July 16th, 2008, 12:05 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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



 
Old July 18th, 2008, 04:40 AM
Authorized User
 
Join Date: Apr 2008
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old August 12th, 2008, 06:46 AM
Registered User
 
Join Date: Aug 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 21st, 2008, 07:39 AM
Registered User
 
Join Date: Aug 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old August 27th, 2008, 12:46 AM
hu_yang
Guest
 
Posts: n/a
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
how to import excel data into sql server khaink ASP.NET 1.0 and 1.1 Basics 1 July 31st, 2007 09:10 PM
how to insert text file in sql server database sharvari_mothe Java Databases 3 June 5th, 2007 07:25 AM
To upload an excel file to to sql server. Abins ASP.NET 1.0 and 1.1 Professional 2 February 22nd, 2006 01:21 AM
Insert data row in SQL SERVER 2000 dimeanel ADO.NET 1 January 25th, 2006 09:32 AM
Bulk Insert to Sql Server from .txt file avats BOOK: Beginning VB.NET Databases 1 June 3rd, 2005 04:44 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.