Pulling data from an Excel spreadsheet into either XML or Dataset

Discussion in 'ASP .Net' started by =?Utf-8?B?d2FzaG9ldGVjaA==?=, Sep 21, 2005.

  1. 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.
    =?Utf-8?B?d2FzaG9ldGVjaA==?=, Sep 21, 2005
    #1
    1. Advertising

  2. =?Utf-8?B?d2FzaG9ldGVjaA==?=

    Mr Newbie Guest

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

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




    "washoetech" <> wrote in message
    news:...
    > 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, Sep 21, 2005
    #2
    1. Advertising

  3. =?Utf-8?B?d2FzaG9ldGVjaA==?=

    Paul Clement Guest

    On Wed, 21 Sep 2005 00:29:02 -0700, "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?
    ¤

    How about transferring from Excel directly to SQL Server:

    Function ExportExcelToSQLServer() As Boolean

    Dim ExcelConnection As New
    System.Data.OleDb.OleDbConnection("Provider=Microsoft.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)
    Paul Clement, Sep 21, 2005
    #3
  4. 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.
    =?Utf-8?B?SEFSSSBQUkFTRCBCQVJV?=, Sep 21, 2005
    #4
  5. =?Utf-8?B?d2FzaG9ldGVjaA==?=

    washoetech Guest

    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" <> wrote in message
    news:...
    > 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, Sep 22, 2005
    #5
  6. =?Utf-8?B?d2FzaG9ldGVjaA==?=

    vsurana

    Joined:
    Jul 7, 2008
    Messages:
    1
    Data from excel to database

    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
    vsurana, Jul 7, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. acool
    Replies:
    0
    Views:
    305
    acool
    May 6, 2004
  2. Harry Zoroc
    Replies:
    1
    Views:
    917
    Gregory Vaughan
    Jul 12, 2004
  3. luckyads
    Replies:
    0
    Views:
    554
    luckyads
    Jan 21, 2009
  4. luckyads
    Replies:
    0
    Views:
    425
    luckyads
    Jan 22, 2009
  5. Replies:
    1
    Views:
    96
    Grant Wagner
    Jul 27, 2005
Loading...

Share This Page