Import excel 2010 into sql 2010

Question

Using sql 2008 r2, vb.net 2010, excel 2010, file in xls mode

I want to import an excel spreadsheet programicly into sql 2010 via vb.net . I have 2 versions of the spreadsheet. The first one is the orginal one that I used for the SQL import wizard to import to the table (sheet name that showed in the import wizard is Dictionary$).

The second version is an export from sql to excel (since I had made many manual changes).

The visible differences I can see between the files is:

1. The sheetname in import wizard does not have a $ after dictionary.
2. Every cell in the spreadsheet has a ' (single quote at the begining of the cell').

Now to the problem of problems. Doing it programmatically and using the second version(exported) will import all the pre-existing rows without an issue but if I add rows to the spreadsheet the import will not see those rows.

If I try programmicly importing the orginal spreadsheet no row get imported becuase it can't find the table dictionary in the excel file (dictionary has the $ showing at end in wizard).

Solutions I am looking for are:

• Is there something I need to enable in excel file to allow importing to SQL programically?
• And what can I do to the exported file from sql so that if I add rows it can be reimported later if needed?

The code is following please note the following code works on the second version of the spreadsheet and that the SQL export wizard was used to produce it. also if I add rows to bottom of the spreadsheet those rows will not import programmtically.

Private Shared Sub ImportDictionary()
        Dim curFile As String = "dictionary.xls"
        Dim fullPath As String = Path.GetFullPath(curFile)
        Dim Excelconnection = New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" + fullPath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'")
        Dim myConnection As New SqlConnection(SQLdbString)
        Try
            myConnection.Open()
            Excelconnection.Open()
            Dim mySelectQuery As String = "SELECT * from dictionary"
            Dim myCommand2 As New OleDbCommand(mySelectQuery, Excelconnection)
            Dim myReader2 As OleDbDataReader
            myReader2 = myCommand2.ExecuteReader()
            While myReader2.Read()
                Dim myCommand As New SqlCommand
                Try
                    myCommand.Connection = myConnection
                    MyQueue.Enqueue(String.Format("{0} {1} {2} {3} {4}", myReader2.GetString(0), myReader2.GetString(1), myReader2.GetString(2), myReader2.GetString(3), myReader2.GetString(4)))
                    myCommand.Parameters.Add("@G0", SqlDbType.VarChar).Value = myReader2.GetString(0)
                    myCommand.Parameters.Add("@G1", SqlDbType.VarChar).Value = myReader2.GetString(1)
                    myCommand.Parameters.Add("@G2", SqlDbType.VarChar).Value = myReader2.GetString(2)
                    myCommand.Parameters.Add("@G3", SqlDbType.VarChar).Value = myReader2.GetString(3)
                    myCommand.Parameters.Add("@G4", SqlDbType.VarChar).Value = myReader2.GetString(4)
                    myCommand.CommandText = "INSERT INTO dictionary values(@G0,@G1,@G2,@G3,@G4)"
                    myCommand.ExecuteNonQuery()
                Catch e As Exception
                    Console.WriteLine(e.ToString())
                Finally
                    myCommand.Dispose()
                End Try
            End While
        Catch e As Exception
            Console.WriteLine(e.ToString())
        Finally
            myConnection.Dispose()
        End Try
        Excelconnection.Dispose()
    End Sub