Convert XLS to SQL Server (via CSV BULK INSERT) | VB.NETBytescout Spreadsheet SDK

Convert XLS to SQL Server (via CSV BULK INSERT) | VB.NET

Program.vb:

VB
Imports System.IO
Imports Bytescout.Spreadsheet
Imports System.Data.SqlClient

Class Program
    Friend Shared Sub Main(args As String())
        Try
            ' Load XLS document
            Using document As New Spreadsheet()
                document.LoadFromFile("SimpleReport.xls")
                Dim csvFile As String = Path.GetTempPath() & "SimpleReport.csv"

                ' Save the document as CSV file
                document.Workbook.Worksheets(0).SaveAsCSV(csvFile)
                document.Close()

                If File.Exists(csvFile) Then
                    ' MODIFY THE CONNECTION STRING WITH YOUR CREDENTIALS!!!
                    Dim connectionString As String = "Data Source=localhost;Initial Catalog=master;Integrated Security=true;"

                    Using connection As New SqlConnection(connectionString)
                        connection.Open()

                        ' Drop test database if exists
                        ExecuteQueryWithoutResult(connection, "IF DB_ID ('XlsTests') IS NOT NULL DROP DATABASE XlsTests")
                        ' Create empty database
                        ExecuteQueryWithoutResult(connection, "CREATE DATABASE XlsTests")
                        ' Switch to created database
                        ExecuteQueryWithoutResult(connection, "USE XlsTests")
                        ' Create a table for CSV data
                        ExecuteQueryWithoutResult(connection, "CREATE TABLE CsvTest (Name VARCHAR(40), FullName VARCHAR(255))")
                        ' Export CSV data from local file
                        ExecuteQueryWithoutResult(connection, "BULK INSERT CsvTest FROM '" & csvFile & "' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '" & vbLf & "')")

                        ' Check the data successfully exported
                        Using command As New SqlCommand("SELECT * from CsvTest", connection)
                            Dim reader As SqlDataReader = command.ExecuteReader()

                            If reader IsNot Nothing Then
                                Console.WriteLine()
                                Console.WriteLine("Exported CSV data:")
                                Console.WriteLine()

                                While reader.Read()
                                    Console.WriteLine([String].Format("{0}  |  {1}", reader(0), reader(1)))
                                End While
                            End If
                        End Using

                        Console.WriteLine()
                        Console.WriteLine("Press any key.")
                        Console.ReadKey()
                    End Using
                End If
            End Using
        Catch ex As Exception
            Console.WriteLine("Error: " & ex.Message)
            Console.ReadKey()
        End Try
    End Sub

    Private Shared Sub ExecuteQueryWithoutResult(connection As SqlConnection, query As String)
        Using command As New SqlCommand(query, connection)
            command.ExecuteNonQuery()
        End Using
    End Sub
End Class