how to transfer data from exel to sql

  • 12 years ago
    hi alll


    how to import data from Excel to MSSQL2000
    or import data from mssql2000 to Excel?

    thanx
  • 12 years ago

    I'm guessing that SQL Server has some built-in tools to do that, but if you want to interact with Excel from VB.NET then you have too choices: ADO.NET or automation.  ADO.NET is easier but automation gives you more power.  A search on MSDN will give you information on both.

  • 12 years ago

    u mean in ado.net


    we must import data from exel to dataset first
    after that  we insert  to the ms sql server one record by one record, that is right?



  • 12 years ago

    You call Fill on a DataAdapter to get the data from Excel and Update on a DataAdapter to send the data to SQL Server.  Obviously there is some other code to set the scene but they're the two that do the work.  You can even use a single OleDbDataAdapter because SQL Server has an OLEDB provider, although you'd need a different OleDbConnection for the SelectCommand and the InsertCommand as they would be using different data sources.

  • 12 years ago


    this is code  but this use 2 oledbdataadapter?


    acroding u say we can just u onw oledbdataadapter can u show me how ?


    Dim mySelectQuery As String = "SELECT column1 FROM table1"
           Dim mycommand1 As System.Data.OleDb.OleDbDataAdapter
           Dim mycommand2 As System.Data.OleDb.OleDbDataAdapter
           Dim ds As New DataSet
           'Dim ds2 As New DataSet
           Dim i As Integer
           Dim myConnection As New OleDbConnection( _
                     "provider=Microsoft.Jet.OLEDB.4.0; " & _
                     "data source=C:\SA20051118.xls; " & _
                     "Extended Properties=Excel 8.0;")
           'Dim myCommand As New OleDbCommand("select * from [Sheet1$]", myConnection)
           mycommand1 = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", myConnection)


           Dim cmd As System.Data.OleDb.OleDbCommand
           Dim param As System.Data.OleDb.OleDbParameter
           Dim cin As New OleDbConnection("Provider=SQLOLEDB;Data Source=taufan;Initial Catalog=RJats;Integrated Security=SSPI;")
           Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
           mycommand2 = New System.Data.OleDb.OleDbDataAdapter("select * from sa", cin)
           Try
               cmd = New System.Data.OleDb.OleDbCommand("insert into sa (SecurityCode,Qty,TotalAmt)values(@SecurityCode,@Qty,@TotalAmt)", cin)
               cmd.Parameters.Add("@CustomerCode", SqlDbType.NVarChar, 12, "CustomerCode")
               cmd.Parameters.Add("@SecurityCode", SqlDbType.NVarChar, 12, "SecurityCode")
               cmd.Parameters.Add("@Qty", SqlDbType.NVarChar, 12, "Qty")
               cmd.Parameters.Add("@TotalAmt", SqlDbType.NVarChar, 12, "TotalAmt")
               mycommand2.InsertCommand = cmd


               mycommand1.Fill(ds, 0)
               mycommand2.Fill(ds, 1)
               For i = 0 To ds.Tables(0).Rows.Count - 1
                   Dim newrow As DataRow = ds.Tables(1).NewRow()
                   newrow(0) = ds.Tables(0).Rows(i)(0)
                   newrow(1) = ds.Tables(0).Rows(i)(1)
                   newrow(2) = ds.Tables(0).Rows(i)(2)
                   newrow(3) = ds.Tables(0).Rows(i)(3)
                   ds.Tables(1).Rows.Add(newrow)
                  mycommand2.Update(ds.Tables(1))


               Next i
               
               Me.Cursor = System.Windows.Forms.Cursors.Default
           Catch ex As OleDbException
               MsgBox(ex.Message, MsgBoxStyle.Information, Me.Text)
           End Try

  • 12 years ago


    this is code  but this use 2 oledbdataadapter?


    acroding u say we can just u onw oledbdataadapter can u show me how ?


    Dim mySelectQuery As String = "SELECT column1 FROM table1"
           Dim mycommand1 As System.Data.OleDb.OleDbDataAdapter
           Dim mycommand2 As System.Data.OleDb.OleDbDataAdapter
           Dim ds As New DataSet
           'Dim ds2 As New DataSet
           Dim i As Integer
           Dim myConnection As New OleDbConnection( _
                     "provider=Microsoft.Jet.OLEDB.4.0; " & _
                     "data source=C:\SA20051118.xls; " & _
                     "Extended Properties=Excel 8.0;")
           'Dim myCommand As New OleDbCommand("select * from [Sheet1$]", myConnection)
           mycommand1 = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", myConnection)


           Dim cmd As System.Data.OleDb.OleDbCommand
           Dim param As System.Data.OleDb.OleDbParameter
           Dim cin As New OleDbConnection("Provider=SQLOLEDB;Data Source=taufan;Initial Catalog=RJats;Integrated Security=SSPI;")
           Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
           mycommand2 = New System.Data.OleDb.OleDbDataAdapter("select * from sa", cin)
           Try
               cmd = New System.Data.OleDb.OleDbCommand("insert into sa (SecurityCode,Qty,TotalAmt)values(@SecurityCode,@Qty,@TotalAmt)", cin)
               cmd.Parameters.Add("@CustomerCode", SqlDbType.NVarChar, 12, "CustomerCode")
               cmd.Parameters.Add("@SecurityCode", SqlDbType.NVarChar, 12, "SecurityCode")
               cmd.Parameters.Add("@Qty", SqlDbType.NVarChar, 12, "Qty")
               cmd.Parameters.Add("@TotalAmt", SqlDbType.NVarChar, 12, "TotalAmt")
               mycommand2.InsertCommand = cmd


               mycommand1.Fill(ds, 0)
               mycommand2.Fill(ds, 1)
               For i = 0 To ds.Tables(0).Rows.Count - 1
                   Dim newrow As DataRow = ds.Tables(1).NewRow()
                   newrow(0) = ds.Tables(0).Rows(i)(0)
                   newrow(1) = ds.Tables(0).Rows(i)(1)
                   newrow(2) = ds.Tables(0).Rows(i)(2)
                   newrow(3) = ds.Tables(0).Rows(i)(3)
                   ds.Tables(1).Rows.Add(newrow)
                  mycommand2.Update(ds.Tables(1))


               Next i
               
               Me.Cursor = System.Windows.Forms.Cursors.Default
           Catch ex As OleDbException
               MsgBox(ex.Message, MsgBoxStyle.Information, Me.Text)
           End Try

  • 12 years ago

    Code:
    Dim myAdapter As New OleDbDataAdapter("SELECT * FROM [Sheet1$]" myExcelConnection)


    myAdapter.InsertCommand = New OleDbCommand("INSERT INTO sa (SecurityCode, Qty, TotalAmt) VALUES (@SecurityCode, @Qty, @TotalAmt)", mySQLServerConnection)
    myAdapter.AcceptChangesDuringFill = False


    Dim myTable As New DataTable


    myAdapter.Fill(myTable)
    myAdapter.Update(myTable)

    I've glossed over a few details here, like the creation of the connections and the addition of the parameters, but you get the idea.  I'm assuming that your Excel sheet follows the same schema as your database table.  If that's not the case then you'll need to do a bit more work.  Notice also that I set the AcceptChangesDuringFill property to False.  That is critical because it ensures that all the rows in the DataTable have their RowState left as Added and therefore the InsertCommand has new rows to insert.  If you don't include that then the call to Fill will implicitly call AcceptChanges and all your rows will be set to Unchanged, meaning that they will not be inserted when you call Update.

  • 12 years ago

    thanx for  u help,
    i have done my project



    but can u specifed what u mean automation more power full then ado.net for this case?

  • 12 years ago

    Quote:
    [1]Posted by Cerebro on 29 Nov 2005 12:31 AM[/1]
    thanx for  u help,
    i have done my project



    but can u specifed what u mean automation more power full then ado.net for this case?

    ADO.NET is a data access technology, so it allows you to retrieve and update data.  You can use the functionality of SQL to do some clever things easily, but basically its data in and data out.  Automation gives you full access to the Excel object model, so you can do virtually anything that you can in the Excel GUI.

  • 12 years ago
    ooooo ic ic

    thanx for u help and u information

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“You can stand on the shoulders of giants OR a big enough pile of dwarfs, works either way.”