VB.NET - Excel - How to save Excel worksheet as MS SQL table

Watch this example on YouTube:


I have simple Excel file called Test.xlsx

and will create program that will read this worksheet and will save it as ms sql table.
Here is the whole example - please refer to my other Excel related blogs that will show you how to read/save Excel file programmatically

        ' open Excel
        Dim oExcel As Object
        oExcel = CreateObject("Excel.Application")
        oExcel.Workbooks.Open("C:\TEMP\ExcelTest\Test.xlsx")

        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        oBook = oExcel.ActiveWorkbook
        oSheet = oExcel.Worksheets(1)


        ' this will be the name of my new table in my MS SQL db.
        Dim TableName As String = "TestTable"
        Dim Col As Integer
        Dim ColumnValue As String = String.Empty

        Dim sqlSelect As New StringBuilder
        Dim sqlCreate As New StringBuilder
        sqlCreate.Append("If Exists(Select * From Information_Schema.Tables Where Table_Type='Base Table' And ")
        sqlCreate.Append(" Table_Name = '" & TableName & "') Begin Drop Table " & TableName & " End ")
        sqlCreate.Append("Create Table " & TableName & "(")

        ' let's loop through the Excel file to get all columns and use column names to create sql statements
        For Col = 0 To oSheet.Columns.Count
            ColumnValue = oSheet.Range(GetLetter(Col) & 1).Value
            If ColumnValue = String.Empty Or ColumnValue = Nothing Then
                Exit For
            End If
            sqlSelect.Append("[" & ColumnValue & "],")
            ' to simplify - varchar(500)
            sqlCreate.Append("[" & ColumnValue & "] varchar(500)" & ", ")
        Next
        ' remove commas
        sqlSelect.Remove(sqlSelect.Length - 1, 1)
        sqlCreate.Remove(sqlCreate.Length - 1, 1)
        sqlCreate.Append(")")

        ' if table exists then delete.  after that create new table
        Dim CreateCommand As New OleDb.OleDbCommand(sqlCreate.ToString())
        Dim dbConnection As New System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB;Data Source=Daniel-Mobile2\DanielTesting;Initial Catalog=School;Persist Sequrity Info=True;User ID=sa;Password=T00Secret")

        CreateCommand.Connection = dbConnection
        dbConnection.Open()
        CreateCommand.ExecuteScalar()
        dbConnection.Close()


        ' select all data from excel
        Dim ExcelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\ExcelTest\Test.xlsx;Extended Properties=""Excel 12.0;HDR=YES;"""
        Dim ExcConnection As New System.Data.OleDb.OleDbConnection(ExcelConnectionString)
        ExcConnection.Open()
        Dim myCommand As New OleDb.OleDbCommand("Select " & sqlSelect.ToString() & " From [SheetName$]")
        myCommand.Connection = ExcConnection
        Dim dReader As OleDb.OleDbDataReader
        dReader = myCommand.ExecuteReader()

        ' insert data into table
        Dim sqlBulk As New SqlBulkCopy("Data Source=Daniel-Mobile2\DanielTesting;Initial Catalog=School;Persist Security Info=True;User ID=sa;Password=T00Secret")
        sqlBulk.DestinationTableName = TableName
        sqlBulk.BulkCopyTimeout = 600
        sqlBulk.WriteToServer(dReader)
        ExcConnection.Close()

        oBook.Close()
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing

Here is the GetLetter function - it just converts number to letter
    Protected Function GetLetter(ByVal i As Integer) As String
        Dim strLetter As String = ChrW(65 + i)

        If i > 181 Then
            strLetter = "G" & ChrW(-117 + i)
        ElseIf i > 155 Then
            strLetter = "F" & ChrW(-91 + i)
        ElseIf i > 129 Then
            strLetter = "E" & ChrW(-65 + i)
        ElseIf i > 103 Then
            strLetter = "D" & ChrW(-39 + i)
        ElseIf i > 77 Then
            strLetter = "C" & ChrW(-13 + i)
        ElseIf i > 51 Then
            strLetter = "B" & ChrW(13 + i)
        ElseIf i > 25 Then
            strLetter = "A" & ChrW(39 + i)
        End If

        Return strLetter
    End Function