Pulling data from an Excel spreadsheet into either XML or Dataset

[washoetech]

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.

[Mr Newbie]

I found this article which may help you, I found it quite useful.

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


"washoetech" <wa********@discussions.microsoft.com> wrote in message
news:7F**********************************@microsof t.com...

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.

[Paul Clement]

On Wed, 21 Sep 2005 00:29:02 -0700, "washoetech" <wa********@discussions.microsoft.com> 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)

[HARI PRASD BARU]

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.

[washoetech]

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" <HA***********@discussions.microsoft.com> wrote in message
news:83**********************************@microsof t.com...

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.