locked
importing excel data into sql server RRS feed

  • Question

  • HI!

    i have written the following code to import data from excel into sql server but the problem is dat each time i import data from excel it makes a new table into the sql server whereas i want to insert data in the same table.

     

    Dim si As String = ListBox1.SelectedItem

    Dim fn As String = fname

    Dim connection As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnectionString)

    connection.Open()

    Try

     

    Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("insert", connection)

     

    command.CommandType = CommandType.Text

    MessageBox.Show(fn)

    command.CommandText = "SELECT * INTO DesignFile FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database = " + fn + "' , 'SELECT * FROM [Site Report$]')"

     

    command.ExecuteNonQuery()

    Catch ex As Exception

    Console.WriteLine(ex.Message)

    MessageBox.Show(ex.ToString)

    Finally

    connection.Close()

    End Try

    MessageBox.Show("File successfully inserted")

     

    Thursday, April 17, 2008 11:43 AM

Answers

  • Only if columns in Excel and table match 1-for-1

    THen you can use

    insert into existingTable

    SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database = " + fn + "' , 'SELECT * FROM [Site Report$]')

    Although mapping columns is safer

    Saturday, April 19, 2008 9:50 PM

All replies

  • Your SQL statement needs to be changed a bit in order to insert into an existing table. Try this instead:

    insert into existingTable(column1, column2)
    SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database = " + fn + "' , 'SELECT * FROM [Site Report$]')

    where you define column1, column2, etc to match the tables in the exiting table and the corresponding columns from the excel file.
    Thursday, April 17, 2008 1:35 PM
  • Hi!

    actually the problem is data i have more than 100 columns and to wtie each name explictily is a bit difficult task.....So is there any work around??

    regards

    Omar_aa

    Friday, April 18, 2008 4:45 AM
  • Only if columns in Excel and table match 1-for-1

    THen you can use

    insert into existingTable

    SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database = " + fn + "' , 'SELECT * FROM [Site Report$]')

    Although mapping columns is safer

    Saturday, April 19, 2008 9:50 PM