473,241 Members | 1,352 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

Pulling data from an Excel spreadsheet into either XML or Dataset

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.
Nov 19 '05 #1
4 2275
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.

Nov 19 '05 #2
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)
Nov 19 '05 #3
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.

Nov 19 '05 #4
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.

Nov 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: bryanilton | last post by:
Hi guys, I hope that one of you gurus can help me out here. Basically what I'd like to do is be able to price harddrives. I'd like to have two drop-down boxes. One for the manufacturer and the...
0
by: Alex Shirley | last post by:
Hi I'm still banging my head on how to import an Excel XML spreadsheet into a dataset. The code will work for standard XML files, but not XML files made with Excel (I get 'Specified cast is not...
7
by: dan graziano | last post by:
I have a very large data set where I have to do considerable regrouping of values and would like to know how you would suggest doing the following regrouping? The structure of the data set is...
2
by: Jason | last post by:
Hi I would like to export a dataset to xml, but not simply writing the XML equivalent of the dataset. so perhaps call it the true excel file. by writing the XML equivalent, excel is able to read...
1
by: Mike P | last post by:
I'm trying to take some data from an Excel spreadsheet, put it into a dataset, and then update a SQL table with the data. I'm not 100% sure I'm doing this right, can somebody tell me what I'm...
1
by: Roger Twomey | last post by:
I have a form that allows a user to upload a microsoft Excel Spreadsheet. (I am testing with one created in Excel 2000) The spreadsheet is uploaded correctly. It is then supposed to be read...
2
by: TM | last post by:
I have an Excel sheet where I setup my needed formatting, page settings, ect, and would like to take my data from a datagrid and paste it into the excel file and print the excel file. Any idea...
5
by: Scott M. Lyon | last post by:
I've just discovered a bug in some code I wrote a little while ago, and I need you guys' help to fix it. My program imports data from a standard Excel Spreadsheet (just with specific column...
7
by: Markalon | last post by:
Greetings. I'm new to Python programming, but I'm coming along. I'm having an issue trying to pull data from a CSV file created by an Excel spreadsheet (save as... csv function). What I need to do...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.