locked
How to import excel data to SQL SERVER 2008 in the same table RRS feed

  • Question

  • This code import data from excel into new table, but I want to know how import it into table already exist.

     

    Imports

     

    System.IO

    Public

     

    Class

    ImportExcel

     

    Dim dm As New

    Datamanage

     

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

    Button2.Click

    OpenFileDialog1.ShowDialog()

    txtexcelPath.Text = OpenFileDialog1.FileName

     

    End

    Sub

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

    Button1.Click

     

    Try

     

    Dim ExcelPath As

    String

     

    Dim Ext As

    String

    ExcelPath = txtexcelPath.Text.ToLower()

    Ext = ExcelPath.Substring(ExcelPath.LastIndexOf(

    "."

    ) + 1)

     

    If File.Exists(ExcelPath) AndAlso (Ext.Equals("xls"

    ) _

     

    Or Ext.Equals("xlsx"))

    Then

    dm.importToServer(OpenFileDialog1.FileName, txtServer.Text, _

    txtDbName.Text, txtusername.Text, txtpassword.Text, _

    txtInsertedTableName.Text)

    ProgressBar1.Visible =

    False

     

    MessageBox.Show("File imported successfully"

    )

     

    End

    If

     

    Catch ex As

    Exception

    ProgressBar1.Visible =

    False

     

    MessageBox

    .Show(ex.Message)

     

    End

    Try

     

    End Sub

     

    End

     

    Class

     

     

    Public

     

     

    Class

    Datamanage

     

     

     

    Public Sub importToServer(ByVal ExcelPath As String

    , _

     

     

    ByVal ServerName As String

    , _

     

     

    ByVal DBName As String, ByVal UserName As String

    , _

     

     

    ByVal Password As String, ByVal InsertedTableName As String

    )

     

     

    Try

     

     

    Dim ExceCon As

    String

    ExceCon =

     

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="

    & _

    ExcelPath &

     

    ";Extended Properties=Excel 8.0"

     

     

    Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection

    (ExceCon)

    excelConnection.Open()

     

     

    Dim OleStr As

    String

    OleStr =

     

    "SELECT * INTO [ODBC; Driver={SQL Server};Server="

    _

    & ServerName &

     

    ";Database=" & DBName & ";Uid="

    & _

    UserName &

     

    ";Pwd=" & Password & "; ].[" & InsertedTableName &

    "] FROM [Plan1$];"

     

     

    Dim excelCommand As New System.Data.OleDb.OleDbCommand

    (OleStr, excelConnection)

    excelCommand.ExecuteNonQuery()

    excelConnection.Close()

     

     

    Catch ex As

    Exception

     

     

    Throw New Exception("Error: "

    & ex.Message)

     

     

    End

    Try

     

     

    End

    Sub

     

    End

     

     

    Class

     

     

     

     

     

     

     

     

     

    Thanks

    G.Lista.

    Friday, March 18, 2011 8:33 PM

Answers

All replies

  • This code import data from excel into new table, but I want to know how import it into table already exist.

     

    Imports

     

    Public

     

     CLass  

    Dim dm As New  

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

    Button2.Click

    OpenFileDialog1.ShowDialog()

    txtexcelPath.Text = OpenFileDialog1.FileName

    End  

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  

     

    Dim ExcelPath As  

    Dim Ext As

    String

    ExcelPath = txtexcelPath.Text.ToLower()

    Ext = ExcelPath.Substring(ExcelPath.LastIndexOf(

    "." ) + 1)

    If File.Exists(ExcelPath) AndAlso (Ext.Equals("xls") _

    Or Ext.Equals("xlsx"))

    Then

    dm.importToServer(OpenFileDialog1.FileName, txtServer.Text, _

    txtDbName.Text, txtusername.Text, txtpassword.Text, _

    txtInsertedTableName.Text)

    ProgressBar1.Visible =

    False
     

    MessageBox.Show("File imported successfully" )

    End  

    Catch ex As

    Exception

    ProgressBar1.Visible =

    False
     

    MessageBox .Show(ex.Message)

    End  

    End Sub

     

    End

     

     

     Class

    Public Class

     G.Lista

    Thanks

    Datamanage

    Public Sub importToServer(ByVal ExcelPath As String , _

    ByVal ServerName As String , _

     ByVal DBName As String, ByVal UserName As String , _ ByVal Password As String, ByVal InsertedTableName As String )

    Try

    Dim ExceCon As

    String

    ExceCon =

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _

    ExcelPath &

    ";Extended Properties=Excel 8.0"
    Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection

    (ExceCon)

    excelConnection.Open()

    Dim OleStr As

    String

    OleStr =

    "SELECT * INTO [ODBC; Driver={SQL Server};Server=" _

    & ServerName &";Database=" & DBName & ";Uid=" & _ UserName &";Pwd=" & Password & "; ].[" & InsertedTableName & "] FROM [Plan1$];"Dim excelCommand As New System.Data.OleDb.OleDbCommand

     

    (OleStr, excelConnection)

    excelCommand.ExecuteNonQuery()

    excelConnection.Close()

    Catch ex As

    Exception

    Throw New Exception("Error: " & ex.Message) End

     

    End

    End

     

    Class

    

     

     

    Sub

    Try

    Try

    If

    String

    Try

    Sub

    Datamanage

    ImportExcel

    System.IO

    Friday, March 18, 2011 8:39 PM
  • Hi Guilherme, I understand that the only tables you can import into are Staging: http://msdn.microsoft.com/en-us/library/ee633808.aspx
    Wednesday, March 30, 2011 3:43 AM
  • Hi Guilherme,

    Are you sure that your question is related to the product "master data services" ?

     

    please refer to the SQL 2008R2 forumshttp://social.msdn.microsoft.com/Forums/en-US/category/sqlserver

    regards,

     



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Wednesday, March 30, 2011 6:16 AM