Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Pulling data from an Excel spreadsheet into either XML or Dataset

Reply
Thread Tools

Pulling data from an Excel spreadsheet into either XML or Dataset

 
 
=?Utf-8?B?d2FzaG9ldGVjaA==?=
Guest
Posts: n/a
 
      09-21-2005
Hello,

I am working on a project where I need to be able to grab the data from an
Excel spreadsheet and create a new table in my database based on the columns
in the spreadsheet. After the table is created then I need to fill the table
with the data from that spreadsheet. I am using ASP.NET 2.0 and SQL 2000 for
my database.

What would be the best way to do this? Should I go Excel to XML or Excel to
a Dataset?

Also, how would I do this?

Please any help would be much appreciated.

Thanks.
 
Reply With Quote
 
 
 
 
Mr Newbie
Guest
Posts: n/a
 
      09-21-2005
I found this article which may help you, I found it quite useful.

http://www.trainingon.net/Articles/ART0007.htm




"washoetech" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> I am working on a project where I need to be able to grab the data from an
> Excel spreadsheet and create a new table in my database based on the
> columns
> in the spreadsheet. After the table is created then I need to fill the
> table
> with the data from that spreadsheet. I am using ASP.NET 2.0 and SQL 2000
> for
> my database.
>
> What would be the best way to do this? Should I go Excel to XML or Excel
> to
> a Dataset?
>
> Also, how would I do this?
>
> Please any help would be much appreciated.
>
> Thanks.



 
Reply With Quote
 
 
 
 
Paul Clement
Guest
Posts: n/a
 
      09-21-2005
On Wed, 21 Sep 2005 00:29:02 -0700, "washoetech" <(E-Mail Removed)> wrote:

Hello,

I am working on a project where I need to be able to grab the data from an
Excel spreadsheet and create a new table in my database based on the columns
in the spreadsheet. After the table is created then I need to fill the table
with the data from that spreadsheet. I am using ASP.NET 2.0 and SQL 2000 for
my database.

What would be the best way to do this? Should I go Excel to XML or Excel to
a Dataset?


How about transferring from Excel directly to SQL Server:

Function ExportExcelToSQLServer() As Boolean

Dim ExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\My
Documents\Book5.xls" & ";" & _
"Extended Properties=""Excel
8.0;HDR=No""")

ExcelConnection.Open()

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_ Connection=yes].[Orders2] FROM [Orders$];",
ExcelConnection)

ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()

End Function


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Reply With Quote
 
=?Utf-8?B?SEFSSSBQUkFTRCBCQVJV?=
Guest
Posts: n/a
 
      09-21-2005
Hi try using this
private void BindExcel()
{
OleDbDataAdapter Adapter = new OleDbDataAdapter();
OleDbCommand Command = new OleDbCommand();


using(OleDbConnection Connection = new OleDbConnection())
{
DataSet dsExcel = new DataSet("Excel");
string ConnectionString;
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("Authors.xls") + ";Extended Properties=\"Excel
8.0;HDR=Yes;IMEX=1\"";

Connection.ConnectionString = ConnectionString;
Command.CommandText = "SELECT * FROM [Authors$]";
Command.Connection = Connection;
Adapter.SelectCommand = Command;
Adapter.Fill(dsExcel);
DataGrid1.DataSource = dsExcel;
DataGrid1.DataBind();
}


}

don't forget to put the Extended Properties in the double quotes.
Here i am binding to datagrid instead of creating in a database...

"washoetech" wrote:

> Hello,
>
> I am working on a project where I need to be able to grab the data from an
> Excel spreadsheet and create a new table in my database based on the columns
> in the spreadsheet. After the table is created then I need to fill the table
> with the data from that spreadsheet. I am using ASP.NET 2.0 and SQL 2000 for
> my database.
>
> What would be the best way to do this? Should I go Excel to XML or Excel to
> a Dataset?
>
> Also, how would I do this?
>
> Please any help would be much appreciated.
>
> Thanks.

 
Reply With Quote
 
washoetech
Guest
Posts: n/a
 
      09-22-2005
Hello,

My main objective is to get the data into the database. So if I pull the
data into a gridview control I can display the data but I dont know how to
then pull the data from the gridview and put it into the database. I am
going to give Pauls idea a try since that directly puts the Excel data into
the database.

The main reason I was asking about either XML or Dataset is because I will
have other kinds of data files to pull from including tab, comma delimited
and XML. I thought XML might be an option so that it does not matter what
kind of file I am pulling from. XML will be the only way that the data gets
from the file and put into the DB. Then all I would have to do is write
code that would convert Excel or Comma Delimited or Tab Delimited into XML
which would inter inject the data into the DB.

I hope I didn't just confuse the hell out of you. LOL

washoetech

"HARI PRASD BARU" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi try using this
> private void BindExcel()
> {
> OleDbDataAdapter Adapter = new OleDbDataAdapter();
> OleDbCommand Command = new OleDbCommand();
>
>
> using(OleDbConnection Connection = new OleDbConnection())
> {
> DataSet dsExcel = new DataSet("Excel");
> string ConnectionString;
> ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
> Server.MapPath("Authors.xls") + ";Extended Properties=\"Excel
> 8.0;HDR=Yes;IMEX=1\"";
>
> Connection.ConnectionString = ConnectionString;
> Command.CommandText = "SELECT * FROM [Authors$]";
> Command.Connection = Connection;
> Adapter.SelectCommand = Command;
> Adapter.Fill(dsExcel);
> DataGrid1.DataSource = dsExcel;
> DataGrid1.DataBind();
> }
>
>
> }
>
> don't forget to put the Extended Properties in the double quotes.
> Here i am binding to datagrid instead of creating in a database...
>
> "washoetech" wrote:
>
>> Hello,
>>
>> I am working on a project where I need to be able to grab the data from
>> an
>> Excel spreadsheet and create a new table in my database based on the
>> columns
>> in the spreadsheet. After the table is created then I need to fill the
>> table
>> with the data from that spreadsheet. I am using ASP.NET 2.0 and SQL 2000
>> for
>> my database.
>>
>> What would be the best way to do this? Should I go Excel to XML or Excel
>> to
>> a Dataset?
>>
>> Also, how would I do this?
>>
>> Please any help would be much appreciated.
>>
>> Thanks.



 
Reply With Quote
 
vsurana vsurana is offline
Junior Member
Join Date: Jul 2008
Posts: 1
 
      07-07-2008
hi

i have to do exactly the same task, if u have found the solution then plz send me,i am facing same problem
i have read data from excel to gridiew and now i have to insert data from gridview to database.

Exactly my aim is to transfer data from excel to database.

thanks
 
Reply With Quote
 
 
 
Reply

Thread Tools

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 On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pulling XML data (nodes and attributes) into datagrid for easy insert, update, delete luckyads ASP .Net 0 01-22-2009 02:03 PM
Pulling XML data (nodes and attributes) into datagrid for easy insert, update, delete luckyads ASP .Net 0 01-21-2009 09:04 PM
Pulling data from an excel file bryanilton@gmail.com Javascript 1 07-27-2005 04:47 PM
Tools to extract data from SQL database and convert it into XML & insert XML data into SQL databases Harry Zoroc XML 1 07-12-2004 10:10 PM
Pulling an Excel file and putting into a SQL DB? acool ASP .Net 0 05-06-2004 06:02 PM



Advertisments