Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > ASP.NET 1.0 and 1.1 Professional
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 16th, 2008, 07:53 AM
Authorized User
 
Join Date: Apr 2008
Location: , , .
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.


Reply With Quote
  #2 (permalink)  
Old July 16th, 2008, 12:05 PM
Friend of Wrox
Points: 2,067, Level: 18
Points: 2,067, Level: 18 Points: 2,067, Level: 18 Points: 2,067, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 592
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);
}
}
}



Reply With Quote
  #3 (permalink)  
Old July 18th, 2008, 04:40 AM
Authorized User
 
Join Date: Apr 2008
Location: , , .
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


Reply With Quote
  #4 (permalink)  
Old August 12th, 2008, 06:46 AM
Registered User
 
Join Date: Aug 2008
Location: pune, maharastra, India.
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
Reply With Quote
  #5 (permalink)  
Old August 21st, 2008, 07:39 AM
Registered User
 
Join Date: Aug 2006
Location: hyderabad, Andhrapradesh, India.
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.
Reply With Quote
  #6 (permalink)  
Old August 27th, 2008, 12:46 AM
Authorized User
Points: 102, Level: 2
Points: 102, Level: 2 Points: 102, Level: 2 Points: 102, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2008
Location: , , .
Posts: 30
Thanks: 0
Thanked 1 Time in 1 Post
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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 12:21 AM
Insert data row in SQL SERVER 2000 dimeanel ADO.NET 1 January 25th, 2006 08:32 AM
Bulk Insert to Sql Server from .txt file avats BOOK: Beginning VB.NET Databases 1 June 3rd, 2005 04:44 AM



All times are GMT -4. The time now is 01:12 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.